Re: One Web Service updates SQL, another can't?
From: Steve Ricketts (steve_at_velocedge.com)
Date: 10/26/04
- Next message: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Previous message: TryingToFigureItOut: "Common Criteria (CC) Certification -- EALx..."
- In reply to: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Next in thread: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Reply: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 26 Oct 2004 14:15:01 -0400
I'm not sure how to trap the login and out events... is that "Connect" and
"Disconnect" in the trace window? I couldn't find them in the Events
section of the Trace Properties dialog. The connection string is:
strCnxn = "Provider=SQLOLEDB; Network Library=DBMSSOCN; Data
Source=xxx.xxx.xxx.xxx; Initial Catalog=CADE_LMW; User ID=sa;
Password=password"
I've tried it with and without the "Network Library" parameter and obviously
there is a real IP address in the Data Source. Watching both the debugger
and the trace, all goes well until the update statement I mentioned earlier.
There is basically nothing in the code from the last "select" call to this
"update". It just sets up the SQL string. When I let the cnxn.execute(sql)
statement run with the "update", I trapped the error and err.description
says:
"an exception of type: (System.Runtime.InteropServices.COMException)
occurred"
And then I start getting the database closed errors on subsequent calls. I
open the database again after the error and all is fine until the next
"update" statement.
sr
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:OslYpR4uEHA.1984@TK2MSFTNGP14.phx.gbl...
> The error suggests that somehow the connection to SQL Server is being
> closed. Audit the Login and Logout events with the profiler and see if
> that's the case. I'm wondering also if there is a connection pooling
issue
> here. Could we have a look at your connection string?
>
> --
> Tom
>
> ---------------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
>
> "Steve Ricketts" <steve@velocedge.com> wrote in message
> news:uKcyPM4uEHA.1860@TK2MSFTNGP15.phx.gbl...
> I set the trace for the problem database and am receiving exactly what I
> sent. (I'm using sa just to eliminate as much of the permissions problems
> as possible). I'm stepping through the Visual Studio .Net debugger and
it's
> showing me the returned data record when its a "select" but as soon as an
> "update" is issued, I get "Operation is not allowed when the object is
> closed". The SQL Trace shows:
>
> update cmi set revised = '10/26/2004 12:32:30 PM' where person_int = 23720
> and course_int = 645 and lesson_int = 2
>
> Which is what I sent from the web service... and why would this work if
it's
> the first Web Service started and the other Service fails. It doesn't
seem
> to matter which database I use, the one I start first works and the second
> one doesn't.
>
> Steve
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:eMxsH%233uEHA.1260@TK2MSFTNGP12.phx.gbl...
> > If it's connecting as sa - which, BTW, is a bad thing - then the
security
> is
> > essentially bypassed. The next thing I'd do is run a profiler trace and
> try
> > and see what's coming at your server. Hopefully, you can run the web
> > service in debug mode to step through it.
> >
> > --
> > Tom
> >
> > ---------------------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "Steve Ricketts" <steve@velocedge.com> wrote in message
> > news:uw%23Fp63uEHA.2172@TK2MSFTNGP14.phx.gbl...
> > I'm sending raw SQL and should have told you that I was opening the
> database
> > as "sa". db_denydatawriter for sa is not checked. Does that help?
> >
> > sr
> >
> >
> >
> > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> > news:uichWz3uEHA.2520@TK2MSFTNGP15.phx.gbl...
> > > Assuming that you're using raw T-SQL - not stored procs - I'd look at
> who
> > is
> > > a member of the db_denydatawriter role in the problem database.
Another
> > > thing to look at is the permissions for that user in EM. That may
tell
> > you
> > > if there are any explicit DENY's.
> > >
> > > --
> > > Tom
> > >
> > > ---------------------------------------------------------------
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > > SQL Server MVP
> > > Columnist, SQL Server Professional
> > > Toronto, ON Canada
> > > www.pinnaclepublishing.com
> > >
> > >
> > > "Steve Ricketts" <steve@velocedge.com> wrote in message
> > > news:eZ5PcW3uEHA.3828@TK2MSFTNGP12.phx.gbl...
> > > I have a production and development system both running on one PC
under
> > W2K
> > > with SQL7sp4. One Web Service can access and update the SQL Server 7
> > > database fine. Another instance of the same Web Service code,
accessing
> a
> > > different, but identical database, can read but not update records.
It
> > > seems like whichever Web Service I start first is allowed to read and
> > write,
> > > but the other has only read permissions.
> > >
> > > Is there a exclusive, read-only, permissions setting that I've missed
> > > somewhere? The problem is simply the second Web Service can't write
to
> > SQL,
> > > but why is the big question. Any help would be greatly appreciated,
> I'm
> > > into days on this one!
> > >
> > > Thanks,
> > >
> > > Steve Ricketts
> > >
> > >
> >
> >
>
>
- Next message: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Previous message: TryingToFigureItOut: "Common Criteria (CC) Certification -- EALx..."
- In reply to: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Next in thread: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Reply: Tom Moreau: "Re: One Web Service updates SQL, another can't?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
