6

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>
nam
  • 21,967
  • 37
  • 158
  • 332

1 Answers1

7

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:

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.

Community
  • 1
  • 1
Leigh
  • 28,765
  • 10
  • 55
  • 103
  • 4
    This was my guess as well. @nam - look for `cfquery` tags that have a `result` attribute. This result has a key `GENERATEDKEY` which CF stores the inserted identity – beloitdavisja Oct 20 '15 at 17:29
  • 2
    @beloitdavisja - 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 (@nam - note the underscore). – Leigh Oct 20 '15 at 17:42
  • 2
    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. – Sev Roberts Oct 20 '15 at 18:13
  • 2
    FWIW, `GENERATEDKEY` was added in CF 9. – Adrian J. Moreno Oct 20 '15 at 18:19
  • @Leigh I've added an Update Section that answers questions from people who have added helpful comments. – nam Oct 20 '15 at 19:03
  • 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. – Leigh Oct 20 '15 at 20:02