custom system proc

Hello, I would like to create a stored procedure, which would manipulate dat
a
in different databases, where all my databases have the same structure.
I would expect , when this proc is called in DB1 to use tables from DB1,
when called in DB2 .. tables from DB2 .. and so on.
However, when I created my sample stored proc in master..
create proc sp_sample as select * from table1
and then I tried to call this proc in DB1.. even that DB1 had also table1 ,
stored procedure was selecting data from master database. I was expecting it
to use DB1..table1.
I hope I am clear on what I am going to achieve, I would like to have one
copy for each of my stored procedures, stored in master database instead of
10' copies stored across all different databases.
I would appreciate any tips on how this could be done.
LucjanThere's no supported or documented way to achieve what you want to do. Sugge
sted method is to have
the same proc in all databases and use some sw to manage versions. You *can*
achieve what you want
by marking the proc as a system proc using sp_MS_marksystemobject (Google fo
r usage), but again, it
is not supported or documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lucjan" <Lucjan@.discussions.microsoft.com> wrote in message
news:BB44926A-3624-4F56-B47C-299338E2F222@.microsoft.com...
> Hello, I would like to create a stored procedure, which would manipulate d
ata
> in different databases, where all my databases have the same structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also table1
,
> stored procedure was selecting data from master database. I was expecting
it
> to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have one
> copy for each of my stored procedures, stored in master database instead
of
> 10' copies stored across all different databases.
> I would appreciate any tips on how this could be done.
> Lucjan
>|||The supported behavior of special procedures (sp_ in master) is to resolve
only system tables in the current database, but user tables in master.
There's an undocumented "feature" that causes the proc to resolve user
tables in the current database as well. To achieve this, you run:
EXEC dbo.sp_MS_marksystemobject 'dbo.sp_procname'
Tough I'd be careful from relying on such undocumented behavior in
production systems. You never know when support for such a "feature" will be
dropped.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Lucjan" <Lucjan@.discussions.microsoft.com> wrote in message
news:BB44926A-3624-4F56-B47C-299338E2F222@.microsoft.com...
> Hello, I would like to create a stored procedure, which would manipulate
> data
> in different databases, where all my databases have the same structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also table1
> ,
> stored procedure was selecting data from master database. I was expecting
> it
> to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have one
> copy for each of my stored procedures, stored in master database instead
> of
> 10' copies stored across all different databases.
> I would appreciate any tips on how this could be done.
> Lucjan
>|||Lucjan (Lucjan@.discussions.microsoft.com) writes:
> Hello, I would like to create a stored procedure, which would manipulate
> data in different databases, where all my databases have the same
> structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also
> table1 , stored procedure was selecting data from master database. I was
> expecting it to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have
> one copy for each of my stored procedures, stored in master database
> instead of 10' copies stored across all different databases.
You can do this in SQL 2000, but it is not supported. And I don't think
you can do it at all in SQL 2005, since there is a radical change how
stored procedures, system tables etc are stored in SQL 2005.
Keep your code under version control and write a script that can update
one or more databases. The script can be Perl, VB, VBscript or even a
BAT file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp