June 20, 2011 at 12:55 pm
Hi All,
INSERT localserver.localdb.dbo.DriveSpace EXECUTE MyServer.master.dbo.xp_fixeddrives
Using above query while fetching data across a linked server, I came across below error.
OLE DB provider "SQLNCLI" for linked server "MyServer" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "MyServer" was unable to begin a distributed transaction.
While researching on this, I found that I may need to change the Security setting for Network DTC access (Start -> Program -> Administrative Tools -> Component Services). Will there be any other problems/disadvantages of doing this change?
Thanks in advance...
June 20, 2011 at 1:12 pm
Does this require restarting SQL Server Service too?
June 20, 2011 at 2:14 pm
I don't think it will require a SQL restart, a MSDTC restart for sure.
For machines that will not be on the receiving end of DTC calls I generally do not turn on the remote access.
And I would say that you should be very careful with linked servers. They are often misused or over used.
CEWII
June 20, 2011 at 2:31 pm
Thanks Elliott...
June 20, 2011 at 2:33 pm
you are very welcome.
CEWII
June 20, 2011 at 2:36 pm
One question though,
when I execute,
EXECUTE MyServer1.master.dbo.xp_fixeddrives -- It gives me drives available and free space result
But the same thing for another server gives me no result. It runs successfully but I get blank result. This works for few servers and doesn't work for few other. Why is that?
June 20, 2011 at 3:11 pm
I'm guessing it might be a permissions issue, such as SQL is not running as a use that has enough rights to see all the drives, but I'm not sure.
Also, xp_fixeddrives is not the ost reliable way to get drive names and space. If you ever use mount points xp_fixed drives will not show them correctly.
For example you have a drive D that is 10GB and you mount a 20GB volume at D:\MountPoint, xp_fixed drives wil ljust show the D: at 10GB. The better way is to use WMI to query the mount points which will give you all drives mounted, either at the root level (D:\) or as a mount point.
CEWII
June 21, 2011 at 6:46 am
Yes you were right. There was a permissions issue. I was using a SQL login to connect to linked server and that login needs to have sysadmin rights on the destination server too.Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply