Re: One Web Service updates SQL, another can't?
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 10/28/04
- Next message: Steve Ricketts: "Re: One Web Service updates SQL, another can't?"
- Previous message: Steve Ricketts: "Re: One Web Service updates SQL, another can't?"
- In reply to: Steve Ricketts: "Re: One Web Service updates SQL, another can't?"
- Next in thread: Steve Ricketts: "Re: One Web Service updates SQL, another can't?"
- Reply: Steve Ricketts: "Re: One Web Service updates SQL, another can't?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Thu, 28 Oct 2004 10:24:08 -0400
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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Steve Ricketts: "Re: One Web Service updates SQL, another can't?"
- Previous message: Steve Ricketts: "Re: One Web Service updates SQL, another can't?"
- In reply to: Steve Ricketts: "Re: One Web Service updates SQL, another can't?"
- Next in thread: Steve Ricketts: "Re: One Web Service updates SQL, another can't?"
- Reply: Steve Ricketts: "Re: One Web Service updates SQL, another can't?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
