T-SQL System Info

I have multiple servers on the network (10.0.0.10; 10.0.0.11; 10.0.0.12; etc...) and I would like to add the following information to a database record (per server) running a stored procedure from SQL Server 2005 located on 10.0.0.10:

Server Name
IP Address
Total Disk Capacity
Free Disk Space
Directory Names and Number of Files and Disk Usage per directory
conrad2010Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris MConsulting - Technology ServicesCommented:
Hi,
You could write a stored procedure which executes commands at the OS command line using xp_cmdshell to retrieve these details.

There are however limitations with just using a procedure to do all these tasks.
You can get the host name using ping command and reading the results e.g:
PING -n 1 -a 10.0.0.11

So passing it in a procedure would need writing SQL like this:
EXECUTE master..xp_cmdshell 'ping -n 1 -a 10.0.0.11';

So getting server name and IP address can be done locally from your database server but tasks like getting details like Total Disk Capacity, Free Disk Space, Directory Names and Number of Files and Disk Usage per directory definitely will require running commands locally on the different servers/hosts whose details you're trying to get. This means that a procedure will therefore not achieve this.

It's best that you write an application for these tasks. You could think of writing an application in any of your favorite programming language e.g dot Net (C#, VB etc) or Java which can be do all these tasks for you and then write details into SQL server by connecting to your database.

All the best,
Chris Musasizi

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.