Using Stored Procedures on Multiple Databases

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 2000 Forums

SQL Server Development (2000)

Using Stored Procedures on Multiple Databases

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2001-05-24 : 15:41:28

I have a group of stored procedures in DB1
These stored procedures are controlled by a master procedure so:
SP1 calls SP2 and SP3

I ALSO HAVE DB2 ... DB100

the stored procedures where written without fully qualified statements
so in any one stored procedure the statements
are as follows :
select id from tbl1

not

select id from db1.dbo.tbl1

I'm wondering If there is a way to use the use command to change databases or a way of executing the stored procedures from DB1 so that they affect tables in the databases they run on.

I've tried

SET @dbname = 'db2'

EXEC 'USE ' + @dbname + '
'EXEC db1.dbo.sp1'

but the stored procedure still affects db1 and not db2.

It would be a pain to go through and fully qualify every sql statement so a simpler solution would be nice.

Edited by - ValterBorges on 05/24/2001 15:49:05

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources