run stored procedure in specific database

[bobbo]

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?

[rmiao]

Did you mark the sp as system sp?

[bobbo]

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

[Owais]

Did you mark the sp as system sp?

How do you mark sp as system sp?

[Owais]

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_.