SQL Server profiler showing SCOPE_IDENTITY () while ColdFusion code does not use it in any query

Question

I am using SQL Server 2008 R2 Profiler to debug a problem in a ColdFusion 7 application that was developed by someone else - it runs on Windows 7 with SQL Server 2008 R2 as a backend. The application originally used MS Access 2003 as a backend, which was subsequently converted to SQL Server 2008 R2. The profiler shows the following SQL, which uses SCOPE_IDENTITY (), but when searching the application’s root directory with the search utility, not a single file has the SCOPE_IDENTITY () function used anywhere in its SQL query. The SQL Server database for the application does not have a stored procedure, views, functions, etc. All SQL queries are inline queries in ColdFusion files. Where then Profiler gets the 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 the application was originally developed in CF 7, CF 7 was later upgraded to CF9, and now I am debugging it on the local machine with CF 11. I do not know if the code was also updated when CF 7 was replaced by CF 8 and then with CF 9. It seems that CFquery, which seems to generate the above SQL in the profiler. Moreover, the ProductItems table has an identifier column, the database does not use triggers, and CFquery tags do not use the 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> 

Answer 1

I assume the CF server will add it automatically. I know that you said that you were using MX7, but ... back in ColdFusion 8 there was a new function that extracts the generated identifier from simple INSERT . In SQL Server, this was accomplished by adding SELECT SCOPE_IDENTITY() to the INSERT query. This definitely caused several problems at the time. For more information see:

• Problems with CF 8 function generated keys
• Two important CF8 bugs / issues detailed yesterday

NB: Implementation may be changed in later versions.

Like the beloitdavisja mentioned in the comments , find the cfquery tags that have the result attribute. result is a structure containing information about a completed query. In CF8, the generated record identifier is returned under the IDENTITYCOL key. In later versions, it also contains an agnostic version of the GENERATEDKEY database.