-
run stored procedure in specific database
I have created a stored procedure in the master database that can be run against any database on the server.
I call this procedure from a sql server agent job. In this job I specify the database I want the procedure to run against but it always seems to run against the master database (presumably because the procedure is in this database). I can't switch database inside the procedure so what can I do other than creating a copy of the procedure in every database?
-
Did you mark the sp as system sp?
-
I didn't know about this feature but I do now and it sorted my problem. Thanks.
p.s. If anyone else is using this, when you call the sp don't include master.dbo. inthe EXEC call
This is how it's done:
USE master
-- This turns the SS2k's system marking on
EXEC master.dbo.sp_MS_upd_sysobj_category 1
go
CREATE PROC sp_xyz
AS
SELECT *
FROM mytable
-- This turns the SS2k's system marking off
EXEC master.dbo.sp_MS_upd_sysobj_category 2
GO
EXEC sp_xyz
go
USE northwind
EXEC sp_xyz
go
-
Originally Posted by rmiao
Did you mark the sp as system sp?
How do you mark sp as system sp?
-
Thanks rmiao. I got it working.
Here is how it can be done. Create system stored procedure in the master database and give it a name that starts with the three characters sp_.
Last edited by Owais; 08-07-2007 at 11:41 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|