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: Wednesday, June 25, 2014 6:20 AM
Points: 159, Visits: 622



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: Wednesday, June 25, 2014 6:20 AM
Points: 159, Visits: 622
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 (76 views, 31.04 KB)
Post #1189537
Posted Thursday, October 13, 2011 1:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
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

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: 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: Wednesday, June 25, 2014 6:20 AM
Points: 159, Visits: 622
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: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
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

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1189648
Posted Friday, April 25, 2014 4:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 6, 2014 11:58 AM
Points: 17, Visits: 56
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: Monday, October 6, 2014 11:58 AM
Points: 17, Visits: 56
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: Monday, October 6, 2014 11:58 AM
Points: 17, Visits: 56
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