Create a job to execute proc in all databases.

Question

Post reply

Create a job to execute proc in all databases.

Viewing 5 posts - 16 through 19 (of 19 total)

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

Doug S. SSCrazy Points: 2801 More actions · Answer 1

You also have the option of adding a single sql agent job and separate job steps for each database.

you can also try this in a single job step:

use xxxxxx

go

exec myproc

go

use xxxxx

go

exec myproc

....

Doug

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

SQL ORACLE

One Orange Chip

Points: 27807

March 13, 2008 at 8:46 am

#789104

sp_msforeachdb "exec mydb..myProc"

Tom Garth SSCertifiable Points: 6193 More actions · Answer 3

If there is common data used by multiple databases of a single application, it is unfortunate that the application designers didn't think to use an Application Master database for housing and accessing that data.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Teshome-283915 Old Hand Points: 367 More actions · Answer 4

you can use something like

declare @dbname varchar(300)

declare @sqlstring varchar(500)

select @dbname = max(name) from master.dbo.sysdatabases

while @dbname is not null

begin

set @sqlstring = 'use '+ @dbname + ' update tableA set column1 = '

exec (@sqlstring)

select @dbname = max(name) from master.dbo.sysdatabases

where name < @dbname

end

Adam Haines SSC-Insane Points: 23217 More actions · Answer 5

ForEachDB would simply this process for you 10 fold. As mentioned earlier, it does uses cursors, but is so dang convientent.

Your statement should look like this:

EXEC master..sp_MSForeachdb '

USE [?]

IF DB_ID(''?'') > 4 --dont do system dbs

IF ''?'' <> ''DB41''

AND ''?'' <> ''DB42''

AND ''?'' <> ''DB43''

AND ''?'' <> ''DB44''

AND ''?'' <> ''DB45''

BEGIN

EXEC stored procedure

END

'

My blog: http://jahaines.blogspot.com