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.