declare @all_counters int
-- sp_sqlagent_get_perf_counters
SELECT 'object_name' = RTRIM(SUBSTRING(spi1.object_name, 1, 50)),
'counter_name' = RTRIM(SUBSTRING(spi1.counter_name, 1, 50)),
'instance_name' = CASE spi1.instance_name
WHEN N'' THEN NULL
ELSE RTRIM(spi1.instance_name)
END,
'value' = CASE
WHEN (spi1.cntr_type = 537003008) -- A ratio
THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
FROM master.dbo.sysperfinfo spi2
WHERE (spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('% Base%', spi2.counter_name)))
AND (spi1.instance_name = spi2.instance_name)
AND (spi2.cntr_type = 1073939459))
WHEN (spi1.cntr_value < 0)
THEN CONVERT(FLOAT, CONVERT(bigint, spi1.cntr_value) + convert(bigint, 0x100000000))
ELSE spi1.cntr_value
END,
'type' = spi1.cntr_type
FROM master.dbo.sysperfinfo spi1,
#temp tmp
WHERE (spi1.cntr_type <> 1073939459) -- Divisors
AND ((@all_counters = 1) OR
(tmp.performance_condition = RTRIM(spi1.object_name) + '|' + RTRIM(spi1.counter_name)))
-- sp_sqlagent_get_perf_counters
INSERT INTO #temp
SELECT DISTINCT SUBSTRING(performance_condition, 1, CHARINDEX('|', performance_condition, PATINDEX('%_|_%', performance_condition) + 2) - 1)
FROM msdb.dbo.sysalerts
WHERE (performance_condition IS NOT NULL)
AND (enabled = 1)
================================
服务器: 消息 208,级别 16,状态 1,行 4
对象名 '#temp' 无效。
-- sp_sqlagent_get_perf_counters
SELECT 'object_name' = RTRIM(SUBSTRING(spi1.object_name, 1, 50)),
'counter_name' = RTRIM(SUBSTRING(spi1.counter_name, 1, 50)),
'instance_name' = CASE spi1.instance_name
WHEN N'' THEN NULL
ELSE RTRIM(spi1.instance_name)
END,
'value' = CASE
WHEN (spi1.cntr_type = 537003008) -- A ratio
THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
FROM master.dbo.sysperfinfo spi2
WHERE (spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('% Base%', spi2.counter_name)))
AND (spi1.instance_name = spi2.instance_name)
AND (spi2.cntr_type = 1073939459))
WHEN (spi1.cntr_value < 0)
THEN CONVERT(FLOAT, CONVERT(bigint, spi1.cntr_value) + convert(bigint, 0x100000000))
ELSE spi1.cntr_value
END,
'type' = spi1.cntr_type
FROM master.dbo.sysperfinfo spi1,
#temp tmp
WHERE (spi1.cntr_type <> 1073939459) -- Divisors
AND ((@all_counters = 1) OR
(tmp.performance_condition = RTRIM(spi1.object_name) + '|' + RTRIM(spi1.counter_name)))
-- sp_sqlagent_get_perf_counters
INSERT INTO #temp
SELECT DISTINCT SUBSTRING(performance_condition, 1, CHARINDEX('|', performance_condition, PATINDEX('%_|_%', performance_condition) + 2) - 1)
FROM msdb.dbo.sysalerts
WHERE (performance_condition IS NOT NULL)
AND (enabled = 1)
================================
服务器: 消息 208,级别 16,状态 1,行 4
对象名 '#temp' 无效。
FROM master.dbo.sysperfinfo spi1,
#temp tmp
你的#temp都没有insert过,哪来的对象?
INSERT INTO #temp
SELECT DISTINCT SUBSTRING(performance_condition, 1, CHARINDEX('|', performance_condition, PATINDEX('%_|_%', performance_condition) + 2) - 1)
FROM msdb.dbo.sysalerts
WHERE (performance_condition IS NOT NULL)
AND (enabled = 1)
declare @all_counters int
-- sp_sqlagent_get_perf_counters
SELECT 'object_name' = RTRIM(SUBSTRING(spi1.object_name, 1, 50)),
'counter_name' = RTRIM(SUBSTRING(spi1.counter_name, 1, 50)),
'instance_name' = CASE spi1.instance_name
WHEN N'' THEN NULL
ELSE RTRIM(spi1.instance_name)
END,
'value' = CASE
WHEN (spi1.cntr_type = 537003008) -- A ratio
THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
FROM master.dbo.sysperfinfo spi2
WHERE (spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('% Base%', spi2.counter_name)))
AND (spi1.instance_name = spi2.instance_name)
AND (spi2.cntr_type = 1073939459))
WHEN (spi1.cntr_value < 0)
THEN CONVERT(FLOAT, CONVERT(bigint, spi1.cntr_value) + convert(bigint, 0x100000000))
ELSE spi1.cntr_value
END,
'type' = spi1.cntr_type
FROM master.dbo.sysperfinfo spi1,
#temp tmp
WHERE (spi1.cntr_type <> 1073939459) -- Divisors
AND ((@all_counters = 1) OR
(tmp.performance_condition = RTRIM(spi1.object_name) + '|' + RTRIM(spi1.counter_name)))
这样试试
FROM master.dbo.sysperfinfo spi1,
#temp tmp
建议定义一个表变量或非系统临时表就可以,我就是这样做的''\
创建表变量:
DECLARE @T_ChartsIdeaItem1 Table(XData char(20) null,YData char(20) null)创建临时表:
createtable#临时表名(字段1约束条件,
字段2约束条件,
.....)