SQL Server Profiler显示SCOPE_IDENTITY（），而ColdFusion代码未在任何查询中使用它

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:

我正在使用SQL Server 2008 R2 Profiler调试ColdFusion 7应用程序(由其他人开发)上的问题 - 在Windows 7上运行,SQL Server 2008 R2作为后端。该应用程序最初使用MS Access 2003作为后端,后来转换为SQL Server 2008 R2。分析器显示以下使用SCOPE_IDENTITY()的SQL,但是当我使用搜索实用程序搜索应用程序根目录时,没有文件在其SQL查询中的任何位置使用SCOPE_IDENTITY()函数。该应用程序的SQL Server数据库没有任何存储过程,视图,函数等。所有SQL查询都是ColdFusion文件中的嵌入式查询。然后Profiler获得SCOPE_IDENTITY()函数:

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:

更新虽然最初的应用程序是在CF 7中开发的,但CF 7后来升级到了CF9,现在我在具有CF 11的本地机器上进行调试。我不知道在更换CF 7时代码是否也升级了CF 8,然后是CF 9.在查询器中似乎生成上述SQL的CFquery看起来像。此外,表ProductItems确实有一个标识列,数据库没有使用任何触发器,CFquery标签没有使用结果属性:

<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>

1 个解决方案

#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:

我的猜测是CF服务器会自动添加它。我知道你说你正在使用MX7,但是......回到ColdFusion 8中,引入了一个新功能,它从简单的INSERT语句中检索生成的ID。在SQL Server中,它是通过将SELECT SCOPE_IDENTITY()附加到INSERT查询来完成的。这肯定在当时引起了一些问题。有关详细信息,请参阅:

Problems with CF 8's Generated Keys Feature

CF 8生成密钥功能的问题

Two important CF8 bugs/issues detailed yesterday

昨天详细介绍了两个重要的CF8错误/问题

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.

正如评论中提到的beloitdavisja,查找具有result属性的cfquery标签。结果是包含有关所执行查询的详细信息的结构。在CF8中,生成的记录ID在密钥IDENTITYCOL下返回。在以后的版本中,它还包含数据库不可知版本GENERATEDKEY。

#1

Problems with CF 8's Generated Keys Feature