March 28, 2003 at 12:11 pm
i have multiple dbs out there that i want to run a sp against. its prefered that this procedure that i create stays in a separate db than the dbs i will be calling. thats my problem.
i have db a where the sp resides
i have have dbs: b,c,d where exact schemas are found but different testing load data.
what i would like to do is have the procedure in a use a parameter like: sp b or sp c which will call and run against whatever db i wanted.
I figured being a novice of course that where all the FROM table statements in the sp i could put in a variable of @db_name for the procedure than change the FROM tables to look like @db_name..table1. Doesn't like it though. Any suggestions?
March 28, 2003 at 12:31 pm
You could create an sp wich name starts with 'sp_', (just like bultin stored procedures)
in the master. This will let you call the sp from any db, like almost sp in SQL.
The Select inside the procedure will run in the context of the DB that you are running it.
March 28, 2003 at 12:56 pm
Also you must use the sp_MS_marksystemobject, else the proc will not run.
Tim C.
//Will write code for food
Tim C //Will code for food
March 28, 2003 at 2:21 pm
Are you sure? I don't use it, and all my sp works just fine.
March 28, 2003 at 2:46 pm
You create a stored proc in master with sp_ and call it from a different db and it works fine? Really? What version of Sql Server are you using?
Tim C.
//Will write code for food
Tim C //Will code for food
March 28, 2003 at 3:01 pm
Well, I tested a proc out without marking it, and it worked fine, yet in the past I have had to mark them. Weirdness. Does anyone know the answer to this conundrum? Here was my test :
USE MASTER
GO
CREATE PROC dbo.sp_FooManchu
AS
SET NOCOUNT ON
SELECT TOP 2 [name], GETDATE() as dt FROM sysobjects
RETURN 1
GO
USE PUBS
GO
EXEC sp_FooManchu --this runs in the pubs db but only exists in master
GO
USE MASTER
GO
DROP PROC sp_FooManchu
GO
Tim C.
//Will write code for food
Tim C //Will code for food
March 29, 2003 at 2:23 am
I've never seen that Tim. I also only use the sp_-prefix and it works fine. sp_MSmarksystemobject just marks the object as a system object. That means is you have to have the option for changing system objects set to later alter it.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply