General information
Forum:
Microsoft SQL Server
The only think I can think of, other than what mike said about spcifying the database prior to each command is to change to the database in an if statement. So, if you know all of the database names then you can do something like:
if @dbname = 'DATA1' use DATA1
if @dbname = 'DATA2' use DATA2
The other thing is to have a copy of your stored procedure in each database, and run the appropriate sp...
exec ('exec '+@dbname+'..dothistask)
And the do this task of the database will do a use for its database.
Actually, I wonder if you could do something like, have two stored procedures... one that just calls the second to do the work....
@sql = 'USE '+@dbname+CHAR(13)+'EXEC sp_secondsp'
exec (@sql)
BOb
>Mike,
>
>geez - seems like an awful basic thing not to be there. I suppose I'll just put all my "stuff" in a separate proc and execute it from first proc. I know I can do it that way.
>
>Thx
>Ken
>
>>To the best of my knowledge, you can't do it. Now you can reference the database within the query:
>>
>>SELECT * FROM pubs.dbo.authors
>>
>>Maybe you can get this to work
>>
>>-Mike
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only