Create a job to execute proc in all databases.

Question

Post reply

Create a job to execute proc in all databases.

Ashwin M N

Hall of Fame

Points: 3668
More actions
March 11, 2008 at 6:40 am

#187370

I have a server which has around 40 databases. I have to execute a procedure(EXEC PROC Procname) in all the databases at a schedule time daily. I want to create a single job to execute this procedure since it is difficult to maintain individual jobs for every databases. How can this be done?

Viewing 15 posts - 1 through 15 (of 19 total)

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

Just out of curiosity, why? What is it you're doing that would require executing a proc in all user databases?

Anyways, do they have to run at the same exact time or can they run one after another?

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 2

Hi thanks for the reply they can run one after another.

But I want to do this in a single job.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

Than you'd need to do either a cursor or a loop ... if the proc name is the same in each database, than simply loop through the databases and call the proc with changing the context of the db name ... there are many a ways to do this, everyone has their preference, but this is probably what I would do ...:

DECLARE

@DBNameSYSNAME

,@ProcNameVARCHAR(16)

,@SQLVARCHAR(128)

,@DebugTINYINT

SET @Debug = 1 -- Change to 0 at run time

SET @ProcName = 'sp_who' -- Change this to your proc name

DECLARE procexec CURSOR FOR

SELECT [name] FROM master.dbo.sysdatabases

WHERE [dbid] > 4

ORDER BY [name]

OPEN procexec

FETCH NEXT FROM procexec INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL=

'

EXEC ' + @DBName + '.dbo.' + @ProcName + '

'

IF @Debug = 1

PRINT (@SQL)

ELSE

EXEC (@SQL)

FETCH NEXT FROM procexec INTO @DBName

END

CLOSE procexec

DEALLOCATE procexec

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 4

Does the stored procedure exists in each of the 40 databases.

You can try using sp_msforeachdb.

sp_msforeachdb " exec procname"

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 5

- So does the proc reside in every individual db ?

If not, make sure you've installed it in master, called it sp_*** and marked it as systemobject (database transparency)

e.g. exec sys.sp_MS_marksystemobject [sp_DBA_RowCount]

- Just for clarity I would create a jobstep per db. (logging per jobstep)

- If on the other hand, you only want one jobstep you could

use the undocumented :sick: sp_msforeachdb

e.g. EXEC sp_MSforeachdb @command1="print '?' DBCC CHECKDB ('?')"

Should I really add a disclamer with this last option :ermm:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

Yeah... I wanna know to...

Simple way (although it uses cursors in the background), is to Google sp_MSForEachDB.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 7

Yes the procedure name is same in all the database. But there are around 45 databases of which I have to execute the procedure only in 40 databases. The remaining databases does not have any relation with this procedure.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 8

Ashwin M N (3/11/2008)
Yes the procedure name is same in all the database. But there are around 45 databases of which I have to execute the procedure only in 40 databases. The remaining databases does not have any relation with this procedure.

Than in the cursor I gave you above, simply exclude what ever databases do not contain the procedure in the select statement.

Again, out of curiosity, what are you doing? Depending on the nature of this procedure, we could probably help you to change the logic of your procedure to not have it run from each and every database and/or provide some help in general on how to do a process like this.

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 9

I have a user table in every database. The user table contains many users with their user name and password to log in from application side. Whenever a user logins and logouts I have to update a different

table at the end of the day by using a lastlogindate column in users table. This update is critical for our application.

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 10

Well, since this is sql2005 right ?

As from sp2 you can use login triggers.

So if your application logges into the server connecting to the appropriate database, you might update that "last login" table immediatly in stead of running a proc every x time.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 11

Sorry folks... was in a hurry and didn't see the earlier suggestions when I posted about sp_MSForeEachDB.

I'd rather not use triggers if I can help it... by the same token, once setup, they always work for critical audits that MUST be there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Ashwin M N (3/11/2008)
I have a user table in every database. The user table contains many users with their user name and password to log in from application side. Whenever a user logins and logouts I have to update a different
table at the end of the day by using a lastlogindate column in users table. This update is critical for our application.

Actually you could probably do this with a 40-branch Union-ed view and then rewrite your local procedures into a single procedure that just runs against that view.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 13

Now there's an idea... and properly partitioned, it would be updateable, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Jeff Moden (3/12/2008)
Now there's an idea... and properly partitioned, it would be updateable, as well.

Thanks!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]