Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Msg 8501, Level 16, State 3, Line 1 MSDTC on server 'My-Server' is unavailable. Expand / Collapse
Author
Message
Posted Thursday, September 29, 2011 1:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 166, Visits: 639



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!!
Post #1182972
Posted Wednesday, October 12, 2011 9:32 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 166, Visits: 639
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!!


  Post Attachments 
MSDTC.JPG (87 views, 31.04 KB)
Post #1189537
Posted Thursday, October 13, 2011 1:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 29, 2015 11:06 AM
Points: 1,932, Visits: 1,595
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?


Dev

*** Open Network for Database Professionals ***
http://www.linkedin.com/in/devendrashirbad
Post #1189620
Posted Thursday, October 13, 2011 1:21 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 166, Visits: 639
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!!
Post #1189624
Posted Thursday, October 13, 2011 2:31 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 29, 2015 11:06 AM
Points: 1,932, Visits: 1,595
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..


Dev

*** Open Network for Database Professionals ***
http://www.linkedin.com/in/devendrashirbad
Post #1189648
Posted Friday, April 25, 2014 4:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 16, 2015 5:57 AM
Points: 17, Visits: 61
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.
Post #1565254
Posted Tuesday, April 29, 2014 2:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 16, 2015 5:57 AM
Points: 17, Visits: 61
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
Post #1566124
Posted Thursday, May 1, 2014 1:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 16, 2015 5:57 AM
Points: 17, Visits: 61
I'm unable to view MSDTC setting which is installed on 2000 server.
I think , i have to go and re install the services.
Post #1566834
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse