How to execute stored proc to multiple DBs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to execute stored proc to multiple DBs

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-07-07 : 14:14:31

I want to execute this stored proc against multiple databases on same server, i have close to 12 identical databases on the same box, each time i need to apply a change then i am executing the same query 12 times pointing to thdatabase.

Is it possible to execute the Sp in on shot affecting all of the 12 databases on the same server/box.

Please help:
**********************example***********
USE [Database1]
GO

ALTER PROCEDURE [dbo].[USP_NewTaskCode]
(@FieldLabelKey nvarchar(50),
@Text nvarchar(10),
@Description nvarchar(50),
@UserName nvarchar(50))
AS
DECLARE @ErrorCode int
DECLARE @TransactionCountOnEntry int

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT @ErrorCode = @@Error, @TransactionCountOnEntry = @@TranCount
IF @ErrorCode = 0
BEGIN
BEGIN TRANSACTION
INSERT INTO TAB_TaskCodes
(FieldLabelKey,
Text,
Description,
UpdatedBy)
VALUES
(@FieldLabelKey,
@Text,
@Description,
@UserName)
SELECT @ErrorCode = @@Error
IF @ErrorCode <> 0
BEGIN
ROLLBACK TRANSACTION
SET NOCOUNT OFF
RETURN @ErrorCode
END
END

-- check if a transaction actually happened
IF @@TranCount > @TransactionCountOnEntry
COMMIT TRANSACTION
SET NOCOUNT OFF
RETURN @ErrorCode
*******************************************

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-07 : 14:21:53

sp_MSforEachDB 'exec yourSproc'
however your sproc must be in master

or
sp_MSforEachDB 'exec ?.dbo.yourSproc'
if you have the same sproc in all db's

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-07 : 14:24:02

Or use the SSMSToolsPack built-in tool for this.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-07 : 14:32:05

even with that you'll need to have the sporc either in master or in every db.
but yes, you can use that too

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-07-07 : 15:21:23

Not all the databases on that server, only have 12 db's which need same stored procs.

rest all databases belonging to other applications for example: reporting services has its database maintained on the sql server box.

Can i use this one: sp_MSforEachDB 'exec yourSproc'

Thanks.

quote:
Originally posted by spirit1

sp_MSforEachDB 'exec yourSproc'
however your sproc must be in master

or
sp_MSforEachDB 'exec ?.dbo.yourSproc'
if you have the same sproc in all db's

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-07 : 15:26:48

no you can't.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources