September 17, 2019 at 8:57 pm
I am considering creating a "Tools" (or similar name) database to hold common procedures and functions so that they don't have to be created and maintained on every individual database. I'm open to comments on whether that is a good idea or not, but that is not my question. For functions that require knowledge of the originating database, it would be nice to be able to determine that value inside the procedure rather than having to pass in that value. However, I cannot figure out how to do that.
Here is what I have tried. On my "Tools" database, I created some "proof of concept" and testing code:
USETools
GO
CREATE-- DROP
PROCjtfMyDB
AS
SELECT[MyDB sys.sysprocesses] = DB_NAME(dbid) FROM sys.sysprocesses WITH(NOLOCK) WHERE spid = @@SPID
SELECT[MyDB sys.dm_exec_sessions] = DB_NAME(database_id) FROM sys.dm_exec_sessions WITH(NOLOCK) WHERE session_id = @@SPID
GO
CREATE-- DROP
PROCjtfTestWho
AS
EXECsp_who2 @@SPID
GO
EXECTools.dbo.jtfMyDB
EXECTools.dbo.jtfTestWho
-- OUTPUT
MyDB sys.sysprocesses
-------------------------------------------------------------------------------------------------------
Tools
MyDB sys.dm_exec_sessions
-------------------------------------------------------------------------------------------------------
Tools
SPID Status Login BlkBy DBName
----- ------------------------------ -------------------------------------------------- ----- ------ ...
53 RUNNABLE DOMAINNAME\jonathan.fahey . Tools
This correctly shows that my "current" database is "Tools", and all three versions produce the same result. Now I change my "current" database to another working database, "LiveData". When I call the procedures on "Tools" ...
USELiveData
GO
EXECTools.dbo.jtfMyDB
EXECTools.dbo.jtfTestWho
... I get the same results (I won't post duplicate results). I have not changed my "current" database on my connection, but the results show that SQL Server thinks my current database is "Tools".
I thought that maybe creating and calling a procedure on "LiveData" would give me different results, because I am explicitly calling a procedure on the "LiveData" database, which would ensure that my "current" database on the connection is "LiveData".
USELiveData
GO
CREATE-- DROP
PROCjtfLocalProc
AS
EXECTools.dbo.jtfMyDB
EXECTools.dbo.jtfTestWho
GO
EXECLiveData.dbo.jtfLocalProc
Again, all three methods - sys.sysprocesses, sys.dm_exec_sessions, and sp_who2 - say that my "current" database is "Tools", when clearly my SSMS is connected to "LiveData" and I am calling a procedure on "LiveData".
I tried checking the system stored procedures on "master" to see how they work, but the ones I checked simply reference "db_name()", which indicates to me that SQL Server keeps the connection on the "current" database and simply finds the code to execute on "master" rather than on "LiveData", then executes it in the context of "LiveData". But when I create procedures that reside on "Tools", SQL Server seems to change the "current" database to "Tools" when executing that code. That makes sense because it allows us to procedures on other databases and have them return "local" results. That is usually what I want, but not in this case.
My question: Is there any way in the procedures on "Tools" to determine that the originating call came from "LiveData" without explicitly passing in the originating database name?
September 18, 2019 at 4:38 pm
I've never been able to find a way to do this. I ended up using CONTEXT_INFO() [*] or sp_set_session_context to pass the database_id to the called proc. [*] With provisions for how to pass levels of dbs too (i.e. procA calls db2.procB which in turn call db3.procC, etc.), which can also be done if needed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 25, 2019 at 1:15 pm
I also do not know of a way to determine the originating database.
I see value in having only a single copy of your tools objects in a tools database. Your strategy would reduce the maintenance costs of ensuring all objects exist and are current in all databases. However, I think this type of centralization would create a dependency between each of your databases and the tools database such that a database could not be restored over to a new server unless the tools database was also restored over. Over time I have come to value not having dependencies between databases.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply