Datatype & Variable Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Datatype & Variable Problem

Author

Topic

heygee
Starting Member

27 Posts

Posted - 2005-07-22 : 10:56:06

All,

I have the script below which isn't returning data to the table. I think that the variable @value doesn't get data allocated to it, subsiquently the variable that gets assigned the data from @value is blank too. Can anyone let me know where I am going wrong please?

TIA,
Gee

CREATE PROCEDURE sp_DBAStats
AS
DECLARE @dbname nvarchar(20)
DECLARE @v_srvname nvarchar(30)
DECLARE @cmd nvarchar(1024)
DECLARE @today datetime
DECLARE @diff int
DECLARE @PARAMDEF nvarchar(200)
DECLARE @value nvarchar(30)
DECLARE @connection nvarchar(30)
DECLARE @Cachehitratio nvarchar(30)

SET @diff = datediff(d, '2005-01-01', @today)
SET @today = dateadd(d, datediff(d, '2005-01-01', current_timestamp),'2005-01-01')

DECLARE srv_cursor CURSOR FOR
SELECT srvname FROM master.dbo.sysservers

OPEN srv_cursor
FETCH NEXT FROM srv_cursor INTO @v_srvname
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = 'SELECT ' + @value + ' = (CONVERT(DECIMAL(5,2),(CONVERT(FLOAT(2),cntr_value))/(CONVERT(FLOAT(2),1024)))) FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N' + char(39) + '''SQLServer:Memory Manager' + char(39) + ''' AND counter_name = N' + char(39) + '''Connection Memory (KB)' + char(39) + ''' SET ' + @connection + ' = ' + @value + ''
EXECUTE sp_executesql @cmd

SET @cmd = 'SELECT ' + @value + ' = (CONVERT(DECIMAL (5,2),(CONVERT(FLOAT(2),s1.cntr_value))/(CONVERT(FLOAT(2),s2.cntr_value))*100)) FROM ' + @v_srvname + '.master.dbo.sysperfinfo s1 INNER JOIN master.dbo.sysperfinfo s2 ON s1.object_name = s2.object_name WHERE s1.counter_name = N' + char(39) + '''Buffer cache hit ratio' + char(39) + ''' AND s2.counter_name = N' + char(39) + '''Buffer cache hit ratio base' + char(39) + ''' SET ' + @Cachehitratio + ' = ' + @value + ''
EXECUTE sp_executesql @cmd

INSERT INTO DBAstats..PerfMonInfo Values(@today, @v_srvname, @connection, @Cachehitratio)

FETCH NEXT FROM srv_cursor INTO @v_srvname
END
CLOSE srv_cursor
DEALLOCATE srv_cursor

Gee

"Happiness is a good health and a bad memory!"

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-22 : 11:57:16

Is this the entire proc? Like you said, I don't see anything that assigns any value to the variable @value. Therefore, if @value is NULL, then the entire @cmd variable will become NULL. Plus, your @diff variable will always be NULL because you are assigning a value to @today after you assign a value to @diff (although, I don't even see where you need to use @diff).

Sorry, I just now realize what you are trying to do. You won't be able to assign a value to @value in that way. Let me find a better way in a minute ...

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-22 : 12:01:38

You are trying to assign a value to a variable within a dynamic SQL statement. Without actually translating your statements, you need to do something like this:

SET @str = 'SELECT @value = COUNT(*) FROM ' + @table_name

EXECUTE sp_executesql @str, N'@value int OUTPUT', @value OUTPUT

heygee
Starting Member

27 Posts

Posted - 2005-07-25 : 04:09:09

Many thanks for your help. I will give this a try and let you know how I get on.
Regards, Gee

Gee

"Happiness is a good health and a bad memory!"

heygee
Starting Member

27 Posts

Posted - 2005-07-25 : 08:34:40

Hi, I have made some changes but can't get it to work completely. The code below is for half of the complete sproc i am working on, as the first half works fine now. The first part works ok and passes data into the table (@connection for Connection Memory), but I can't get the next one to work for Cach Hit Ratio. I think it may be because of the s1 and s2 in the code, but can't figure it out. I still need to go through and test the rest too, but they should follow on from this code as they are similar to the first two. Any ideas?
TIA, Gee

IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_DBAStats]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_DBAStats]
GO

CREATE PROCEDURE sp_DBAStats
AS
DECLARE @dbname nvarchar(20)
DECLARE @v_srvname nvarchar(30)
DECLARE @cmd nvarchar(1024)
DECLARE @today datetime
DECLARE @diff int
DECLARE @value nvarchar(30)

SET @diff = datediff(d, '2005-01-01', @today)
SET @today = dateadd(d, datediff(d, '2005-01-01', current_timestamp),'2005-01-01')

DECLARE srv_cursor CURSOR FOR
SELECT srvname FROM master.dbo.sysservers

WHERE srvname NOT IN ('DE00415', 'GENCORBSAPP01')

OPEN srv_cursor
FETCH NEXT FROM srv_cursor INTO @v_srvname
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = '
DECLARE @connection nvarchar(30)
DECLARE @Cachehitratio decimal(18,2)
DECLARE @Freepages decimal(18,2)
DECLARE @SQLTotMemory decimal(18,2)
DECLARE @SQLTargetMemory decimal(18,2)
DECLARE @PageSplits decimal(18,2)
DECLARE @transactions decimal(18,2)
DECLARE @userconnections decimal(18,2)
DECLARE @deadlocks decimal(18,2)
DECLARE @lockwait decimal(18,2)
DECLARE @totaldatagrowth decimal(18,2)
DECLARE @totalloggrowth decimal(18,2)
DECLARE @batchrequest decimal(18,2)
DECLARE @compilation decimal(18,2)
DECLARE @LazyWrites decimal(18,2)

SELECT @connection = (CONVERT(DECIMAL(5,2),(CONVERT(FLOAT(2),cntr_value))/(CONVERT(FLOAT(2),1024)))) FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Memory Manager'' AND counter_name = N''Connection Memory (KB)''

SELECT @Cachehitratio = (CONVERT(DECIMAL (5,2),(CONVERT(FLOAT(2),s1.cntr_value))/(CONVERT(FLOAT(2),s2.cntr_value))*100)) FROM ' + @v_srvname + '.master.dbo.sysperfinfo s1 INNER JOIN master.dbo.sysperfinfo s2 ON s1.object_name = s2.object_name WHERE s1.counter_name = N''Buffer cache hit ratio'' AND s2.counter_name = N''Buffer cache hit ratio base''

SELECT @Freepages = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE counter_name = N''Free Pages'' AND object_name = N''SQLServer:Buffer Manager''

SELECT @SQLTotMemory = (CONVERT(DECIMAL(5,2),(CONVERT(FLOAT(2),cntr_value))/(CONVERT(FLOAT(2),1024)))) FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Memory Manager'' AND counter_name = N''Total Server Memory (KB)''

SELECT @SQLTargetMemory = (CONVERT(DECIMAL(5,2),(CONVERT(FLOAT(2),cntr_value))/(CONVERT(FLOAT(2),1024)))) FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Memory Manager'' AND counter_name = N''Target Server Memory(KB)''

SELECT @PageSplits = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Access Methods'' AND counter_name = N''Page Splits/sec''

SELECT @transactions = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Databases'' AND counter_name = N''Transactions/sec'' AND instance_name = N''_Total''

SELECT @userconnections = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:General Statistics'' AND counter_name = N''User Connections''

SELECT @deadlocks = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Locks'' AND instance_name = N''Table'' AND counter_name = N''Number of Deadlocks/sec''

SELECT @lockwait = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Locks'' AND counter_name = N''Average Wait Time (ms)'' AND instance_name = N''_Total''

SELECT @totaldatagrowth = cntr_value/1024 FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Databases'' AND counter_name = N''Data File(s) Size (KB)'' AND instance_name = N''_Total''

SELECT @totalloggrowth = cntr_value/1024 FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Databases'' AND counter_name = N''Log File(s) Size (KB)'' AND instance_name = N''_Total''

SELECT @batchrequest = (CONVERT(DECIMAL(5,2),(CONVERT(FLOAT(2),cntr_value))/(CONVERT(FLOAT(2),1024)))) FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:SQL Statistics'' AND counter_name = N''Batch Requests/sec''

SELECT @compilation = (CONVERT(DECIMAL(5,2),(CONVERT(FLOAT(2),cntr_value))/(CONVERT(FLOAT(2),1024)))) FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:SQL Statistics'' AND counter_name = N''SQL Compilations/Sec''

SELECT @LazyWrites = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Buffer Manager'' AND counter_name = N''Lazy writes/sec''

INSERT INTO DBAstats..PerfMonInfo
VALUES ( dateadd(d, datediff(d, ''2005-01-01'', current_timestamp),''2005-01-01''),
''' + @v_srvname + ''', @connection, @Cachehitratio, @Freepages, @SQLTotMemory,
@SQLTargetMemory, @PageSplits, @transactions, @userconnections, @deadlocks,
@lockwait, @totaldatagrowth, @totalloggrowth, @batchrequest, @compilation, @LazyWrites)'

EXECUTE sp_executesql @cmd, N'@value int OUTPUT', @value OUTPUT

FETCH NEXT FROM srv_cursor INTO @v_srvname
END
CLOSE srv_cursor
DEALLOCATE srv_cursor

Gee

"Happiness is a good health and a bad memory!"

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-25 : 11:54:47

Is it possible that your @cmd string is longer than 1024 characters?

heygee
Starting Member

27 Posts

Posted - 2005-07-25 : 12:22:56

It was too long and I have split the dynamic sql into 3 chunks now, but I'm not able to concatenate the outcome to run as a final block of sql for the data. I think there is a datatype problems some within too... if its not one thing then another! Any ideas?

IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_DBAStats]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_DBAStats]
GO

CREATE PROCEDURE sp_DBAStats
AS
DECLARE @dbname nvarchar(20)
DECLARE @v_srvname nvarchar(30)
DECLARE @str1 nvarchar(4000)
DECLARE @str2 nvarchar(4000)
DECLARE @cmd nvarchar(4000)
DECLARE @runcmd nvarchar(4000)
DECLARE @today datetime
DECLARE @diff int
DECLARE @value nvarchar(4000)

SET @diff = datediff(d, '2005-01-01', @today)
SET @today = dateadd(d, datediff(d, '2005-01-01', current_timestamp),'2005-01-01')

DECLARE srv_cursor CURSOR FOR
SELECT srvname FROM master.dbo.sysservers

WHERE srvname NOT IN ('DE00415', 'GENCORBSAPP01')

OPEN srv_cursor
FETCH NEXT FROM srv_cursor INTO @v_srvname
WHILE @@FETCH_STATUS = 0
BEGIN

SET @str1 = '
DECLARE @connection nvarchar(30)
DECLARE @Cachehitratio decimal(18,2)
DECLARE @Freepages decimal(18,2)
DECLARE @SQLTotMemory decimal(18,2)
DECLARE @SQLTargetMemory decimal(18,2)
DECLARE @PageSplits decimal(18,2)
DECLARE @transactions decimal(18,2)

SELECT @connection = (CONVERT(DECIMAL(5,2),(CONVERT(FLOAT(2),cntr_value))/(CONVERT(FLOAT(2),1024)))) FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Memory Manager'' AND counter_name = N''Connection Memory (KB)''
SELECT @Cachehitratio = (CONVERT(DECIMAL (5,2),(CONVERT(FLOAT(2),s1.cntr_value))/(CONVERT(FLOAT(2),s2.cntr_value))*100)) FROM ' + @v_srvname + '.master.dbo.sysperfinfo s1 INNER JOIN master.dbo.sysperfinfo s2 ON s1.object_name = s2.object_name WHERE s1.counter_name = N''Buffer cache hit ratio'' AND s2.counter_name = N''Buffer cache hit ratio base''
SELECT @Freepages = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE counter_name = N''Free Pages'' AND object_name = N''SQLServer:Buffer Manager''
SELECT @SQLTotMemory = (CONVERT(DECIMAL(5,2),(CONVERT(FLOAT(2),cntr_value))/(CONVERT(FLOAT(2),1024)))) FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Memory Manager'' AND counter_name = N''Total Server Memory (KB)''
SELECT @SQLTargetMemory = (CONVERT(DECIMAL(5,2),(CONVERT(FLOAT(2),cntr_value))/(CONVERT(FLOAT(2),1024)))) FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Memory Manager'' AND counter_name = N''Target Server Memory(KB)''
SELECT @PageSplits = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Access Methods'' AND counter_name = N''Page Splits/sec''
SELECT @transactions = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Databases'' AND counter_name = N''Transactions/sec'' AND instance_name = N''_Total''
'

--EXECUTE sp_executesql @str1, N'@value int OUTPUT', @value OUTPUT

SET @str2 = '
DECLARE @userconnections decimal(18,2)
DECLARE @deadlocks decimal(18,2)
DECLARE @lockwait decimal(18,2)
DECLARE @totaldatagrowth decimal(18,2)
DECLARE @totalloggrowth decimal(18,2)
DECLARE @batchrequest decimal(18,2)
DECLARE @compilation decimal(18,2)
DECLARE @LazyWrites decimal(18,2)

SELECT @userconnections = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:General Statistics'' AND counter_name = N''User Connections''
SELECT @deadlocks = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Locks'' AND instance_name = N''Table'' AND counter_name = N''Number of Deadlocks/sec''
SELECT @lockwait = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Locks'' AND counter_name = N''Average Wait Time (ms)'' AND instance_name = N''_Total''
SELECT @totaldatagrowth = cntr_value/1024 FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Databases'' AND counter_name = N''Data File(s) Size (KB)'' AND instance_name = N''_Total''
SELECT @totalloggrowth = cntr_value/1024 FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Databases'' AND counter_name = N''Log File(s) Size (KB)'' AND instance_name = N''_Total''
SELECT @batchrequest = (CONVERT(DECIMAL(5,2),(CONVERT(FLOAT(2),cntr_value))/(CONVERT(FLOAT(2),1024)))) FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:SQL Statistics'' AND counter_name = N''Batch Requests/sec''
SELECT @compilation = (CONVERT(DECIMAL(5,2),(CONVERT(FLOAT(2),cntr_value))/(CONVERT(FLOAT(2),1024)))) FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:SQL Statistics'' AND counter_name = N''SQL Compilations/Sec''
SELECT @LazyWrites = cntr_value FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Buffer Manager'' AND counter_name = N''Lazy writes/sec''
'

--EXECUTE sp_executesql @str2, N'@value int OUTPUT', @value OUTPUT

SET @cmd = '
INSERT INTO DBAstats..PerfMonInfo
VALUES (dateadd(d, datediff(d, ''2005-01-01'', current_timestamp), ''2005-01-01''), ''' + @v_srvname + ''', @connection,
@Cachehitratio, @Freepages, @SQLTotMemory, @SQLTargetMemory, @PageSplits, @transactions, @userconnections, @deadlocks,
@lockwait, @totaldatagrowth, @totalloggrowth, @batchrequest, @compilation, @LazyWrites)'

print '' + @str1 + ''
print '' + @str2 + ''
print '' + @cmd + ''
--SET @runcmd = print '' + @str1 + '', print '' + @str2 + '', print '' + @cmd + ''

--EXECUTE sp_executesql @runcmd, N'@value int OUTPUT', @value OUTPUT

FETCH NEXT FROM srv_cursor INTO @v_srvname
END
CLOSE srv_cursor
DEALLOCATE srv_cursor

Gee

"Happiness is a good health and a bad memory!"

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-25 : 17:50:42

I don't think you're going to be able to do the SQL statements in that way. For one, I think you're going to have to declare the variables outside of the "SET @str1 ..." command. And then your "EXECUTE sp_executesql" syntax will have to change. So it will look something like this:


DECLARE @connection decimal(5, 2)
DECLARE @Cachehitratio decimal(18,2)
SET @str1 = 'SELECT @connection = (CONVERT(DECIMAL(5,2),(CONVERT(FLOAT(2),cntr_value))/(CONVERT(FLOAT(2),1024)))) FROM ' + @v_srvname + '.master.dbo.sysperfinfo WHERE object_name = N''SQLServer:Memory Manager'' AND counter_name = N''Connection Memory (KB)'' 
SELECT @Cachehitratio = (CONVERT(DECIMAL (5,2),(CONVERT(FLOAT(2),s1.cntr_value))/(CONVERT(FLOAT(2),s2.cntr_value))*100)) FROM ' + @v_srvname + '.master.dbo.sysperfinfo s1 INNER JOIN master.dbo.sysperfinfo s2 ON s1.object_name = s2.object_name WHERE s1.counter_name = N''Buffer cache hit ratio'' AND s2.counter_name = N''Buffer cache hit ratio base''
'
EXECUTE sp_executesql @str1, N'@connection decimal(5,2) OUTPUT, @CacheHitratio decimal(18,2) OUTPUT', @connection OUTPUT, @CacheHitRatio OUTPUT

heygee
Starting Member

27 Posts

Posted - 2005-08-09 : 09:00:29

Many thanks for the reply. I have been able to resolve the matter since then and the script now looks like:
SET @cmd = 'SELECT @Freepages = cntr_value
FROM ' + @v_srvname + '.master.dbo.sysperfinfo
WHERE counter_name = N''Free Pages''
AND object_name = N''SQLServer:Buffer Manager'''
EXECUTE sp_executesql @cmd, N'@Freepages decimal(18,2) OUTPUT', @Freepages OUTPUT

This is just one portion of the script. Once it is completed then I will post the script for all to look at/use as it could be quite useful for a free monitoring solution. Thanks :)

Gee

"Happiness is a good health and a bad memory!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources