0

I am using SQL Server 2008 in my application. Here I need to do database operations between multiple databases. The complexity is database names are user defined except one masster database. I can have information about secondary database name in my master database, from which I have to fire DML statements. I am using following approach to achive the same:

USE [Database1]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[CTR_Production_InsertRecordInDatabase2] ON [dbo].[Table1]
AFTER  INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;     

    DECLARE @SecondDatabaseName varchar(125)    

    --Fetch second db name
    SET @SecondDatabaseName = dbo.sfn_GetSecondDbName()
    :SETVAR SecondDatabaseName @SecondDatabaseName

------------------------------INSERT TO Second DB------------------------------

    INSERT INTO $(SecondDatabaseName).[dbo].[pDM_Production_RecordInfo]
       (  Column1
        )           
        (     
          SELECT Column1 FROM INSERTED i
        );

But due to some misinterpretation of SQLCMD usage could not achieve the same.

Here my requirement is to,

  1. Fetch secondary db name from Table1 in Database1
  2. set to some variable
  3. use it to insert data in secondary database

Could anyone please guide me to achieve the same?

0
1

What a brittle solution. Does the return value of sfn_GetSecondDbName() really change that often? Where does its value come from? Would be much easier to hard-code the database in the trigger, and change the trigger when that changes, rather than change the function and try to "save work" by not having to update the trigger.

Your current approach will require nested levels of dynamic SQL, and this becomes more complicated because dynamic SQL can't see the scope where inserted is, so additionally you'll have to dump those contents into a #temp table before you can access them from dynamic SQL. Ugly, ugly, ugly.

ALTER TRIGGER [dbo].[CTR_Production_InsertRecordInDatabase2] 
ON [dbo].[Table1]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #inserted(column1 INT); -- guessing
  INSERT #inserted(column1) SELECT column1 FROM inserted;

  DECLARE 
    @db  SYSNAME = dbo.sfn_GetSecondDbName(), 
    @cmd NVARCHAR(320),
    @sql NVARCHAR(MAX) = N'INSERT dbo.pDM_Production_RecordInfo(column1) 
      SELECT column1 FROM #inserted;';

  SET @cmd = @db + N'.sys.sp_executesql';
  EXEC @cmd @sql;
END
GO

(Also note that nothing will get inserted in the event of a delete, since inserted will be empty.)

Better yet, store this data in a staging table that is in this database, then create a backup job that moves the data to the other database. That way the trigger doesn't have to do all this dynamic dynamic dynamic SQL, and the transaction that called it isn't affected by any delays there or communicating with the other database (think about the fact that another database could be offline, single user, detached, or the customer could have fat-fingered the name when they changed it - do you want any of these things to block DML on the primary?).

2
  • Aaron Bertrand, thanks for the response. I understand that this one is not a clever solution but the thing is, Currently my application having 2 databases which may extend based on plugins. Here one is master/base database and other are secondary. Based on user actions master db have to update secondary using triggers. Now as per change of requirement user wants to make this db(master n secondary) names variable (fixed earlier) which user might change from SSMS and later on select changed db on application launch. So that user can maintain multiple version dbs in same server instance. conti.. Jan 16 '15 at 6:04
  • ..I am aware about dynamic query like you proposed but it takes lot of time to update existing development and so i want to make it possible using any alternative if available. Here only master db contains information regarding secondary db info. Which sfn_GetSecondDbName() will return on demand. Jan 16 '15 at 6:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.