InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 09:16:55 GMT20http://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxYou 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 FORSELECT NAME FROM SYSDATABASES WHERE NAME NOT IN ('MASTER','MODEL','MSDB','TEMPDB')OPEN CUR1FETCH NEXT FROM CUR1 INTO @DBNAMEWHILE @@FETCH_STATUS=0BEGINSET @SQLCMD = 'EXEC '+@DBNAME+'.DBO.'+@SPNAME+''--PRINT(@SQLCMD)EXEC(@SQLCMD)FETCH NEXT FROM CUR1 INTO @DBNAMEENDCLOSE CUR1DEALLOCATE CUR1GOTue, 27 Nov 2012 23:23:55 GMTkvishu83http://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxHi Anthony,I have modified the Script, i need one more help in the scriptWhat 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 @SQLEXECUTE 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?????Wed, 21 Nov 2012 05:04:35 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxThanks Sure :-)Wed, 21 Nov 2012 04:13:33 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxAny 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 generatesUSE [database1];EXECUTE dbo.thisismyprocedure;USE [datebase2];EXECUTE dbo.thisismyprocedure;etcetcetcTue, 20 Nov 2012 04:59:38 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxI 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???Tue, 20 Nov 2012 04:56:50 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxUse my code, the only thing you need to do is to replace the the text [b]schema.procedure[/b] with the name of your procedure eg. [b]dbo.thisismyprocedure[/b]Then remove the spaces between the '& # x 0 D ;'The last step should be exec sp_executesql @sql do not change that line.Tue, 20 Nov 2012 04:53:24 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxthanks Shell.. will try to implement itAnthony - 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 @SQLExec pr_Index @SQLis this correct???Tue, 20 Nov 2012 04:49:34 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxAnother way would be to use the following code[code="sql"]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 @SQLEXECUTE sp_executesql @SQL[/code]just remove the spaces between '& # x 0 D ;'Tue, 20 Nov 2012 04:37:48 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxHave 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:[code="other"]EXEC DB1.dbo.MySP EXEC DB2.dbo.MySP EXEC DB3.dbo.MySP EXEC DB4.dbo.MySP EXEC DB5.dbo.MySP EXEC DB6.dbo.MySP [/code], 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?Tue, 20 Nov 2012 04:32:35 GMTshellbushttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxHi Shell,Acccording to John, i have followed the below step(1) Modify the code I posted so that it fits your environment(2) Create a new job(3) Copy and paste the code into the "Command" field(4) Schedule the job to run with the desired frequency and at the desired timesNow how will i confirm myself that the 2nd sp gets executed only after 1st one finishes and so on??U have any ideaTue, 20 Nov 2012 04:17:51 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxCould create a maintenance plan for the steps and just add them in one at a time with a success constraint. Pretty much the same thing as doing it in a job, however there's the handy little success constraint that you can add to ensure the next step doesn't execute until the previous one is successful.Tue, 20 Nov 2012 04:02:15 GMTshellbushttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspx20 seperate steps is okas of now...But in another Server there are almost more than 100 Db's :w00t:Anyways will test it step by step, incase i have any issues, will write here.. thanks John :-)Tue, 20 Nov 2012 03:40:23 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxThat's right, yes. You might want to test what happens if one command fails - does it abort the whole batch or does it move on to the next command. If it moves on and that isn't what you want, then you'll either need to include some error handling, or split your job into 20 separate steps.JohnTue, 20 Nov 2012 03:31:14 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxCool John, Thanks Man ;-)I will do it today itself & will let you know tomorrow..Just one question..IF i set the SP pne after other then..The sequence of execution will be one after other???I mean 1st sp excutes & then after it finishes themn only second Sp will execute???Tue, 20 Nov 2012 03:27:04 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspx(1) Modify the code I posted so that it fits your environment(2) Create a new job(3) Copy and paste the code into the "Command" field(4) Schedule the job to run with the desired frequency and at the desired timesClearly you're struggling with something, but remember I'm not standing behind you and I can't see your screen, so if there's anything you don't understand, please be very specific about what it is.JohnTue, 20 Nov 2012 03:21:22 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxYes the procedure is same for all database, but John can u let me know the steps by step about setting the jobs?Tue, 20 Nov 2012 03:16:13 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxNot only can you do it one single job, you can do it in one single job step, in the way I suggested above.JohnTue, 20 Nov 2012 03:00:40 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxDoes the same procedure exist in all 20 databases and does it need any special paramateres to run?Tue, 20 Nov 2012 02:59:46 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxhow do i set the JOBS one after one..I mean...1st the SP for one DB will be executed & only after that the SP should get executed of rthe second DB & so on....Is this possible to perform the above activity in One Single Job???Tue, 20 Nov 2012 02:58:55 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxI didn't go anywhere. What is your question?JohnTue, 20 Nov 2012 02:56:41 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxjohn, where did u go:w00t:Tue, 20 Nov 2012 02:50:05 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxYes John, same to same :-)Mon, 19 Nov 2012 04:51:15 GMTOnlyOneRJhttp://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxSomething like this?[code="sql"]EXEC DB1.dbo.MySP EXEC DB2.dbo.MySP EXEC DB3.dbo.MySP EXEC DB4.dbo.MySP EXEC DB5.dbo.MySP EXEC DB6.dbo.MySP EXEC DB7.dbo.MySP EXEC DB8.dbo.MySP EXEC DB9.dbo.MySP EXEC DB10.dbo.MySPEXEC DB11.dbo.MySP EXEC DB12.dbo.MySP EXEC DB13.dbo.MySP EXEC DB14.dbo.MySP EXEC DB15.dbo.MySP EXEC DB16.dbo.MySP EXEC DB17.dbo.MySP EXEC DB18.dbo.MySP EXEC DB19.dbo.MySP EXEC DB20.dbo.MySP [/code]JohnMon, 19 Nov 2012 04:47:58 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1386299-1549-1.aspxSQL JOBS Not Functioning :w00t:I have Stored procedure & that stored procedure needs to be executed on 20 Databases daily,Currently i am doing it manually..I tried setting the Jobs on SQL but it only executes the SP for One database & not for all DB's.... :w00t:how do i set the JOBS one after one..I mean...1st the SP for one DB will be executed & only after that the SP should get executed of rthe second DB & so on....:crazy: somehow i am not getting the exact steps or i may be missing something.. please help meMon, 19 Nov 2012 04:39:29 GMTOnlyOneRJ