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

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

Date: Tue, 26 Oct 2004 23:03:18 -0400

Ok, many thanks for your help and suggestions.

Regards,

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