November 20, 2012 at 4:32 am
Have you made one step for each database that the SP needs to run over?
If so:
On the job step properties, click advanced. You will then see "On success action" and make sure "go to the next step" is chosen. (You have to do this for each and every step untill the last one)
So, when Step1 completes, Step2 will run, when it completes Step3 will run and so on.
If not and your using just 1 job step to do it in a "batch" like so:
EXEC DB1.dbo.MySP
EXEC DB2.dbo.MySP
EXEC DB3.dbo.MySP
EXEC DB4.dbo.MySP
EXEC DB5.dbo.MySP
EXEC DB6.dbo.MySP
, then as John said your going to need to do some error handling and such.
Do you need to ensure that the previous step is successful before the next one runs, or can the next step run even the first one fails?
November 20, 2012 at 4:37 am
Another way would be to use the following code
DECLARE @sql NVARCHAR(MAX)
SELECT @sql =
REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'EXECUTE schema.procedure;'+CHAR(13)+CHAR(10)
FROM sys.databases WHERE name NOT IN ('master','msdb','model','tempdb')
FOR XML PATH('')
) AS NVARCHAR(MAX)
),
'& # x 0 D ;',CHAR(13) + CHAR(10)
)
--SELECT @sql
EXECUTE sp_executesql @sql
just remove the spaces between '& # x 0 D ;'
November 20, 2012 at 4:49 am
thanks Shell.. will try to implement it
Anthony - Do u want me to paste the entire code in Command pane ending with Exec SP???
example -
DECLARE @sql NVARCHAR(MAX)
SELECT @sql =
REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'EXECUTE schema.procedure;'+CHAR(13)+CHAR(10)
FROM sys.databases WHERE name NOT IN ('master','msdb','model','tempdb')
FOR XML PATH('')
) AS NVARCHAR(MAX)
),
'& # x 0 D ;',CHAR(13) + CHAR(10)
)
--SELECT @sql
Exec pr_Index @sql
is this correct???
************************************
Every Dog has a Tail !!!!! :-D
November 20, 2012 at 4:53 am
Use my code, the only thing you need to do is to replace the the text schema.procedure with the name of your procedure eg. dbo.thisismyprocedure
Then remove the spaces between the '& # x 0 D ;'
The last step should be exec sp_executesql @sql do not change that line.
November 20, 2012 at 4:56 am
I got it, but one question while copying this is command pane.. there we have to select the database also?? by defauklt it is Master, what should i choose???
************************************
Every Dog has a Tail !!!!! :-D
November 20, 2012 at 4:59 am
Any it does not matter as it will execute the procedure in every database you have in sys.databases unless you specifically exlcude it in the exclusions list.
Uncomment the SELECT @sql line, comment out the EXEC sp_executesql line and run it, you will see what the command is doing.
It basically generates
USE [database1];
EXECUTE dbo.thisismyprocedure;
USE [datebase2];
EXECUTE dbo.thisismyprocedure;
etc
etc
etc
November 21, 2012 at 4:13 am
Thanks Sure 🙂
************************************
Every Dog has a Tail !!!!! :-D
November 21, 2012 at 5:04 am
Hi Anthony,
I have modified the Script, i need one more help in the script
What i have done is if the Script fails for any DB it will entry the record in One table.. so that i will come to know which DB was failed to execute the Script.
*************************************
DECLARE @sql NVARCHAR(MAX)
SELECT @sql =
REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'Begin Try
EXECUTE dbo.Test_SP;
End Try
Begin Catch
Use Master
Insert into Runal_Job (DBName,FailureType) values('''+name+''',''Log'')
End Catch
'+CHAR(13)+CHAR(10)
FROM sys.databases WHERE name IN ('DB1','DB2')
FOR XML PATH('')
) AS NVARCHAR(MAX)
),
' ',CHAR(13) + CHAR(10)
)
--SELECT @sql
--print @sql
EXECUTE sp_executesql @sql
--Use Master
--select * from Runal_Job
*************************************
I want to add the time constraint in this..
as some time it takes hours to execute the script for one DB & due to which it may affect the performance..
Can you please help me with the Time query..ao that if the script runs for more than half hour it should automatically kill the execution for that DB..
Secondly one more question
If there are 3 DB's & this script will run for first & then for second & then for third..
What if it fails for 2nd DB, will it leave the 2nd DB & run 1 & 3?????
************************************
Every Dog has a Tail !!!!! :-D
November 27, 2012 at 11:23 pm
You can use below script by just enter the database name which you want to exclude and provide the spname as input variable.
DECLARE @SPNAME VARCHAR(100)
DECLARE @DBNAME VARCHAR(100)
DECLARE @SQLCMD VARCHAR(1000)
SET @SPNAME = 'MY_SP'
DECLARE CUR1 CURSOR FOR
SELECT NAME FROM SYSDATABASES WHERE NAME NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @DBNAME
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQLCMD = 'EXEC '+@DBNAME+'.DBO.'+@SPNAME+''
--PRINT(@SQLCMD)
EXEC(@SQLCMD)
FETCH NEXT FROM CUR1 INTO @DBNAME
END
CLOSE CUR1
DEALLOCATE CUR1
GO
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply