Re: One Web Service updates SQL, another can't?

From: Steve Ricketts (steve_at_velocedge.com)
Date: 10/28/04 

Date: Thu, 28 Oct 2004 10:56:36 -0400

I'll repost.

Yeah, I changed the code to check if it was an select, update, insert, etc.
and used the options you mentioned earlier. Didn't fix the problem, but it
was a good suggestion anyway that I'll keep in the code.

Just for grins, I set up duplicate webs, web services, and databases on
another computer and got the very same error... so at least I'm consistent!
;-)

sr

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:e$pYYnPvEHA.1308@TK2MSFTNGP09.phx.gbl...
> I was getting concerned about that myself. I'm fairly certain it isn't a
> permission problem, since you're connecting as sa. I think you should
post
> the VB code and also put REPOST in the subject line, so it gets someone's
> attention.
>
> Also, have you tried using the two versions of Execute that I suggested?
> That would be a good starting point. If you are going to execute a
SELECT,
> it will expect to consume rows. If you're not pulling the rows back, that
> could be an issue. If you are executing an UPDATE, then it should know to
> execute with no rows.
>
> --
> 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:egN6FiPvEHA.2288@TK2MSFTNGP09.phx.gbl...
> Looks like they might have thought you answered my post on the other
> newsgroup. Think I ought to post another question?
>
> Steve
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:unULyC7uEHA.444@TK2MSFTNGP10.phx.gbl...
> > I'm not too sure what to suggest next. The code here doesn't show any
> > retrieval of the rows from the SELECT, which can be problematic. Also,
> it's
> > not a good idea to do a one-size-fits-all on statements that do and do
not
> > return rows. For example, an UPDATE never returns rows. If you want to
> > stick with executing things through the Connection object - instead of
> using
> > a Command object - consider the following two approaches:
> >
> > ' returns a Recordset
> > Set rs = Cnxn.Execute ("select * from MyTable",, adCmdText)
> >
> > ' returns no Recordset
> > Cnxn.Execute ("update MyTable set x = 3 where y = 2",,
adExecuteNoRecords)
> >
> > Specifying the command type, as shown above, can also help performance,
> > since ADO doesn't have to spend time guessing.
> >
> > If this doesn't help, you may want to post this in the ADO newsgroup.
Val
> > and the guys there may be able to help.
> >
> >
> > --
> > 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:e%23jSlz5uEHA.3416@TK2MSFTNGP09.phx.gbl...
> > The code doesn't seem to be much special, but I'm happy to include the
> parts
> > that are failing.
> >
> > Public strCnxn as string
> >
> > strCnxn = strCnxn = "Provider=SQLOLEDB; Network
Library=DBMSSOCN;
> > Data
> > Source=xxx.xxx.xxx.xxx; Initial Catalog=CADE_LMW; User ID=sa;
> > Cnxn = New ADODB.Connection()
> > Cnxn.Open(strCnxn)
> > .
> > .
> > In a procedure
> > Dim recData As ADODB.Recordset
> > Dim recCMI As ADODB.Recordset
> >
> > SQL = "select * from cmi_student "
> > SQL = SQL & " where person_int = " & CMI(_person_int)
> > Call sqlExecute(recData, SQL) 'This works
> > If recData.EOF Then
> > Return False
> > Else
> > Return True
> > End If
> >
> > SQL = "update cmi set "
> > SQL = SQL & " revised = " & Delimit & Now & Delimit
> > SQL = SQL & " where person_int = " & CMI(_person_int)
> > SQL = SQL & " and course_int = " & CMI(_course_int)
> > SQL = SQL & " and lesson_int = " & CMI(_lesson_int)
> > Call sqlExecute(recCMI, SQL) 'This fails
> > .
> > .
> > .
> > Function sqlExecute(ByRef RS As ADODB.Recordset, ByVal SQL As
String)
> As
> > Boolean
> > Dim i As Integer
> >
> > i = 0
> > tryAgain:
> > On Error GoTo gotError
> >
> > RS = Cnxn.Execute(SQL)
> > If RS.EOF Then sqlExecute = False Else sqlExecute = True
> > Exit Function
> >
> > gotError:
> > i = i + 1
> > If i > 5 Then Exit Function
> > Cnxn.Close()
> > Cnxn.Open(strCnxn)
> > GoTo tryagain
> > End Function
> >
> >
> > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> > news:uC$MeO5uEHA.4028@TK2MSFTNGP15.phx.gbl...
> > > Sorry about that. Right now, I have access only to a SQL2K box, which
> > > obviously has more objects that it can profile than does SQL7.
> > >
> > > I'm wondering if there is some use of disconnected recordsets going on
> > here.
> > > I know ADO supports that. It's just that you have to reconnect when
you
> > > want to update.
> > >
> > > Do you have a small snippet of your client code that we can see? I
> don't
> > > have any Interop experience but at least we can see if the code looks
> OK.
> > >
> > > --
> > > 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:%23pVLgH5uEHA.3948@TK2MSFTNGP15.phx.gbl...
> > > I'm trying.... but I just can't see anything about a Security Audio on
> the
> > > Events tab. In the tree I have:
> > >
> > > Cursors
> > > Errors & Warning
> > > Locks
> > > Misc.
> > > Objects
> > > Scans
> > > SQL Operators
> > > Stored Procedures
> > > Transactions
> > > TSQL
> > >
> > > On the ADO.Net... I guess its because this is my first Web Service and
I
> > > just used the access methods I knew. Until now (2 years) it hasn't
been
> a
> > > problem. This is the first time we've tried to run two instances of
the
> > > same Web Service. I'm not sure how to answer your question on shared
> > > connections across all instances, so let me answer this way. When the
> Web
> > > Service starts, it opens the database connection. All subsequent
calls
> to
> > > that service are handled through that connection. Its open as long as
> the
> > > Web Service is running. The second Web Service is exactly the same,
> only
> > it
> > > opens a connection to a different database with identical structure as
> the
> > > first.
> > >
> > > Steve
> > >
> > >
> > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> > > news:u$QUwj4uEHA.1264@TK2MSFTNGP12.phx.gbl...
> > > > In the profiler, in the Events tab, click on Security Audit. In
that
> > > tree,
> > > > you'll see Audit Login, Audit Login Failed, Audit Logout, Audit
Object
> > > > Permission Event. See what that gives you in the trace.
> > > >
> > > > The connect string looks innocent enough.
> > > >
> > > > Since you're using Interop, I'm wondering if there is something
there.
> > > > Since this is a web service, why aren't you using ADO.NET? Also, is
> > this
> > > a
> > > > shared connection across all instances?
> > > >
> > > > --
> > > > 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:%23X%231Ee4uEHA.3624@TK2MSFTNGP09.phx.gbl...
> > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages