Re: One Web Service updates SQL, the other can't

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 10/27/04 

Date: Wed, 27 Oct 2004 10:17:29 -0400

You may want to post your ADO code plus my comments. 

-- 
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:OQlDzVCvEHA.4076@TK2MSFTNGP14.phx.gbl...
I posted this on .sqlserver.security under the same name and Tom Moreau
suggested I post here and see if you all have any ideas.
I have a production and development system both running on one PC under W2K
with SQL7sp4.  One Web Service can access and update the database via ADO
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.
I'm logging in as "sa" to try and remove as many permission issues as
possible using the following connection string:
strCnxn = "Provider=SQLOLEDB; Network Library=DBMSSOCN; Data
Source=xxx.xxx.xxx.xxx; Initial Catalog=CADE_LMW; User ID=sa;
Password=password"
Watching both the debugger and the trace, SQL is receiving what I send and
returning data from all "select" statements.  All goes well until I send an
update statement.  There is basically nothing in the code from the last
"select" call to this "update".  When the "update" was sent, 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.
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

  • REPOST: One Web Service updates SQL, the other cant
    ... I posted this on .sqlserver.security under the same subject and Tom Moreau ... One Web Service can access and update the database via ADO ... Columnist, SQL Server Professional ...
    (microsoft.public.vb.database.ado)
  • Re: REPOST: One Web Service updates SQL, the other cant
    ... One Web Service can access and update the database via ADO ... > possible using the following connection string: ... > Columnist, SQL Server Professional ...
    (microsoft.public.vb.database.ado)
  • Re: One Web Service updates SQL, another cant?
    ... I guess its because this is my first Web Service and I ... it opens the database connection. ... > Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.security)
  • Re: One Web Service updates SQL, another cant?
    ... I set the trace for the problem database and am receiving exactly what I ... the first Web Service started and the other Service fails. ... > Columnist, SQL Server Professional ... >> thing to look at is the permissions for that user in EM. ...
    (microsoft.public.sqlserver.security)
  • Re: One Web Service updates SQL, another cant?
    ... Columnist, SQL Server Professional ... I'm sending raw SQL and should have told you that I was opening the database ... > thing to look at is the permissions for that user in EM. ... One Web Service can access and update the SQL Server 7 ...
    (microsoft.public.sqlserver.security)