OLE DB Connection + SET NOCOUNT ON problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

OLE DB Connection + SET NOCOUNT ON problem

Author

Topic

janevok
Starting Member

1 Post

Posted - 2008-03-20 : 08:09:20

What I am Trying To Achieve
I am trying to get the ID of the row I am inserting - using code that works fine on: SQL Server 2000 with an ODBC connection and SQL Server 2005 with an ODBC connection

Problem
When the code is executed it inserts the row 4 times.

My Settings
I have the following OLE DB connection string:-
Provider=SQLOLEDB.1;User ID=myuserid;Initial Catalog=dbname;Data Source=dbserveraddress;User Id=myuserid;PASSWORD=mypassword;

and I am executing the following SQL:-
session("sessionID") = dbExecuteRS("SET NOCOUNT ON;INSERT INTO myTable (ipAddress) VALUES('"& request.serverVariables("REMOTE_HOST") &"');SELECT SCOPE_IDENTITY();SET NOCOUNT OFF;")(0)

dbExecuteRS = a function that executes a query

I know my asp page is only executing this sql once but the SQL Profiler says it is running multiple times.

What I've Tried So Far
1) A normal insert works fine - only inserts one row
2) The same SQL with an OLE DB connection string on SQL Server 2000 encounters the same problem - the insert sql with the set nocount on inserts 4 rows
3) I've tried using this bit of SQL/ASP which causes the same problem:-
set tmpRS = dbExecuteRS("SET NOCOUNT ON;INSERT INTO mytable (ipAddress) VALUES('"& request.serverVariables("REMOTE_HOST") &"');SELECT newID = SCOPE_IDENTITY();")
if not tmpRS.eof then
response.write("tmpRS('newID'): " & tmpRS("newID") & "<BR>")
end if
set tmpRS = nothing

Conclusion
From all of my tests I can conclude that this problem ONLY happens when we are connecting to the database via the OLE DB connection string and using an INSERT statement with SET NOCOUNT ON

All ideas/suggestions appreciated, thanks.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-21 : 00:12:18

Sounds bug, did you report to Microsoft?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources