? Convert a Variable''s Output To a Text String

Question

Post reply

? Convert a Variable''s Output To a Text String

Keith Saynor

SSC Veteran

Points: 278
More actions
February 7, 2006 at 5:11 am

#93256

I'm wanting to convert Greg's Record Count script to place the output into a fixed table and run the script weekly to monitor record growth. The procedure below creates the SQL -
INSERT INTO DBMON..Records (RunDate, DB, RecCount, TBName) Select(GetDate(), Ciphr, rows, b.name) FROM Ciphr..sysindexes a , Ciphr..sysobjects b WHERE a.id = b.id and type='u' AND indid < 2 order by b.name
The 1st Ciphr in the select statement needs to be in quotes ' '. If I put the additional quotes in the select statement it returns '+ @DB +'
Thanks in advance for any help
CREATE PROCEDURE KS_DB_Count1 AS
-- Originally Written By: Greg Larsen
-- Date: 2/28/2002
declare @CMD char(2000)
declare @DB varchar(14)
set nocount on
declare db cursor for
select name from master..sysdatabases where name = 'Ciphr%'
open db
fetch next from db into @db
WHILE @@FETCH_STATUS = 0
BEGIN
--print 'Record Counts for database ' + @DB
set @CMD = 'INSERT INTO DBMON..Records (RunDate, DB, RecCount, TBName) Select(GetDate(), ' + @DB +', rows, b.name) FROM ' + @DB + '..sysindexes a , ' + @DB + '..sysobjects b ' +
' WHERE a.id = b.id and type=''u'' AND indid < 2 order by b.name'
Print @CMD
exec (@CMD)
fetch next from db into @db
end
close db
deallocate db
GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Liliana Sorrentino SSC-Addicted Points: 400 More actions · Answer 1

Hello,

Try:

set @CMD = 'INSERT INTO DBMON..Records (RunDate, DB, RecCount, TBName) Select(GetDate(), ''' + @DB +''', rows, b.name) FROM ' + @DB + '..sysindexes a , ' + @DB + '..sysobjects b ' +

' WHERE a.id = b.id and type=''u'' AND indid < 2 order by b.name'

Liliana.

Keith Saynor SSC Veteran Points: 278 More actions · Answer 2

Thanks Liliana

That gives me what I thought I needed but when I run it in QA I get an error - see below

INSERT INTO DBMON..Records (RunDate, DB, RecCount, TBName) Select(GetDate(), 'Ciphr', rows, b.name) FROM Ciphr..sysindexes a , Ciphr..sysobjects b WHERE a.id = b.id and type='u' AND indid < 2 order by b.name

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near ','.

Liliana Sorrentino SSC-Addicted Points: 400 More actions · Answer 3

Ok, then:

set @CMD = 'INSERT INTO DBMON..Records (RunDate, DB, RecCount, TBName) Select GetDate(), ''' + @DB +''', rows, b.name FROM ' + @DB + '..sysindexes a , ' + @DB + '..sysobjects b ' +

' WHERE a.id = b.id and type=''u'' AND indid < 2 order by b.name'

without ( ) in SELECT.

Keith Saynor SSC Veteran Points: 278 More actions · Answer 4

Sorted !

I'd still got brackets around the fields in the select statement

Liliana - I was just typing the above response when I received your latest post.

Thanks for your help - it works a treat.