calling xp_fixeddrives on a remote server

Question

Post reply

calling xp_fixeddrives on a remote server

SQLdba-473999

SSCrazy

Points: 2761
More actions
November 13, 2008 at 2:48 pm

#193789

Hi,
My goal is to have a stored procedure go through all servers and store the results of xp_fixeddrives into 1 table. My problem is getting the stored procedure to run on a remote server.
I have created a linked sql server called server-a\testdb. (virtual server \ named instance)
This tsql statement works correctly
exec [server-a\testdb].master.dbo.xp_fixeddrives
Now to get it to work with a variable...
declare @servername nvarchar(30)
select @servername = '[server-a\testdb]'
exec @servername.master.dbo.xp_fixeddrives
But it returns an error: Incorrect syntax near '.'.
My next thought is that maybe I'm not storing @servername correctly, so I only run
declare @servername nvarchar(30)
select @servername = '[server-a\testdb]'
print @servername
and it returns exactly what I thought was being stored: [server-a\testdb]
And now I'm stumped...logic tells me this should work no problem...what am I missing???
Any help would be greatly appreciated!

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

I've never had to do that before but, according to BOL, here's the correct syntax for how to do it...

[font="Arial Black"]Execute a pass-through command against a linked server[/font]

{ EXEC | EXECUTE }

( { @string_variable | [ N ] 'command_string' } [ + ...n ]

[ {, { value | @variable [ OUTPUT ] } } [...n] ]

)

[ AS { LOGIN | USER } = ' name ' ]

[ AT linked_server_name ]

Check BOL for more info...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Damned smiley faces... wish they could fix that...

Here's the listing without the smiley...

[font="Arial Black"]Execute a pass-through command against a linked server[/font]

{ EXEC | EXECUTE }

( { @string_variable | [ N ] 'command_string' } [ + ...n ]

[ {, { value | @variable [ OUTPUT ] } } [...n] ])

[ AS { LOGIN | USER } = ' name ' ]

[ AT linked_server_name ]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Oh yeah... almost forgot...

The "AT linked_server_name" probably cannot be a variable... you'll need to use dynamic SQL to create the EXEC's, and then execute the dynamic SQL. That can be done without or with a "loop". Since it's not controlling RBAR, a loop isn't a mortal sin here, just a small one. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 4

Thank you for the info! I will try this now and let you know.

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 5

It worked!!!! Actually, the key was using dynamic sql. This is the end result..

DECLARE @ServerName nvarchar(30)

DECLARE @sql nvarchar(100)

select @ServerName = '[server-a\testdb]'

set @sql = @servername + '.master.dbo.xp_fixeddrives'

exec(@sql)

Thank you for all the help!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

ang (11/14/2008)
It worked!!!! Actually, the key was using dynamic sql. This is the end result..
DECLARE @ServerName nvarchar(30)
DECLARE @sql nvarchar(100)
select @ServerName = '[server-a\testdb]'
set @sql = @servername + '.master.dbo.xp_fixeddrives'
exec(@sql)
Thank you for all the help!

Outstanding... and thanks for posting the code you used in the feedback. My only question now is, did you verify it's reading the drives on THAT server and not the one you ran it from?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 7

My only question now is, did you verify it's reading the drives on THAT server and not the one you ran it from?

Sure did. In my case it was easy to tell since the local server only had 1 drive and the other had multiple.

I appreciate all your help. Thanks!

SQLdba-473999 SSCrazy Points: 2761 More actions · Answer 8

Just when you think you're done...another road block!

I want to save the results to a table, so changed it to the following:

DECLARE @ServerName nvarchar(30)

DECLARE @sql nvarchar(max)

set @ServerName = '[server-a\testdb]'

set @sql = 'insert into #disk_free_space (driveLetter, FreeMB) '

set @sql = @sql + 'exec ' + @servername + '.master.dbo.xp_fixeddrives'

exec(@sql)

Now this returns another error:

OLE DB provider "SQLNCLI" for linked server "server-a\testdb" returned message "The transaction manager has disabled its support for remote/network transactions.".

Msg 7391, Level 16, State 2, Line 1

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "server-a\testdb" was unable to begin a distributed transaction.

This doesn't make sense to me since I can run the exec xp_fixeddrives with no problem, now when I try to insert into the table it throws an error. Any ideas?

Thanks.

Matt Slocum Default port Points: 1497 More actions · Answer 9

Try this on for size:

DECLARE @ServerName varchar(32)

DECLARE @sql varchar(1024)

SET @ServerName = 'server-a\testdb'

SET @sql = 'SELECT Drive, [MB Free] FROM OPENQUERY([' + @ServerName + '],

''SET FMTONLY OFF; EXEC master.dbo.xp_fixeddrives'')'

INSERT INTO #disk_free_space (driveLetter, FreeMB)

EXEC (@sql)

I just ran into the same issue and found the OPENQUERY option to be able to run a query in another scope.