question

anil40 avatar image
anil40 asked

TQSL works in Query analyser errors SQL job

Following query

--Use Cursor to Loop through Databases DECLARE @DatabaseName AS VARCHAR(500) --Provide the name of SP that you want to run DECLARE @SPName AS VARCHAR(128) = 'IndexRebuild' DECLARE DBCursor CURSOR FOR --Filter the list of the database in which Stored Procedure exists SELECT NAME FROM sys.databases WHERE STATE <> 6 --SKIP OFFLINE AND database_id > 4 --SKIP SYTEMDB'S OPEN DBCursor FETCH NEXT FROM DBCursor INTO @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @DBName AS NVARCHAR(500); SET @DBName = QUOTENAME(N'' + @DatabaseName + ''); --USE Dynamic SQL To change the Database Name and --Execute Stored Procedure from that Database EXEC ( N'USE ' + @DBName + N'; EXEC(''' + @SPName + ' '');' ); FETCH NEXT FROM DBCursor INTO @DatabaseName END CLOSE DBCursor DEALLOCATE DBCursor

Works in SQL query analyser but fail when scheduled as a job with error SP not found.

Where I am missing the point.

sql agent
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
anthony.green avatar image
anthony.green answered

It may be down to "breaking spaces"

You settings compared to the agents settings for spaces may be different hence why it works one way and not the other.

When your running it, this actually wants to run "EXEC('IndexRebuild ')", notice the extra space at the end of the Rebuild, before the '

What happens if you remove the space in this piece of code between the ' and the '' + ' '')

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.