为什么会这样呢?
我在开发数据库SQL2005 执行这个语句通过,而在生产库SQL 2005 却报错 红线标示的提示错误信息
消息 102,级别 15,状态 1,第 19 行
'.' 附近有语法错误。
消息 102,级别 15,状态 1,第 22 行
'.' 附近有语法错误。
——————————————————————————————————————————————————————————SELECT DB_ID(DB.dbid) '数据库名'
, OBJECT_ID(db.objectid) '对象'
, QS.creation_time '编译计划的时间'
, QS.last_execution_time '上次执行计划的时间'
, QS.execution_count '执行的次数'
, QS.total_elapsed_time / 1000 '占用的总时间(秒)'
, QS.total_physical_reads '物理读取总次数'
, QS.total_worker_time / 1000 'CPU 时间总量(秒)'
, QS.total_logical_writes '逻辑写入总次数'
, QS.total_logical_reads N'逻辑读取总次数'
, QS.total_elapsed_time / 1000 N'总花费时间(秒)'
, SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset ) / 2 ) + 1) AS '执行语句'
FROM sys.dm_exec_query_stats AS QS CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN
( SELECT *
FROM sys.dm_exec_cached_plans cp CROSS APPLY
sys.dm_exec_query_plan(cp.plan_handle)
) DB
ON QS.plan_handle = DB.plan_handle
where SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2 ) + 1) not like '%fetch%'
ORDER BY QS.total_elapsed_time / 1000 DESC
我在开发数据库SQL2005 执行这个语句通过,而在生产库SQL 2005 却报错 红线标示的提示错误信息
消息 102,级别 15,状态 1,第 19 行
'.' 附近有语法错误。
消息 102,级别 15,状态 1,第 22 行
'.' 附近有语法错误。
——————————————————————————————————————————————————————————SELECT DB_ID(DB.dbid) '数据库名'
, OBJECT_ID(db.objectid) '对象'
, QS.creation_time '编译计划的时间'
, QS.last_execution_time '上次执行计划的时间'
, QS.execution_count '执行的次数'
, QS.total_elapsed_time / 1000 '占用的总时间(秒)'
, QS.total_physical_reads '物理读取总次数'
, QS.total_worker_time / 1000 'CPU 时间总量(秒)'
, QS.total_logical_writes '逻辑写入总次数'
, QS.total_logical_reads N'逻辑读取总次数'
, QS.total_elapsed_time / 1000 N'总花费时间(秒)'
, SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset ) / 2 ) + 1) AS '执行语句'
FROM sys.dm_exec_query_stats AS QS CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN
( SELECT *
FROM sys.dm_exec_cached_plans cp CROSS APPLY
sys.dm_exec_query_plan(cp.plan_handle)
) DB
ON QS.plan_handle = DB.plan_handle
where SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2 ) + 1) not like '%fetch%'
ORDER BY QS.total_elapsed_time / 1000 DESC
--没有改动SELECT DB_ID(DB.dbid) '数据库名'
, OBJECT_ID(db.objectid) '对象'
, QS.creation_time '编译计划的时间'
, QS.last_execution_time '上次执行计划的时间'
, QS.execution_count '执行的次数'
, QS.total_elapsed_time / 1000 '占用的总时间(秒)'
, QS.total_physical_reads '物理读取总次数'
, QS.total_worker_time / 1000 'CPU 时间总量(秒)'
, QS.total_logical_writes '逻辑写入总次数'
, QS.total_logical_reads N'逻辑读取总次数'
, QS.total_elapsed_time / 1000 N'总花费时间(秒)'
, SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset ) / 2 ) + 1) AS '执行语句'
FROM sys.dm_exec_query_stats AS QS CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN
( SELECT *
FROM sys.dm_exec_cached_plans cp CROSS APPLY
sys.dm_exec_query_plan(cp.plan_handle)
) DB
ON QS.plan_handle = DB.plan_handle
where SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2 ) + 1) not like '%fetch%'
ORDER BY QS.total_elapsed_time / 1000 DESC
/*
数据库名 对象 编译计划的时间 上次执行计划的时间 执行的次数 占用的总时间(秒) 物理读取总次数 CPU 时间总量(秒) 逻辑写入总次数 逻辑读取总次数 总花费时间(秒) 执行语句
------ ----------- ----------------------- ----------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL NULL 2011-04-07 09:31:45.827 2011-04-07 09:31:45.967 1 33 1 23 0 18 33 select smpermissi0_.id as id196_, smpermissi0_.name as name196_, smpermissi0_.parentId as parentId196_, smpermissi0_.path as path196_, smpermissi0_.fullPath as fullPath196_, smpermissi0_.re as re196_ from LianShengDB.dbo.SM_Permissions smpermissi0_
NULL NULL 2011-04-07 08:42:04.187 2011-04-07 08:42:04.217 1 33 3 26 0 767 33 SELECT
'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/Table[@Name=' + quotename(tbl.name,'''') + ' and @Schema=' + quotename(SCHEMA_NAME(tbl.schema_id),'''')
NULL NULL 2011-04-07 08:41:57.890 2011-04-07 08:41:57.890 1 23 0 23 0 4 23 SELECT
cfg.name AS [Name],
cfg.configuration_id AS [Number],
cfg.minimum AS [Minimum],
cfg.maximum AS [Maximum],
cfg.is_dynamic AS [Dynamic],
cfg.is_advanced AS [Advanced],
cfg.value AS [ConfigValue],
cfg.value_in_use AS [RunValue],
cfg.description AS [Des
NULL NULL 2011-04-07 08:42:01.780 2011-04-07 08:42:01.840 1 22 0 22 0 40 22 SELECT
dtb.name AS [Database_Name],
'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' + '/Database[@Name=' + quotename(dtb.name,'''') + ']' AS [Database_Urn],case
when DATABASEPROPERTY(dtb.name,'IsShutDown') is
NULL NULL 2011-04-07 08:41:57.233 2011-04-07 08:41:57.310 1 9 0 9 0 2 9 SELECT se.is_admin_endpoint AS N'AdminConnection'
FROM sys.endpoints se
INNER JOIN sys.dm_exec_connections dmc ON dmc.endpoint_id = se.endpoint_id
WHERE
NULL NULL 2011-04-07 09:55:12.000 2011-04-07 09:55:12.013 1 8 0 8 0 12 8 select a.aid,a.anum - sum(b.bnum) as asum
from tb a left join ta b on a.aid = b.aid
group by a.aid,a.anum
...(30 行受影响)
(30 行受影响)
sys.dm_exec_sql_text(QS.sql_handle) AS ST
我这个函数