Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2007

    Question Unanswered: Selecting version of remote SQL instance into local variable?

    Hello all,
    I'm a bit new to SQL Server and T-SQL (my background is in DB2), so hoping you can help me with this. I'm writing a T-SQL script that's hopefully going to scan a bunch of SQL server instances and record the results. We've got a mix of SQL2K and SQL2K5 instances, and for part of my script I only want to run something if the remote instance is SQL2K.

    I'd tested this on my local PC with several instances created and it worked fine. When I try and run it for instances located on other servers it barfs . From what I've found from looking up the error message it looks like I've got myself a loopback -- but I don't know how to get around it - any ideas please?

    DECLARE @tempdata TABLE (scratch sql_variant)
    DECLARE @debug tinyint
    DECLARE @server sysname
    DECLARE @version varchar(20)
    SET @debug=1
    INSERT INTO @tempdata (scratch) EXEC ('[' + @server + '].master.dbo.sp_executesql N''SELECT SERVERPROPERTY(''''ProductVersion'''')''')
    SELECT @version = convert(varchar(100),scratch) from @tempdata
    DELETE FROM @tempdata
    IF @debug>0 BEGIN PRINT @server + ' is running SQL Server version: ' + @VERSION END
    IF charindex('8.00',@version) > 0 BEGIN
        PRINT 'SQL2K-only code goes here'

    The error I get is:
    OLE DB provider "SQLNCLI" for linked server "SQLSERVER_INSTANCE" returned message "The transaction manager has disabled its support for remote/network transactions.".
    Msg 7391, Level 16, State 2, Line 1
    The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "SQLSERVER_INSTANCE" was unable to begin a distributed transaction.

    I get this error running from a SQL2K5 instance against both SQL2K or SQL2K5

    TIA, moff.

  2. #2
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    is the MS DTC started?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2007
    sorry should have said - checked all that, found the KB article, went through all the settings - looks fine.

    other remote transactions are working fine, it's just this loopback one I'm struggling with.

  4. #4
    Join Date
    Feb 2007
    for info, got the answer to this here

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts