SQL Server Performance

Achieving a concurrency while recreating link server

Discussion in 'SQL Server 2005 General DBA Questions' started by pritesh.akhani, Oct 31, 2007.

  1. pritesh.akhani New Member

    I have created a stored procedure in which i am passing a CatalogName as a parameter.
    The purpose of stored procedure is to change the catalog name of Link Server.The Stored
    procedure will first delete the current link server and the recreate the link server.


    ALTER PROCEDURE [dbo].[UpdateLinkedServerCatalog]
    @CatalogName VARCHAR(500)

    AS
    BEGIN

    DECLARE @srvname VARCHAR(500)
    SET @srvname='srvname'

    exec sp_droplinkedsrvlogin @srvname,null
    exec sp_dropserver @srvname
    exec sp_addlinkedserver @srvname,'','SQLOLEDB', NULL,NULL,'SERVER=gtl-83;UID=test;PWD=test;',@CatalogName
    exec sp_addlinkedsrvlogin @srvname, 'false', NULL, NULL, NULL
    END


    I want to use this thing in transaction to allow only single user to access the code at a same time
    but when i put the body of stored procedure in transaction then i got an error that "The procedure 'sp_droplinkedsrvlogin' cannot be executed within a transaction."

    My Questions are

    Is there any way to execute these all statments in a single transaction ?
    Is there any way to execute these all statments in a single transaction without BEGIN TRANSACTION.....COMMIT TRANSACTION

  2. Adriaan New Member

    What happens if you run the script direct, instead of as a stored procedure?
    Anyway, why not use a table where the proc inserts user name, but first check if there's already another row - if not then insert current user name, and do the actual work on the linked server, then delete the user name from table, else either wait until the table is empty, or get out with a warning.

Share This Page