Problem Retrieving SCOPE_IDENTITY

A couple of Web applications in different SQL Server 2000 databases use SCOPE_IDENTITY to retrieve the key value of a record that was just inserted. It works--most of the time. However, from time to time the identity value is not retrieved. Evidence suggests that in these cases, a null value is being retrieved. This has forced me to come up with less-than-ideal workarounds for the missing identity value.

Does anyone have any idea why SCOPE_IDENTITY sometimes fails to retrieve the identity value and transmit it back to the Web page? Could a network issue cause the problem? Is there anything I can do other than rewrite the apps to use a different algorithm than using SCOPE_IDENTITY? Thanks.

I am not aware of any issues with SCOPE_IDENTITY(); this might be an application / connection issue and not a problem with SCOPE_IDENTITY(). I am certainly interested in the outcome of this. Can somebody please check me on this?|||

If you are using embedded SQL in your application it might be worth placing this logic into a stored procedure and calling that from your application. That should avoid any comms problems as the procedure will run or not run as a single call (and not have a problem between statements in the operation).

|||

Yes, the web app uses embedded SQL in classic ASP. The application was written in classic ASP and there has never been a good reason to rewrite it. The web app is the only application that performs DML on the table--there are no separate triggers or other ways into the table.

How could an embedded SQL statement in a single Web page cause scope problems? One Web page consulted during the research on this problem said this situation should be treated as a single scope.

I will probably try the stored procedure method. But I am curious as to why all sources practically demand that SCOPE_IDENTITY be used within a stored procedure when it is allowed to work in other situations.

Thanks for the input.

Problem inserting into DB2 from SQL Server via Linked Server

Here goes: (good luck understanding all this)
I have data in SQL Server that needs to be inserted into DB2. I have
installed the IBM DB2 Client Configuration Assistant on the SQL Server and
created a DSN. I can use the IBM Command Center to execute commands (both
reads and writes) successfully. I then created a Linked Server on SQL
Server that uses the Microsoft OLE DB provider for ODBC Connections in order
to connect to DB2. Using this method, I can run SELECT statements
successfully. When I try to do any writes (a DELETE for example), I get
this error message:
OLE DB provider 'MSDASQL' could not delete from table '"HCEDB"."APPLQUE2"'.
User did not have sufficient permission to delete the row.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
[OLE/DB provider returned message: [IBM][CLI Driver] CLI0150E Driver not
capable. SQLSTATE=S1C00]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
I verified that the user on DB2 has appropriate permissions and have on the
Security tab of the linked server properties page specified that user with
its password. Personally, I think that the whole SQL Server-->OLE
DB-->ODBC-->DB2 route is kinda obtuse. I would love to get rid of ODBC
alltogether. Unfortunately, the Microsoft OLE DB Provider for DB2 only
comes with their Host Integration Server product (which we don't have and
wouldn't be able to install on the SQL Server anyway) and I don't have the
equivalent IBM OLE DB driver...
Any recommendations or suggestions on how I can get the writes working?
Thanks..
Peace,
Gary Hampson
SQL Server - Siebel DBA
Application Development Group - IS
Horizon Blue Cross Blue Shield of New Jersey"Driver not capable" sounds like whatever driver you are using doesn't meet
the minimum capabilities required for the type of operation you are
performing. Drivers will often be queried for standards compliance and this
is the type of error that's reported when the driver does not report that
it supports one or more requested standards.
Do you have the latest version of all drivers involved on both the SQL
Server machine and the DB2 side? Do you have the latest SQL Server service
pack and security rollup (SP3 + MS03-031)?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.