Error in dynamic sql script (SOS)

Question

Post reply

Error in dynamic sql script (SOS)

Max Wanningen

SSC Journeyman

Points: 98
More actions
September 3, 2007 at 1:43 am

#118101

Hi all,
Could use some help on a statement that would cycle trough all databases, and all tables within a database. Executing a sp_spaceused on each table (want to process that data later).
Hereby the statement & the issue : (yes, i should use
sp_Msforeachtable, but the fact that this isn't working is annoying me)
Issue:
Right now (at the end of the script, outer loop, marked with Red) i use the print statement, instead of the "execute sp_execute @StringForExcecute" (marked with green). The result is a serie of strings which give expected output when copied to a second statement and run manually with an sp_executesql. When the "execute sp_execute @StringForExcecute" is used instead (now commented out, green) it produces an error.
What's going wrong?

Statement:
-------------
DECLARE @Database_from_cursor nvarchar(150)
DECLARE @SQLString nvarchar(2000)
DECLARE @StringForExcecute nvarchar(2000)
USE master
DECLARE databases_cursor CURSOR FOR
SELECT name FROM sysdatabases
OPEN databases_cursor
FETCH NEXT FROM databases_cursor INTO @Database_from_cursor WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = N'Use '+ @Database_from_cursor+
'
DECLARE @table_from_cursor nvarchar(150)
DECLARE @SQLString nvarchar(2000)
DECLARE @StringForExcecute nvarchar(2000)
DECLARE table_cursor CURSOR FOR
SELECT name
FROM sysobjects
WHERE (xtype = '+'''+''''''U''''''+'''+')
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_from_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = N'''''''+'+'+'''EXEC '+ 'sp_spaceused ''''''+'+'''+@table_from_cursor
SET @StringForExcecute = ''''+@SQLString+''''
--Print @StringForExcecute
EXECUTE sp_executesql @SQLString
FETCH NEXT FROM table_cursor into @table_from_cursor
END
CLOSE table_cursor
DEALLOCATE table_cursor
'
SET @StringForExcecute = ''''+@SQLString+''''
Print @StringForExcecute
--EXECUTE sp_executesql @StringForExcecute
FETCH NEXT FROM databases_cursor into @Database_from_cursor END CLOSE databases_cursor DEALLOCATE databases_cursor
---------------

Viewing 7 posts - 1 through 6 (of 6 total)

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

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 1

maybe

SET @StringForExcecute = ''''+@SQLString+''''

should be

SET @StringForExcecute = @SQLString

Max Wanningen SSC Journeyman Points: 98 More actions · Answer 2

That way the output is no longer a string so execution will result in an error. I included the '''' so the output of the inner loop is like 'string'.

Regards,

Marcel

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 3

The below 2 lines are both strings.

This sentence has no single quotes.

'This sentence are in quotes'.

Try it anyway. You will find the next problem in your script.

Max Wanningen SSC Journeyman Points: 98 More actions · Answer 4

Hi Koji,

Thanks for thinking along. I tried your suggestion before i posted the reply, but it throw an error.

If the statement is 'printed' instead of executed, the output (blue) can be processed manually (see below) and it will work without problems. When executed by the outer loop it produces an error (driving me nuts:crazy.

Because the output (blue) below can be processed i believe the '''' is needed.

Suggestions are appreciated!

declare @WOW NVARCHAR(2000)

set @wow =

'Use XXXX

DECLARE @table_from_cursor nvarchar(150)

DECLARE @SQLString nvarchar(2000)

DECLARE @StringForExcecute nvarchar(2000)

DECLARE table_cursor CURSOR FOR

SELECT name

FROM sysobjects

WHERE (xtype = '+'''U'''+')

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @table_from_cursor

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString = N'''+'EXEC sp_spaceused '''+'+@table_from_cursor

SET @StringForExcecute = ''+@SQLString+''

--Print @StringForExcecute

EXECUTE sp_executesql @SQLString

FETCH NEXT FROM table_cursor into @table_from_cursor

END

CLOSE table_cursor

DEALLOCATE table_cursor

'

--PRINT @WOW

EXECUTE SP_EXECUTESQL @WOW

Regards,

Marcel

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 5

declare @WOW NVARCHAR(2000)

set @wow =

'Use XXXX

DECLARE @table_from_cursor nvarchar(150)

DECLARE @SQLString nvarchar(2000)

DECLARE @StringForExcecute nvarchar(2000)

DECLARE table_cursor CURSOR FOR

SELECT name

FROM sysobjects

WHERE (xtype = '+'''U'''+')

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @table_from_cursor

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString = N'''+'EXEC sp_spaceused '''+'+@table_from_cursor

SET @StringForExcecute = ''+@SQLString+''

--Print @StringForExcecute

EXECUTE sp_executesql @SQLString

FETCH NEXT FROM table_cursor into @table_from_cursor

END

CLOSE table_cursor

DEALLOCATE table_cursor

'

PRINT @WOW

--EXECUTE SP_EXECUTESQL @WOW

will print

Use XXXX

DECLARE @table_from_cursor nvarchar(150)

DECLARE @SQLString nvarchar(2000)

DECLARE @StringForExcecute nvarchar(2000)

DECLARE table_cursor CURSOR FOR

SELECT name

FROM sysobjects

WHERE (xtype = 'U')

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @table_from_cursor

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString = N'EXEC sp_spaceused '+@table_from_cursor

SET @StringForExcecute = '+@SQLString+'

--Print @StringForExcecute

EXECUTE sp_executesql @SQLString

FETCH NEXT FROM table_cursor into @table_from_cursor

END

CLOSE table_cursor

DEALLOCATE table_cursor

Your original post will print something like

'Use XXXXX

DECLARE @table_from_cursor nvarchar(150)

DECLARE @SQLString nvarchar(2000)

DECLARE @StringForExcecute nvarchar(2000)

DECLARE table_cursor CURSOR FOR

SELECT name

FROM sysobjects

WHERE (xtype = '+'''U'''+')

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @table_from_cursor

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString = N'''+'EXEC sp_spaceused '''+'+@table_from_cursor

SET @StringForExcecute = ''+@SQLString+''

--Print @StringForExcecute

EXECUTE sp_executesql @SQLString

FETCH NEXT FROM table_cursor into @table_from_cursor

END

CLOSE table_cursor

DEALLOCATE table_cursor

'

Do you see the quotes?

David McFarland SSChampion Points: 11815 More actions · Answer 6

Max, I think you're a bit too fond of the ol' single quotes.

Try this and see how if it works for you (be advised, your code will break badly if you have tables with an owner other than dbo, and this applies to schemas in 2005 as well):

DECLARE

@Database_from_cursor nvarchar(150)

DECLARE

@SQLString nvarchar(2000)

DECLARE

@StringForExcecute nvarchar(2000)

USE

master

DECLARE

databases_cursor CURSOR FOR

SELECT

name FROM sysdatabases

OPEN

databases_cursor

FETCH

NEXT FROM databases_cursor INTO @Database_from_cursor WHILE @@FETCH_STATUS = 0

BEGIN

SET

@SQLString = N'Use '+ @Database_from_cursor+

'

DECLARE @table_from_cursor nvarchar(150)

DECLARE @SQLString nvarchar(2000)

DECLARE @StringForExcecute nvarchar(2000)

DECLARE table_cursor CURSOR FOR

SELECT name

FROM sysobjects

WHERE (xtype = ''U'')

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @table_from_cursor

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString = N''EXEC sp_spaceused '' + @table_from_cursor

EXECUTE sp_executesql @SQLString

FETCH NEXT FROM table_cursor into @table_from_cursor

END

CLOSE table_cursor

DEALLOCATE table_cursor

'

SET

@StringForExcecute = @SQLString

--Print @StringForExcecute

EXECUTE

sp_executesql @StringForExcecute

FETCH

NEXT FROM databases_cursor into @Database_from_cursor END CLOSE databases_cursor DEALLOCATE databases_cursor