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>

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.