Post reply

EXEC ('USE [DataBase] GO; '+@trigger) AT [linkedServer]

  • July 19, 2012 at 3:41 am

    #276076

    Hi,

    I might be doing this in the wrong way. But I need your help. (It is SQL Server 2008 SP1)

    I have dynamically created a string containing the SQL to create a trigger, this must be executed on remote server. I would prefer if I could create the trigger by running a SP on the local SQL server, which then executes the dynamically created SQL on the linked server.

    In short: the variable @trigger, contains the statement that would create the trigger on Server X, Database X and table X.

    But this does not work:

    SELECT @execute = 'EXEC (@trigger) AT ['+@sourceserver+']'

    SELECT @param = N'@trigger nvarchar(4000)'

    EXEC sp_executesql @execute,@param,@trigger

    Because it will create the trigger in the default database (in this case master, and will look for a table called X, which is not there).

    I cannot do this either:

    SELECT @execute = 'EXEC (''USE '+@sourcedatabase+' ; ''+@trigger) AT ['+@sourceserver+']'

    SELECT @param = N'@trigger nvarchar(4000)'

    EXEC sp_executesql @execute,@param,@trigger

    I have tried with GO also.. (Is this because EXEC only can execute a single statement?)

    Is there no way to execute two statements, where the second statement "continues" after the "first".

    The problem is also that "CREATE TRIGGER" can only be executed on the current database.. 🙁

    Please help, if you can.

    Kind regards,

    Anders Hansen

  • July 19, 2012 at 4:53 am

    #1514739

    the error I get when executing the:

    SELECT @execute = 'EXEC (''USE '+@sourcedatabase+' ; ''+@trigger) AT ['+@sourceserver+']'

    SELECT @param = N'@trigger nvarchar(4000)'

    EXEC sp_executesql @execute,@param,@trigger

    is:

    EXEC ('USE SyncTestBase ; '+@trigger) AT [FEBKH03\COAS]

    @trigger nvarchar(4000)

    Msg 111, Level 15, State 1, Line 2

    'CREATE TRIGGER' must be the first statement in a query batch.

  • July 19, 2012 at 5:23 am

    #1514755

    I found out, that changing the default database for the user which is used with the linked server, to the database which is solves it. But still, not satisfying if you ask me. I would like to be able to do this, no matter which database is default for the user.

    Can you help?

  • July 19, 2012 at 5:38 am

    #1514763

    whoops that was wrong;

    i left it in place but striken.

    i got the same error you mentioned when i tried this:

    Msg 2108, Level 15, State 1, Procedure TR_NewTrigger, Line 4

    Cannot create trigger on 'SandBox.dbo.AllMeasures' as the target is not in the current database.

    I think your @trigger needs to use the full three part naming convention for the target table; then you don't need a default database:

    then you don't need a use statement or anything else.

    declare @trigger varchar(max)

    SET @TRIGGER = 'Create Trigger dbo.TR_NewTrigger ON SandBox.dbo.AllMeasures

    FOR INSERT,UPDATE

    AS

    UPDATE AllMeasures

    SET CreatedDate = GetDate()

    FROM INSERTED

    WHERE AllMeasures.ID = INSERTED.ID'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 20, 2012 at 2:17 am

    #1515347

    Is there really no way?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply