I have created multiple databases storing multi-country data. Tables and store proc in each db are exactly the same except they are placed and run in different dbs in runtime.
My question is:
Is it possible to centralize all the store proc and function into a central db (or a dummy db) for easy maintenance but it allows us to point to the desired database environment in runtime even I run the store proc in central database.
I find the "Use database" command but it does allow us to use it in store proc. Any advice or suggestions?
Thanks in advance
bryanAs long as the account that is running the stored proc has access to the other DBs then you can do it. So a stored proc in db1 can retrieve data from db2.
SELECT * from db2.tblStuff
I would use views so look into those.|||I would look for the System Stored Procedure called SP_Auto start it is in the Master Database, I must warn you there 930 plus System stored procs so it will take time to find it. It makes Stored Proc maintaince easy because it puts all your stored procs in the SQL Server Procedure Cache any time the Operating System starts just like SQL Server Service Auto start. The SQL Server Procedure Cache uses the least used Algorithm to remove less used Stored Procs but SP_Auto start puts them back. Hope this helps.
Kind regards,
Gift Peddie|||Hi,
What I am looking for is something about multiple databases but using a central place to store store only one set of shared store procs and they can be called in their individual db when necessary.
I can't find the exact name of "sp_auto" in master db. Could you give me more hints about that. Or anyone with other suggestions?
Thank you very much
bryan|||exec sp_procoption @.ProcName='Your proc',@.OptionName='startup',
@.OptionValue='true'
Sorry I seem to be making a lot of mistakes lately, the code above is what I mean the SP-Procoption is used to start all procedures and very critical procedures like those used for Cluster Service during start up of the OS. Hope this helps.
Kind regards,
Gift Peddie
No comments:
Post a Comment