Re: One Web Service updates SQL, another can't?
From: Steve Ricketts (steve_at_velocedge.com)
Date: 10/26/04
- Next message: Sue Hoegemeier: "Re: Master database can be browsed by any user. is this normal?"
- Previous message: GYatabe: "No OLE DB providers listed when creating a Linked Server"
- 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 16:48:03 -0400
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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Sue Hoegemeier: "Re: Master database can be browsed by any user. is this normal?"
- Previous message: GYatabe: "No OLE DB providers listed when creating a Linked Server"
- 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
|
