Using dynamic database name for DML in trigger 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,


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


Could anyone please guide me to achieve the same?
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?).