July 19, 2007 at 11:51 am
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
July 19, 2007 at 12:01 pm
July 19, 2007 at 12:45 pm
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
July 19, 2007 at 2:20 pm
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?
July 19, 2007 at 2:26 pm
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
July 19, 2007 at 8:40 pm
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
July 20, 2007 at 7:40 am
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
July 20, 2007 at 7:54 am
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