Post reply

Msg 8501, Level 16, State 3, Line 1 MSDTC on server 'My-Server' is unavailable.

  • September 29, 2011 at 1:17 am

    #248658

    I tried following code in SQL2K8r2..... for getting login modes of Linked server's

    code is absolutely correct. But when I tried to execute it gives error like...

    Msg 8501, Level 16, State 3, Line 1 MSDTC on server 'My-Server' is unavailable.

    I tried by restarting DTC service.... but still same error persists.

    code :

    --------------------------------

    DECLARE @srvname varchar(50)

    create table #temp(name varchar(20),mode varchar(50))

    create table #servtemp(servername nvarchar(50))

    DECLARE @getsrvname CURSOR

    --SET servername = [@srvname]

    --Declare @servername nvarchar(50)

    SET @getsrvname = CURSOR FOR SELECT name FROM master.sys.servers where is_linked <> 0 -- SET cursor statement

    OPEN @getsrvname

    FETCH NEXT

    FROM @getsrvname INTO @srvname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    insert into #temp exec( '['+ @srvname +' ] .master.sys.xp_loginconfig [login mode]')

    insert into #servtemp values(@srvname)

    exec('select * from #servtemp,#temp')

    truncate table #temp

    FETCH NEXT

    FROM @getsrvname INTO @srvname

    END

    CLOSE @getsrvname

    DEALLOCATE @getsrvname

    --drop table #servtemp

    --drop table #temp

    Sagar Sonawane
    ** Every DBA has his day!!:cool:

  • October 12, 2011 at 9:32 pm

    #1393770

    As I said there wasnt any problem with code itself.... the problem was at Linked Server Server Options...

    Please find attachement....

    Sagar Sonawane
    ** Every DBA has his day!!:cool:

  • October 13, 2011 at 1:13 am

    #1393835

    As I said there wasnt any problem with code itself.... the problem was at Linked Server Server Options...

    I am sorry but I didn't get you. You are facing some issue with Linked Server and you know the reason for that issue. Then why are you posting it here? Is it FYI post?

  • October 13, 2011 at 1:21 am

    #1393838

    Dev @ +91 973 913 6683 (10/13/2011)

    As I said there wasnt any problem with code itself.... the problem was at Linked Server Server Options...

    I am sorry but I didn't get you. You are facing some issue with Linked Server and you know the reason for that issue. Then why are you posting it here? Is it FYI post?

    I faced that issue...and after posting here.... I have resolved that issue... I thought to post the resolution for the problem here... so in case if anyone will have same problem will get a solution.... I do post here to only increase my knowledge...

    Sagar Sonawane
    ** Every DBA has his day!!:cool:

  • October 13, 2011 at 2:31 am

    #1393860

    if anyone will have same problem will get a solution

    Nice Thought... and this is what I meant by FYI (For Your Information) post 😀

    I do post here to only increase my knowledge...

    You are most welcome here..

  • April 25, 2014 at 4:43 pm

    #1708912

    hi

    I'm too facing the same error

    i'm running the below query from source server

    INSERT INTO <source_tablename>

    exec [destination server name].[master].[dbo].[xp_fixeddrives]

    Msg 8501, Level 16, State 3, Line 1

    MSDTC on server '<source server>' is unavailable.

    I checked the linked server option and enable promotion of distribution transcation is true.

    but still i'm getting error ..

    please help me out in finding the solution.

  • April 29, 2014 at 2:01 pm

    #1709622

    I have enable my MSDTC services too..

    but still unable to run the query

    i have changed the MSTDC setting by checking allows remote client connection, and checking in-bound and out bound transaction.

    but still i'm facing the error.

    Any one could please provide me a information on this

  • May 1, 2014 at 1:09 pm

    #1710200

    I'm unable to view MSDTC setting which is installed on 2000 server.

    I think , i have to go and re install the services.

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

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