obtaining disk space

Hi,
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.

Many thanksset nocount on

create table #FreeSpace(Drive char(1), MB_Free int)

insert into #FreeSpace exec master..xp_fixeddrives

drop table #FreeSpace
go|||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)
exec the_remote_server.master.dbo.xp_fixeddrives"

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, *
from the_remote_server.northwind.dbo.sysfiles"

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.

Thanks.|||I'd like to avoid creating tables or making too many config changes on the remote servers if at all possible.

Thanks.
No changes on remote server.

create table FreeSpace
(
Drive nvarchar(5),
MB_Free nvarchar(20)
)
go
create Procedure remoteServerDiskSpace_sp (
@.servername nvarchar(4000),
@.FileName varchar(50)
)
AS
declare @.bulksql varchar(200)
Begin


SET @.servername = 'osql -Slocalhost -E -q"SET NOCOUNT ON; exec ' + @.servername +' .master.dbo.xp_fixeddrives" -s"," -n -w8000 -o"' + @.FileName +'"'

Exec master..xp_cmdshell @.servername, NO_OUTPUT
set @.bulksql='BULK INSERT FreeSpace FROM '''+@.FileName+''' WITH (FIELDTERMINATOR = '','',FirstRow=3)'
exec(@.bulksql)
END
GO
-- execute query
exec remoteServerDiskSpace_sp 'remoteserverName','c:\DiskSpace.csv'
--select query
select * from FreeSpace|||Thanks Mallier,
I've now done something in a similar vein to your routine, and it works a treat.

Obsolete technology - dblibrary

Hello!
In my application I need to use extended stored procedures.
Developing these extended stored procedures I use the "dblibrary APIs" to
access MS SQL Server 2000.
As far as I understand /reading articles in MSDN/, Microsoft declares its
intention not to support dblibrary in future.
Which is the recommended method to access MS SQL Server?
Thank you!
In order, the preferences are:
ADO.Net (but you have to be writing in VB.Net or C#)
ADO
OLE-DB
ODBC
SQL-DMO for controlling the server instead of manipulating the data.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>
|||I'd just tack on SQLXML/IIS onto the end of Geoff's list (in that order) as
well as this is a data access method which is growing in popularity and
might suit your development strategy, especially if you're in the web area\
or working with something like Biztalk.
Regards,
Greg Linwood
SQL Server MVP
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>
|||Just in case the other posters missed that you are developing extended proc's:
Forget about ADO.NET, as it is not supported to have SQL Server execute CLR code.
For this type of low-level programming, I have a feeling that ODBC or OLEDB is the best choice. www.sqldev.net
might have some comments about this...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ggeshev" <ggeshev@.tonegan.bg> wrote in message news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>

Obsolete technology - dblibrary

Hello!
In my application I need to use extended stored procedures.
Developing these extended stored procedures I use the "dblibrary APIs" to
access MS SQL Server 2000.
As far as I understand /reading articles in MSDN/, Microsoft declares its
intention not to support dblibrary in future.
Which is the recommended method to access MS SQL Server?
Thank you!In order, the preferences are:
ADO.Net (but you have to be writing in VB.Net or C#)
ADO
OLE-DB
ODBC
SQL-DMO for controlling the server instead of manipulating the data.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>|||I'd just tack on SQLXML/IIS onto the end of Geoff's list (in that order) as
well as this is a data access method which is growing in popularity and
might suit your development strategy, especially if you're in the web area\
or working with something like Biztalk.
Regards,
Greg Linwood
SQL Server MVP
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>|||Just in case the other posters missed that you are developing extended proc's:
Forget about ADO.NET, as it is not supported to have SQL Server execute CLR code.
For this type of low-level programming, I have a feeling that ODBC or OLEDB is the best choice. www.sqldev.net
might have some comments about this...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ggeshev" <ggeshev@.tonegan.bg> wrote in message news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>

Obsolete technology - dblibrary

Hello!
In my application I need to use extended stored procedures.
Developing these extended stored procedures I use the "dblibrary APIs" to
access MS SQL Server 2000.
As far as I understand /reading articles in MSDN/, Microsoft declares its
intention not to support dblibrary in future.
Which is the recommended method to access MS SQL Server?
Thank you!In order, the preferences are:
ADO.Net (but you have to be writing in VB.Net or C#)
ADO
OLE-DB
ODBC
SQL-DMO for controlling the server instead of manipulating the data.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>|||I'd just tack on SQLXML/IIS onto the end of Geoff's list (in that order) as
well as this is a data access method which is growing in popularity and
might suit your development strategy, especially if you're in the web area\
or working with something like Biztalk.
Regards,
Greg Linwood
SQL Server MVP
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl...
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>|||Just in case the other posters missed that you are developing extended proc'
s:
Forget about ADO.NET, as it is not supported to have SQL Server execute CLR
code.
For this type of low-level programming, I have a feeling that ODBC or OLEDB is the best
choice. www.sqldev.net
might have some comments about this...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ggeshev" <ggeshev@.tonegan.bg> wrote in message news:OdH$50COEHA.2336@.TK2MSFTNGP09.phx.gbl..
.
> Hello!
> In my application I need to use extended stored procedures.
> Developing these extended stored procedures I use the "dblibrary APIs" to
> access MS SQL Server 2000.
> As far as I understand /reading articles in MSDN/, Microsoft declares its
> intention not to support dblibrary in future.
> Which is the recommended method to access MS SQL Server?
> Thank you!
>