January 11, 2008 at 2:15 am
I have number of Sql Server Databases , all having same objects(tables , triggers , functions)
but i want to store stored-procedure only at one location and by logging to that Server you can fire required procedure on all Sql-Server .
What i would like to do is have just one stored procedure, but pass in a parameter that defines the database. So each of databases calls the same stored procedure, passing the database name as parameter. When the stored procedure runs, it knows which database to look at.
Does anyone have any ideas how to do this? Dynamic SQL is not an option as it is too inefficient.
what i tried is : i am passing procedure text which i received from syscomments by by passed name 'DOC_DEL_BLL_DETAILS' replacing parameter name by its parameter value passed
declare @t nvarchar(2000)
declare @t1 nvarchar(2000)
--select @t = text
-- FROM
-- syscomments
-- WHERE
-- object_id('DOC_DEL_BLL_DETAILS') = syscomments.id
-- i get following string :
set @t = N ' CREATE PROCEDURE [dbo].[DOC_DEL_BLL_DETAILS]
@IN_BLL_ID INT
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM dbo.DOC_BLL_LIST
WHERE
BLL_ID = @IN_BLL_ID
END'
print @t
select @t1 = replace(@t,N'@IN_BLL_ID',N'12') -- replacing all parameters with its value.
select @t = replace(@t1,N'AS',N'^')
print @t
select @t1 = substring(@t, charindex('^',@t)+1,len(@t))
print @t1
-- finally i have string :
set @t1 = N'BEGIN
SET NOCOUNT ON;
DELETE FROM dbo.DOC_BLL_LIST
WHERE
BLL_ID = 12
END'
-- Now i can execute it any where.
execute(@t1)
please pls pls reply if you get some idea.
Thank you for spending you precious time and read my problem.
Gaurav Teli
Software Engineer
January 11, 2008 at 2:28 am
Hi,
my first comment would be is to avoid the old system tables/views on 2005. Instead of syscomments use sys.sql_modules.
Another "trick" that is worth considering, is to prefix the stored procedures you would like to become "global" with sp_ and create them in the master database. In this case they will be available in all of your databases on that server.
Regards,
Andras
January 11, 2008 at 6:30 am
Hello Andras Belokosztolszki ,
Thank you For your reply
I tried your 'trick'
it is working
but i need to call sp_temp that i created as 'global' in master Database , it is not fired on my current database objects (tables)
i.e.
i have two Database test have table 'Address'
and test1 have same table 'Address' with different data.
now i want to call procedure sp_temp on test DataBase 'Address' table as well as test1 database table.
but problem is it is searching Address table in side master Database.
thank you
Gaurav Teli
Software Engg.
January 11, 2008 at 6:43 am
teligaurav (1/11/2008)
Hello Andras Belokosztolszki ,Thank you For your reply
I tried your 'trick'
it is working
but i need to call sp_temp that i created as 'global' in master Database , it is not fired on my current database objects (tables)
.....
I've forgotten to mention step two 🙂
Mark these as system stored procedures:
exec dbo.sp_MS_marksystemobject 'sp_foo'
And here are the warnings: you need to back up your master database when you make changes to the stored procedures! Make sure that these stored procedures have excellent error handling, maybe even check if the one who is executing it is DBO or admin, otherwise throw an error. When upgrading SQL Server, remove these stored procedures, and do the upgrade only after that. Use sensible naming, e.g. sp_MyCompany_ ...
If you make it a system procedure, it will execute in the context of the current database, so the tables will be looked up in your current database.
Regards,
Andras
January 11, 2008 at 6:49 am
In addition to my previous warnings, here are some more 🙂 Do make sure that you are happy with modifications to the master database. Note also, that dbo.sp_MS_marksystemobject is not a documented stored procedure. Consider the alternatives:
* There are tools on the market that will allow you to run the same code on a large number of servers/databases in parallel and will aggregate the result for you (SQL Farm, Red Gate's SQL MultiScript, and to some extent SQL Server Management Studio 2008 (this is not yet out though))
* There are tools that allow you to make sure that certain stored procedures are the same across many databases/servers, so maybe you would like to keep them locally in each database, and use such a tool to make sure you have the latest versions (advantage: this can check that the dependent objects exist as well, and backups of the database will also back up the relevant stored procedures too)
Regards,
Andras
January 11, 2008 at 6:53 am
Hello András Belokosztolszki ,
This time First of all Thank you for your instant reply.
it is working fine :).
I read your articles , i like it.
presently reading 'Why would one ever disable a foreign key?'
( so at present your are doing research . I saw your web-site it is interesting.)
Regards
Gaurav Teli
Software Engg.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply