CF9 IDENTITYCOL bug?

[paule1234]

This one is hard for us to reproduce...  we recently upgraded from CF8 Enterprise to CF9 Enterprise (version 9,0,0,251028), connected to a SQL Server 2005 SP3 database (version 9.0.4053) ... we have a CFQUERY that does an INSERT and is occassionally failing to return an IDENTITYCOL in the result structure.  The record is correctly inserted into the table and no error is thrown by the CFQUERY.  The datasource is using the Microsoft SQL Server driver.

The OS is 32-bit Windows 2003 SP2 Enterprise.

[Mr_Vball]

Has anyone found a solution to this? We are running into the same issue using the same version of CF9 on SQL 2000. It is very hard to diagnose because it happens intermittenly.

TIA

[paule1234]

FWIW, I ended up changing my code to the "old fashioned" SELECT @@IDENTITY AS new_id technique in order to eliminate the errors on our CF9 system.

[-__cfSearching__-]

Generally SCOPE_INDENTITY() should be used instead of @@IDENTITY

http://msdn.microsoft.com/en-us/library/ms190315.aspx