Setting up RPC via Trigger

  • I have a control table into which new rows gets added whenever a new remote SQL Server is added (via a spcific SP on the remote server). Since I want the remote server (under a specific SQL login account) to be able to RPC to execute a common stored procedure - I've set up an AFTER INSERT trigger to set up RPC.

    Unfortunately when the trigger is executed I get the following error:

    [Microsoft][ODBC SQL Server Driver][SQL Server]The procedure 'sp_addlinkedserver' cannot be executed within a transaction

    [Microsoft][ODBC SQL Server Driver][SQL Server]The procedure 'sp_addremotelogin' cannot be executed within a transaction

    Does anyone know of any work around for this? The alternative is manual...

    Thanks

    Glenn

  • The image you posted is a broken link because it links to your pc.  You should just post the text from the error.


  • OK - I'm getting a bit closer. I modified the AFTER INSERT trigger to an INSTEAD OF INSERT trigger (code below). Now the new row gets inserted into the table and the linked server gets added. Unfortunately I still get the error: sp_addremotelogin cannot be executed with a transaction.

    Thoughts?

    ALTER  TRIGGER [AddServerRPC] ON [dbo].[ControlTable]

    INSTEAD OF INSERT

    AS

    INSERT INTO DbBackupParameters SELECT * FROM inserted

    COMMIT

    DECLARE @server varchar(50)

    DECLARE @serverid  smallint

    SELECT @server = (SELECT SQLServer FROM inserted)

    IF NOT EXISTS (SELECT srvname FROM master..sysservers WHERE srvname = @server)

    EXEC master..sp_addserver @server

    SELECT @ServerId = (SELECT srvid FROM master..sysservers WHERE srvname = @server)

    IF NOT EXISTS (SELECT  xstatus FROM master..sysxlogins WHERE srvid = @ServerId AND name = 'acct')

    BEGIN

     EXEC master..sp_addremotelogin @server, 'acct', 'acct'

    END

  • are you now getting one error message where before you were getting two, or are you now only getting just the one error message? Also, what version(s) of SS?

  • Steve - per my previous posting: I'm getting one error now - sp_addremotelogin cannot be executed within a transaction.

    The remote server is SQL 2000 SP4 (the local server is also SQL 2000 SP4)

    Glenn

  • Not sure why do you need that table at all.

    It repeats information stored in master system tables.

    CREATE VIEW [dbo].[ControlTable]

    AS

    SELECT srvname AS SQLServer, srvid as ServerId, ...

    FROM master.dbo.sysservers

    GO

    Now, instead of inserting into [dbo].[ControlTable] do proper adding of linked server.

    Your "ControlTable" will be "miraculously" populated.

    _____________
    Code for TallyGenerator

  • Tell you what... humor me - we have other reasons for managing data in this fashion. I'm looking for a technical discourse on the topic of using a trigger to automatically set up RPC. This method will eliminate several manual steps for the support staff whenever we stand up a new SQL server (physical or virtual).

    Glenn

  • Glenn,

    despite all your reasons the method you've chosen is unappropriate.

    End of story.

    There are a lot of other technical reasons why it should not be done this way.

    Rethink your approach.

    _____________
    Code for TallyGenerator

Viewing 8 posts - 1 through 7 (of 7 total)

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