Menu
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>
454
SCOPE_IDENTITY() returns the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function returns the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.
The scope_identity() function returns the last identity created in the same session and the same scope. The ident_current(name) returns the last identity created for a specific table or view in any session. The identity() function is not used to get an identity, it's used to create an identity in a select...into query.
From the documentation of SCOPE_IDENTITY(), emphasis mine: Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch.
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.
117
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
