I'm new to these forums (and to SQL Server), so please be gentle with me.
I am developing a process to obtain information on all our remote servers/databases, and store it in a single local database.
I'm after things like db size, last backup date, free drive space etc...the usual weekly statistics.
I've linked the remote servers to my local one, and have written a few simple procedures (which exist on the local server) to grab backup and file size information from the remote tables. The output is stored locally in tables which we can then query as necessary.
I am having difficulty obtaining the free drive space details.
I'm using :-
'exec <remote_server>.master.dbo.xp_fixeddrives' to get the info, but I cannot store the output in a table on the local server. (remote_server_name, date, drive_letter, space_mb)
I wish to avoid creating any objects on the remote servers if at all possible.
I really want to pass the remote server name into the procedure, and the output to be inserted into the table.
Thanks for the reply Blindman, I think the issue is that the insert is being turned into a distributed transaction when the "xp_fixeddisks" function is being called eg...
"insert into my_local_database.dbo.disk_space (drive,mb_free)
returns the error "MSDTC on server 'my_local_server' is unavailable".
Whereas the sql to get the file size stats, uses pure sql rather than the exec and works :-
"insert into my_local_database.dbo.db_growth select 'the_remote_server','northwind', getdate() as timestamps, *
If it is run locally, the insert works :-
"insert into my_local_database.dbo.disk_space (drive,mb_free) exec master.dbo.xp_fixeddrives"
it is the "exec the_remote_server......." bit that is giving me problems.
I could get it to work by using two processes - (1) the remote server runs the function and inserts into a table of it's own, then (2) my_local_server grabs that data and inserts into the central table, but I'd like to avoid creating tables or making too many config changes on the remote servers if at all possible.