Error quering linked server....

  • Hello,

    I’ve got the following queries to test one linked server, the point is that in Q1 the output appears in a moment and in Q2 no results are returned instead of it it appears to me the following output.

    Q1:

    select * from Linked_server.database_name.owner.table_name where 1 = 2

    Q2:

    :doze:

    begin distributed tran

    select * from Linked_server.database_name.owner.table_name where 1=2

    commit tran

    Server: Msg 7391, Level 16, State 1, Line 2

    The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

    [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

    OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

    :crazy:

    The poin tis that i've got a lot of linked servers on another SQL Server Hosts and i really don't remember if i'm missing something πŸ™

    Hope you can help me again

    Thanks and regards πŸ˜‰

    JMSM

  • This is a DTC error, not really a SQL error. Since you are only doing a select query in Q2, it should work if you remove the BEGIN TRANSACTION and COMMIT.

    We have had this issue with our servers, but I would actually have to walk through the DTC configuration to tell you what we had to do it make it work. PeopleSoft automatically used transactions between servers, so we had to fix it, couldn't just remove the BEGIN TRANS and COMMIT statements.

    If you need more help on this, let me know and I will see what I can do.

    😎

  • - is msdtc running at both instances ?

    - did you configure msdtc security ?

    Windows 2003 vs other windows ?

    - did you add the registry key ?

    -- zie HOWTO: Enable DTC Between Web Servers and SQL Servers Running Windows Server 2003

    --http://support.microsoft.com/Default.aspx?kbid=555017

    set nocount on

    -- is xp_cmdshell enabled ? if not, enable it

    declare @SQLcmdshell varchar(100)

    Create table #tmpConfigCmdShell (configName varchar(128), MinValue varchar(15), MaxValue varchar(15), ConfigValue varchar(15), RunValue varchar(15))

    Set @SQLcmdshell = 'sp_configure @configname = ''xp_cmdshell'''

    insert into #tmpConfigCmdShell

    exec (@SQLcmdshell)

    if exists (select * from #tmpConfigCmdShell where configName = 'xp_cmdshell' and RunValue = '0' )

    begin

    exec sp_configure @configname = 'xp_cmdshell', @configvalue = '1' ;

    RECONFIGURE WITH OVERRIDE;

    End

    declare @DosCmd varchar(500)

    Set @DosCmd = 'ver'

    SET nocount ON

    CREATE TABLE #tmpMsver (line varchar(8000))

    insert into #tmpMsver

    exec master..xp_cmdshell @DosCmd

    if exists (select *

    from #tmpMsver

    where line like '%Microsoft Windows _Version 5.2%')

    begin

    -- print @@servername + ' W2k3'

    declare @NewSQLArg varchar(50)

    declare @NumericValue int

    set @NumericValue = 1

    create table #tmpRegValues (Value varchar(50), Data varchar(1000))

    insert into #tmpRegValues

    exec master..xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSDTC'

    -- HKEY_LOCAL_MACHINE, then SOFTWARE, then Microsoft.

    -- 3. Right-click on MSDTC, point to Add, then select DWORD Value.

    -- 4. Rename the key from the default New Value #1 to TurnOffRpcSecurity.

    -- 5. Double-click the new key and change the value to 1.

    if not exists (select * from #tmpRegValues where Value ='TurnOffRpcSecurity' and Data = '1')

    begin

    print @@servername + ' Key TurnOffRpcSecurity toevoegen'

    --Bepalen volgende SQLArg.

    set @NewSQLArg = 'TurnOffRpcSecurity'

    --print @NewSQLArg

    -- exec xp_instance_regwrite -- N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSDTC', @NewSQLArg, N'REG_DWORD', '1'

    exec xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE',

    @key='SOFTWARE\Microsoft\MSDTC',

    @value_name='TurnOffRpcSecurity',

    @type='REG_DWORD',

    @value=@NumericValue

    Print 'Allow Distributed transactions' -- (W2K3 rtm)

    -- kan ook via DcomCnfg \ Component Services \ Computers \ My computer (rechtsklikken \ Properties \ MSDTC)

    exec xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE',

    @key='SOFTWARE\Microsoft\MSDTC\Security',

    @value_name='NetworkDtcAccess',

    @type='REG_DWORD',

    @value=@NumericValue

    exec xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE',

    @key='SOFTWARE\Microsoft\MSDTC\Security',

    @value_name='NetworkDtcAccessAdmin',

    @type='REG_DWORD',

    @value=@NumericValue

    exec xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE',

    @key='SOFTWARE\Microsoft\MSDTC\Security',

    @value_name='NetworkDtcAccessTransactions',

    @type='REG_DWORD',

    @value=@NumericValue

    -- XaTransactions voor DB2 en Oracle communicatie

    exec xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE',

    @key='SOFTWARE\Microsoft\MSDTC\Security',

    @value_name='XaTransactions',

    @type='REG_DWORD',

    @value=@NumericValue

    end

    else

    begin

    print @@servername + ' key reeds geΓ―nstalleerd'

    select cast(@@servername as char(25)) as ServerNaam, *

    from #tmpRegValues

    where Value ='TurnOffRpcSecurity'

    end

    drop table #tmpRegValues

    end

    else

    begin

    print @@servername + ' Geen W2k3 - Key niet van toepassing'

    end

    - did you fix the issue(s) with sql2000 sp3 and sp4 ?

    http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

    --

    --

    -- You may receive an error message when you try to run distributed queries

    -- from a 64-bit SQL Server 2005 client

    -- to a linked 32-bit SQL Server 2000 server

    -- Uit te voeren Query :

    C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thanks a lot for your answer.

    The point is that the error was one test to can simulate the error that people return to me when executing the DTC syntax.

    In this moment i can't tell you how is configured the Data Transaction Cordinator because someone is connected to this host remotely and i think that we have no licenses since i cant connect at this point on it :sick:

    :allien: So can you help me on what should i check on Data Transaction Cordinator since it wasn't me that have installed it and this is installed in a cluster and i haven't expirience working on it.

    Thanks again and regards,

    JMSM πŸ˜‰

  • To get you started (I have to go to a meeting now), search the Microsoft Knowledge Base with yoor error message. There are a couple of KB articles that should get you pointed in the right direction.

    😎

  • Hello again,

    The registry key is configured as follows:

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC]

    "TurnOffRpcSecurity"=dword:00000001

    I dont know what sould i do now, i did not change any value of this key.

    Thanks and regards,

    JMSM πŸ˜‰

  • This is scream day, meetings just keep getting in the way.....

    Any way, on the way to another one, but in fixing this issue for our PeopleSoft systems, I made no changes directly to the registry. All changes were done through MMC to the DTC process.

    Hopefully I will get a chance later to give you more details, but what KB articles did you read so far?

    😎

  • Meeting has been delayed. Question, what version of Windows Server are you running? We are using Server 2003 R2 Enterprise Edtion.

    😎

  • Basing this off our config.

    We are not in a cluster environment, so this may be different for you.

    Open Component Services, expand Component Services, expand Computers, right click My Computers and select properties.

    On the My Computer Properties, click the MSDTC tab, click the Securiity Configuration button.

    In this dialog box, Security Settings:

    Network DTC Access should be checked.

    Client and Adminitration, Allow Remote Clients should be checked.

    Transaction Manager Communication, Allow Inbound and Allow Outbound checkboxes should be checked, the No Authentication Required Radio Button should be selected.

    And, to be sure, DTC Logon Account should be NT Authority\NetworkService

    click OK, click OK, right click My Computer, Stop MS DTC, right click My Computer Start MS DTC (the stop and start may not be needed, as MS DTC may get restarted automatically when you accept your changes).

    This will most likely need to be done on all systems involved in distributed transactions.

    😎

  • Thanks a lot.

    Regards πŸ˜‰

    JMSM

Viewing 10 posts - 1 through 9 (of 9 total)

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