SQL Server Profiler showing SCOPE_IDENTITY() while ColdFusion code is not using it in any query

Question

I am using SQL Server 2008 R2 Profiler to debug an issue on a ColdFusion 7 application - that was developed by someone else - running on Windows 7 with SQL Server 2008 R2 as a backend. The application was originally using MS Access 2003 as a backend that was later converted to SQL Server 2008 R2. The profiler is showing the following SQL that is using SCOPE_IDENTITY() but when I search the application root directory using a search utility no file has the SCOPE_IDENTITY() function used anywhere in their SQL query. The SQL Server Database for the app does not have any stored procedure, views, functions etc. All the SQL queries are embedded queries inside ColdFusion files. Where then Profiler is getting SCOPE_IDENTITY() function:

declare @p1 int
set @p1=11
exec sp_prepexec @p1 output,N'@P1 datetimeoffset,@P2 varchar(8000),@P3 int,@P4 varchar(8000)',N'insert into ProductItems (item_date , item_description, item_type)
values (
@P1 ,
@P2 ,
@P3 , 
) select SCOPE_IDENTITY()','2015-10-19 00:00:00 +00:00','Test description',1
select @p1

UPDATE Although originally the app was developed in CF 7, CF 7 was later upgraded to CF9 and now I'm debugging it on local machine that has CF 11. I don't know if the code was also upgraded when CF 7 was replaced with CF 8 and then with CF 9. The CFquery that seems to generate the above SQL in the profiler looks like. Moreover, the table ProductItems does have an identity column, the Database is not using any triggers, and the CFquery tags are not using result attribute:

<cfquery name="addProductItems" datasource="#dtsource#">
  insert into Productitems (item_date,item_description,item_type)  
  values (
    <cfqueryPARAM value = "#item_dat#" CFSQLType = "CF_SQL_TIMESTAMP" null="#item_dat eq '-1'#">,
    <cfqueryPARAM value = "#item_description#" CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryPARAM value = "#item_type#" CFSQLType = "CF_SQL_INTEGER">
    )
</cfquery>

Bimetallism

What is the actual CF code for the INSERT above? – Deathless

Answer 1

My guess would be the CF server is adding it automatically. I know you said you are using MX7, but ... back in ColdFusion 8, a new feature was introduced that retrieves the generated ID's from simple INSERT statements. In SQL Server it was accomplished by appending SELECT SCOPE_IDENTITY() to the INSERT query. That definitely caused a few problems at the time. For more details, see:

• Problems with CF 8's Generated Keys Feature
• Two important CF8 bugs/issues detailed yesterday

NB: The implementation may have changed in later versions.

As beloitdavisja mentioned in the comments, look for cfquery tags having the result attribute. Result is a structure containing details about the query executed. In CF8, the generated record ID is returned under the key IDENTITYCOL. In later versions, it also contains the database agnostic version, GENERATEDKEY.

Deathless

This was my guess as well. @Bimetallism - look for cfquery tags that have a result attribute. This result has a key GENERATEDKEY which CF stores the inserted identity – Slavism

@Slavism - Yep. Though, IIRC the db-agnostic GENERATEDKEY did not exist in CF8. It was added later. CF8, only supported GENERATED_KEY for MySQL db's (@Bimetallism - note the underscore). – Deathless

Don't forget it's also worth checking that you haven't got any triggers defined in the database as these can execute SQL that you won't see in your CF code search. However as above the stored procedure call looks very much like the prepared statement that you'd expect the DB driver to output for a cfquery when cfqueryparam has been used, and the SCOPE_IDENTITY() would be expected in CF8 onwards when the cfquery result attribute has been specified. Just strange to see that if you're really still on CF7. – Tamra

FWIW, GENERATEDKEY was added in CF 9. – Nonego

@Deathless I've added an Update Section that answers questions from people who have added helpful comments. – Bimetallism

Thanks @nam. RE CFquery tags are not using result attribute I do not recall the answer off the top of my head, but it is entirely possible CF always adds scope_identity() for simple inserts, regardless of whether or not "result" is used. – Deathless