Error with Linked Server and Dynamic SQL

  • GBeezy

    SSCarpal Tunnel

    Points: 4468

    I am attempting to execute the following code via SQL agent job.

    USE [dataASH]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROC [dbo].[usp_CaptureStatusData] (

    @RunFeat TINYINT = 0,

    @ELSonly TINYINT = 0

    )

    AS

    DECLARE

    @LSname SYSNAME

    ,@SQL NVARCHAR(3000)

    ,@curflag TINYINT

    ,@ErrString VARCHAR(200)

    ,@Trans INT

    ,@ret INT

    --Initialization

    SELECT @curflag = 0

    --Processing

    SET @Trans = 0

    SET @Trans = @@TRANCOUNT

    IF @Trans = 0

    BEGIN TRAN

    DECLARE ls_cursor INSENSITIVE CURSOR

    FOR

    SELECT

    a.Name

    FROM Sys.servers a (NOLOCK)

    WHERE 1=1

    AND a.is_linked = 1

    AND a.server_id <> 0

    ORDER BY a.Name

    OPEN ls_cursor

    FETCH NEXT

    FROM ls_cursor

    INTO @LSname

    SELECT @curflag = 1

    WHILE @@FETCH_STATUS = 0

    BEGIN

    ---Loading Status Data

    SELECT @SQL = 'DECLARE @ret INT, @pdate DATETIME

    SELECT @pdate = getdate()

    EXEC @ret = ' + @LSname + '.DataAdmin.dbo.usp_ASHDbStatus @pdate

    IF @@ERROR <> 0

    RAISERROR(''Inner Error'', 16, 1)'

    --PRINT @SQL

    EXEC (@SQL)

    IF (@@ERROR <> 0) OR (@ret <> 0)

    BEGIN

    SELECT @ErrString = 'Error loading ' + @LSname + ' db status.'

    GOTO ERREXIT

    END

    FETCH NEXT

    FROM ls_cursor

    INTO @LSname

    END

    CLOSE ls_cursor

    DEALLOCATE ls_cursor

    SELECT @curflag = 0

    IF @Trans = 0 AND @@TRANCOUNT > 0

    COMMIT TRAN

    RETURN (0)

    ERREXIT:

    IF @curflag = 1

    BEGIN

    CLOSE ls_cursor

    DEALLOCATE ls_cursor

    END

    IF @Trans = 0 AND @@TRANCOUNT > 0

    ROLLBACK TRAN

    RAISERROR ('%s', 16, 1, @ErrString)

    RETURN(1)

    This is a code rewrite that uses Dynamic SQL (as you can see ๐Ÿ™‚ so that we dont have duplicate code thru out the SP. Below is the error message I am getting:

    Executed as user: ASH\ASHSQLserv. The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "LS01" was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) OLE DB provider "SQLNCLI10" for linked server "LS01" returned message "The transaction manager has disabled its support for remote/network transactions.". [SQLSTATE 01000] (Error 7412). The step failed.

    If I un-comment out the PRINT, I copy that result to a new Query Window and it executes successfully. I can run a SELECT statement across the Linked Server, so I dont think it is a problem with MSDTC. I did check all of the MSDTC settings though and everything appears to be started and in good order.

    Below is what I get with my PRINT statement un-commented out:

    DECLARE @ret INT, @pdate DATETIME

    SELECT @pdate = getdate()

    EXEC @ret = LS01.DataAdmin.dbo.usp_ASHDbStatus @pdate

    IF @@ERROR <> 0

    RAISERROR('Inner Code Block Error', 16, 1)

    How can I get the above SPROC to loop over over the Linked Servers and execute the SQL dynamically?

    Any and all help will be greatly appreciated.

    Thanks in Advance!

  • sturner

    SSC-Insane

    Points: 22411

    It appears that all of this dynamic SQL is running in one giant distributed transaction across however many SQL servers are in your sys.servers table. For one thing, I would question why that is necessary, it makes whatever is happening require a lot more of a footprint on your concurrency and resources that would otherwise be the case with a different technique, but might not work correctly with differing brands/versions of databases servers (or SQL Servers).

    What actually is the the stored procedure trying to do at the remote server?

    The probability of survival is inversely proportional to the angle of arrival.

  • ChrisM@Work

    SSC Guru

    Points: 186077

    Sturner has a good point.

    You might also want to check your firewall settings[/url].

    [font="Arial"]โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • GBeezy

    SSCarpal Tunnel

    Points: 4468

    The SPROC being called by the Dynamic SQL is a gathering metadata about each database on a Linked Server. The reason for this, is that we are undertaking a big migration, which means getting rid of Linked Servers, adding new Linked Servers, etc...The goal is to make the code Dynamic so that for each change requires minimal intervention. As you could guess, I have other SPROCs that I need to make dynamic as well.

    Thank You

  • GBeezy

    SSCarpal Tunnel

    Points: 4468

    Chris, Thanks for the reply, but why would I need to check the firewall settings, when I can run the old SPROC that hardcodes everything (it works fine) and when I use the PRINT statement, I can execute the "guts" of the dynamic SQL with no problems.

    Thank You

  • sturner

    SSC-Insane

    Points: 22411

    Gathering data across different databases and/or servers does not require that everything take place in one single transaction... unless I am missing something. That is why I was interested in what the procedure is doing.

    The probability of survival is inversely proportional to the angle of arrival.

  • sturner

    SSC-Insane

    Points: 22411

    GBeezy (7/8/2013)


    Chris, Thanks for the reply, but why would I need to check the firewall settings, when I can run the old SPROC that hardcodes everything (it works fine) and when I use the PRINT statement, I can execute the "guts" of the dynamic SQL with no problems.

    Are you actually running all of the generated SQL all together in a single transaction as your original code is doing or just executing one iteration of the cursor?

    The probability of survival is inversely proportional to the angle of arrival.

  • GBeezy

    SSCarpal Tunnel

    Points: 4468

    The intention is to loop over each Linked Server, run the transaction for that Linked Server, and then fetch the next Linked Server. That being said, I have gotten this to work. By changing Server Option (within the Linked Server properties) "Remote Proc Transaction Promotion" to false, the SPROC was able to complete successfully. I still want to test this solution and see how it affects other internal processes that use the Linked Server.

    Here are some links to some articles regarding the above mentioned server option:

    http://blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx

    http://armaitus.wordpress.com/2011/09/30/sql-server-2008-inserting-from-a-stored-procedure-via-linked-servers/

    http://technet.microsoft.com/en-us/library/ms178532.aspx

    Thanks Again for all the help, I do appreciate it.

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

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