Listing SQL Server services

Question

Listing SQL Server services

SQLBuddy123

One Orange Chip

Points: 28294
More actions
December 1, 2009 at 3:23 pm

#206796

Can anyone help me in finding a script that can do the following?
I have a group of 100 windows servers.
1) Now, I want to list the SQL Server services (at least one main SQL Server service) that are running on each Widnows server one by one.
2) If there is no SQL Server instance, it should give an informational message that “SQL is absent on the <Server Name>”
3) If connection cannot be established to the server, it should give a message “Couldn’t connect to the server <Server Name>”
Here <Server Name> is the name of the Windows server in the servers list or in the loop.
Is this possible with a script?
If the script could at least a couple of things in the above listed 1-3 things that would be great.
Thank You,
Best Regards,
SQLBuddy.

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

AFAIK, this is very hard to do with TSQL alone. It's much easier to do from another facility (DOS, Powershell, SMO w VB or C#, etc...) and then bring the data into SQL Server.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

This is a much simpler task in some other medium than TSQL as Barry suggested. From within tsql, you would end up making calls to another medium to accomplish your tasks.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Hi Barry and Jason,

Thank you very much for your reply.

Do you know any script like VBScript or so to acomplish this task?

Could you please help me with this?

Thank You,

Best Regards,

SQLBuddy.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

http://www.freevbcode.com/ShowCode.asp?ID=5081

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

Hi Jason,

Thank you very much for your reply. I really appreciate your help.

The script is asking for the manual entry of a Servername using a text box.

Is there any way in which we can loop though a list of servers i.e for each iteration of the loop, a distinct server should be picked from the list of servers and then logic has to be executed for that server and so on.

Also instead of getting the output in the Command window, can we export the results to a file.

Thank You,

Best Regards,

SQLBuddy

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

sqlbuddy123 (12/1/2009)
Also instead of getting the output in the Command window, can we export the results to a file.

Sometimes, the "old ways" are the easiest...

[font="Arial Black"]OSQL.exe -L >C:\Temp\MyServers.txt[/font]

... and be patient. There will be no sign on screen of it working until it is done where it will simply return to the command prompt. Of course, it will not find any servers/instances outside the current domain nor will it find any servers that are offline or have communications problems. It also won't tell you things like which version of SQL Server is running on the instance. Also keep in mind that those are SQL Server instance names... not computer names.

Once you have that file, you can BULK INSERT it and make/break linked servers to check out the individual remote machines. Of course, you'll probably need logins and passwords unless trusted connections are allowed.

--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)

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 7

Hi Jeff,

Thank you very much for your reply.

But still I didn't find the true solution.

Best Regards,

SQLBuddy.

Fozzie SSCrazy Points: 2222 More actions · Answer 8

I am not saying it's the answer to all your questions, but there is a free tool call SQL Ping available from http://www.sqlsecurity.com. Comes in GUI and command line form.

You could schedule a job to run the command line which wuld ouput to a file then import the results? There was an article on here about it http://www.sqlservercentral.com/articles/64016/.

The other way maybe to create some sort of CLR package that enables you to query the WMI providers... think that's how sql ping works.

It also detects blank sa passwords, which is handy.