SQL Server Performance Forum – Threads Archive

trying to get disk space info of a remote server

Hi, i was trying to execute the following sql to obtain disk space info on a remote server the sql looks like the following create table #drvlist (drive nvarchar(2),[MB free] int) insert into #drvlist EXEC OPENDATASOURCE( ’SQLOLEDB’, ’Data Source=SERVER_NAME;User ID=myuser<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=mypassword' ).master.dbo.xp_fixeddrives select drive, [MB free] from #drvlist drop table #drvlist however i got the error msg below OLE DB provider "SQLNCLI" for linked server "(null)" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 3 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "(null)" was unable to begin a distributed transaction. the opendatasource query works if i don’t try to insert the resulting recordset into a temp table any ideas??? thanks

check these links http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3451
http://blogs.msdn.com/florinlazar/archive/2003/12/04/41371.aspx madhu

i’m running this on a 2005 machine
but i’m actually connecting to a machine running windows 2000 sp4 + sql server 2000 sp3 8.00.760 and the opendatasource query works if i don’t try to insert the resulting recordset into a temp table
it only returns an error if i try to insert the recordset into a temp table

You need to have latest service SP4 on SQL Server 2000 to perform such actions, when performing from a SQL 2005 instance. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

I tried from SQL 2005 to 2000 SP4 machine getting the same error. Make sure "Network DTC Access" is enabled for "Allow Remote Client"… MohammedU.
Moderator
SQL-Server-Performance.com

the machine running sql server 2000 sp3 is still on win2k sp4
i checked the MSDTC page, and unlike windows 2k3 or windows xp, it doesn’t have an option to let you set security configuration for MSDTC any other ideas?? thanks

I;m talking about SP4 on SQL Server not on Windows 2000 operating system, which account is used to start MSDTC? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Alternate is you can use BCP/DTS… MohammedU.
Moderator
SQL-Server-Performance.com

]]>