Updates 15 identical databases on Server

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)

Updates 15 identical databases on Server

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-08-01 : 13:17:57

I have 15 identical databases on the same server, how can i run a create or alter stored procedures.
in one execution.

currently i am running alters or create sps manually pointing to each database. which is taking up all my time.

Please help, i do have reporting server related database also installed on the same sql server along with some other applications related databases.

Is there a way to use these:
USE [DB1]
GO

USE [DB2]
GO

upto DB15 in one shot.

Thank you all for the helpful information.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 13:22:45

try using sp_msforeachdb system stored procedure

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-08-01 : 13:24:39

quote:
Originally posted by visakh16

try using sp_msforeachdb system stored procedure

I agree with visakh16... this little stored procedure (along with sp_foreachtable) are godsends.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-08-01 : 13:30:09

If i use that, it gets executed via master DB, but the problem is the stored procedures will be executed on all of the databases on that server.

My reportserver and other databases also will have those sp's, which don't belong to them.

is it possible i can specify the all 15 db names using sp_MSforEachDB.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 13:33:20

quote:
Originally posted by cplusplus

If i use that, it gets executed via master DB, but the problem is the stored procedures will be executed on all of the databases on that server.

My reportserver and other databases also will have those sp's, which don't belong to them.

is it possible i can specify the all 15 db names using sp_MSforEachDB.

Thank you.

you can just use this

exec sp_Msforeachdb 'select ''?'''

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-08-01 : 13:37:09

Please visakh, it seems like you know the trick.

How can i use DB1, DB2 using this: for SP Name: "USP_MyOrders"

exec sp_Msforeachdb 'select ''?'''

please provide me the exact syntax

Really appreceate your help.

Thanks.

quote:
Originally posted by visakh16

quote:
Originally posted by cplusplus

If i use that, it gets executed via master DB, but the problem is the stored procedures will be executed on all of the databases on that server.

My reportserver and other databases also will have those sp's, which don't belong to them.

is it possible i can specify the all 15 db names using sp_MSforEachDB.

Thank you.

you can just use this

exec sp_Msforeachdb 'select ''?'''

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 13:41:13

what are you trying to achieve?

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-08-01 : 13:43:52

How can i execute msforeachDB using DB1 with SP: USP_myOrders

I don't knwo where to place the DBname and sp name with the below command:, can you please provide the sysntax.

exec sp_Msforeachdb 'select ''?'''

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 13:49:51

quote:
Originally posted by cplusplus

How can i execute msforeachDB using DB1 with SP: USP_myOrders

I don't knwo where to place the DBname and sp name with the below command:, can you please provide the sysntax.

exec sp_Msforeachdb 'select ''?'''

just give

exec sp_Msforeachdb 'exec USP_myOrders'

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-08-01 : 13:59:23

If I understand him correctly, he wants to dynmically execute a "CREATE PROCEDURE" statement in each database. There is a way around this though like so:


DECLARE @command1 NVARCHAR(MAX);
DECLARE @script NVARCHAR(MAX);

SET @script = 'SELECT TOP 5 * FROM sys.tables;';
SET @command1 = 'EXECUTE ..sp_executesql @stmt=N''' + @script + ''';';

EXEC sp_MSforeachdb @command1=@command1

Just format the @script parameter with your create procedure script and presto. The trick here is to execute sp_executesql in the context of the desired database as highlighted in the above sample.

What is happening is the statement supplied in the @command1 parameter is always executed in the context of the master database, hence the problem identified by the OP. The simple answer would be to toss a USE statement in the script and move on. However you'll get an error stating the CREATE PROCEDURE statement must be the first command in a statement or batch. So, work around it by executing it in the context of the desired database by combining sp_msforeachdb and sp_executesql.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-08-01 : 14:13:12

I did this and getting error message:
DECLARE @command varchar(1000)

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''Northwind'', ''ReportServer'',"ReportServerTempDB") BEGIN USE ? EXEC(''USP_GetOrders'') END'

EXEC sp_MSforeachdb @command

I get the following error message: Could'nt find teh stored procedure

This above stored proc is available in Masters DB.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-08-01 : 14:20:31

@cplusplus...

Please clarify if you are attempting to execute an existing stored procedure or create/alter one.

As far as the error you are getting, you have a USE statement so the procedure is trying to execute the proc in a specific database - not master. It does not exist hence the error.

Now, if you want to execute a stored procedure the example you just posted is exactly what you need. However if you are attempting to create/alter one, read my post just prior to yours.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-08-01 : 14:22:27

A side note... why do you have user defined procedures in the master database? I personally thing that should be taboo :). But if you want to be able to execute a stored procedure in the master database, in the context of another database then the procedure in master must start with sp_, not usp_. This is a "special feature" built into SQL Server.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-08-01 : 14:27:29

TFOUNTAIN, really appreceate your help Thanks.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-08-01 : 14:28:22

Just so you have an example that looks closer to what you want:


DECLARE @command1 NVARCHAR(MAX);
DECLARE @script NVARCHAR(MAX);

SET @script = 'CREATE PROCEDURE dbo.usp_sel_testing AS SELECT TOP 5 DB_NAME(), * FROM sys.tables;';
SET @command1 = 'EXECUTE ..sp_executesql @stmt=N''' + @script + ''';';

EXEC sp_MSforeachdb @command1=@command1

Then test it with this:

EXEC sp_MSforeachdb @command1='EXEC .dbo.usp_sel_testing'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources