October 10, 2019 at 7:45 pm
The present database server I have consists of 6 databases based on Technical & Non technical sectors.
For Technical sector, has 3 DBs namely BizTech, BizTechArchive & BizTechLog
For Non technical sector, has 3 DBs namely BizNonTech, BizNonTechArchive & BizNonTechLog.
BizTech & BizNonTech are the main databases. BizTechArchive & BizNonTechArchive arethe archive Dbs where data is archived when some updation & deletion happens in the main DBs. These archive DBs has all tables present in the main DBs. The other DBs are for logging the error if some error occurs.
For example, in every stored proc of BizTech database, in the CATCH block, error is caught & inserted into respective Log DB tables.
INSERT INTO BizTechLog.dbo.errorlog (Collumn1, Column2..)
How can I pass the database name as a parameter such that I can pass it to every stored procedure in BizNonTech CATCH block i.e. BizNonTech is passed as input param say @dbname & used as
INSERT INTO @dbname.dbo.errorlog (Collumn1, Column2..)
Please guide me on this. Thanks
October 10, 2019 at 7:48 pm
no need to pass anything as a parameter.
In each database just setup a synonym pointing to the corresponding log db and on your code you just use the synonym in instead of the hardcoded db name (which should almost never be used (possible exception for utilities databases)
October 11, 2019 at 10:36 am
Dear friend, thanks for the insight. You made my day.
I have created Synonyms & it is working.
Now I have a doubt. I have created a Synonym for 'BizTechArchive. dbo' in main DB BizTech
USE BizTech
CREATE SYNONYM Int_Archive FOR BizTechArchive.dbo;
But when I execute the below command in BizTech DB, it is throwing error. Why is it so?
select * from Int_Archive.Table1
Should I create a seperate SYNONYM for BizTechArchive.dbo.Table1 and all other tables in BizTechArchive DB?
October 11, 2019 at 10:46 am
Unfortunately, you can't create a synonym for a schema, only for individual objects. The reason you didn't get an error when you created the synonym is that the name is checked at run time, not at create time.
John
October 14, 2019 at 5:26 am
Even though I can create a synonym for "BizTechArchive.dbo", I cannot use it.
So I need to create individual synonyms for each table like BizTechArchive.dbo.Table1
Is there any alternative for this?
October 14, 2019 at 8:50 am
Yes. You can use dynamic SQL. Make sure you understand what SQL injection is and how to prevent it before you play with this.
DECLARE @DBName sysname;
DECLARE @SQL nvarchar(200);
-- This step is essential to guard against SQL injection
SELECT @DBName = name
FROM sys.databases
WHERE name = @DBName;
SET @SQL = N'INSERT INTO ' + @DBName;
SET @SQL = @SQL + N'.dbo.errorlog (Collumn1, Column2..)';
EXEC sys.sp_executesql @stmt = @SQL;
John
October 15, 2019 at 1:19 pm
Hey George,
This gets into a rather tricky area. Referencing error message 15250:
"The database name component of the object qualifier must be the name of the current database."
As John suggested, this will likely require dynamic SQL. There's a great question on StackOverflow in which the OP wants to return the remote database name.
How to return the remote database name which is calling a stored proc in a different database?
I have accomplished this before using dynamic SQL and embedding the following code within my stored procedure:
IF @DatabaseName IS NULL
BEGIN
SELECT TOP (1) @DatabaseName = DB_NAME(resource_database_id)
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
AND resource_type = 'DATABASE'
AND request_owner_type = 'SHARED_TRANSACTION_WORKSPACE'
ORDER BY CASE WHEN resource_database_id = DB_ID() THEN 1 ELSE 0 END;
END;
I hope this helps to at least point you in the right direction!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply