Get server property details into Database table.

Question

Post reply

Get server property details into Database table.

msn29

SSC Veteran

Points: 249
More actions
November 16, 2010 at 10:16 pm

#157319

Hi Expert,
I am looking forward to capture Server property info from more than 100 servers in to one of the SQL DB for reporting purpose.
I want to capture following Server property.
productversion,
productlevel,
edition,
collation,
instancename,
lcid,
servername
into one of SQL DB.
I know how to cature it within a server but any idea how can we capture that info from all other servers from one machine.
Linked server is one of the option but any other way other than linked server.
Your help is greatly appreciated.
Cheers,

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You could use OPENROWSET and provide the connection string for each server.

You would have to enable ad hoc queries though.

-- Gianluca Sartori

Adiga One Orange Chip Points: 27284 More actions · Answer 2

If you have SSMS 2008 installed, you can make use of the "Query against Multiple Instances" features. Once you have registered all the instances, you can run any query against all the instances. More details here[/url]

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

It's a nice tip for a one-shot query, though it won't help you automating the data flow to a destination table.

-- Gianluca Sartori

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

This is the kind of thing that SSIS (Integration Services) was designed for. Have you tried using it?

John

msn29 SSC Veteran Points: 249 More actions · Answer 5

Thanks Everyone for your reply and time.

I am aware about SSMS 2008 features but I am looking forward to make it automatic so that once a week the table is populated and I can query that anytime to generate report. Any idea ?

Thanks in advance.

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

msn29 (11/17/2010)
Any idea ?

You had at least two from this thread:

1) populate the table with openrowset in a loop

2) use SSIS

Does any of these fit your needs?

-- Gianluca Sartori

Sequelsurfer SSCrazy Points: 2562 More actions · Answer 7

Try a search on SQL_Overview on the SQLServerCentral forums. It's ultimately an SSIS package based originally on something created by Rodney Landrum. I have been using it for the last 12 months in SQL2008 (a few tweaks here and there) but one of the tables created contains almost all of the info you require for all my servers. It builds into a very nice monitoring tool if you follow the complete package.

barsuk SSChampion Points: 12280 More actions · Answer 8

I was using that what you are trying to do about 5 years ago on SS2000 with about 150 servers with Linked Servers, when we were pulling all Server(s) and Database(s) information in once centralized DBA Server.

Once you set it up, it worked just fine. The only caveat back then was that you should have a special handling on when the server is not available....