asked on

VBScript code to list all SQL 2000/2005 instances given a server name.

Hi,

I am looking for some VBScript code that will do the following:

1. Script will run localy on a SQL 2000/2005 server.
2. It will return all SQL server instance names of the server it is running on.
3. It will ideally use SQLDMO.

You can assume the script will be launched with the right permissions local adminisstrative rights etc.

Any ideas?

Thanks,

Ward.

RobSampson

Hi, I can't test this, but I found some code here:
http://rasikjain.blogspot.com/2005/03/check-database-size.html

Something like this may work:

'===============
vServerName = "SQLServerName"
Set objServer = CreateObject("SQLDMO.SQLServer2")
'Set Authentication for Server
' Login with current Windows account
objServer.LoginSecure = True
objServer.Connect vServerName
For each objDatabase in objServer.Databases
strDBs = strDBs & vbCrLf & objDatabase.Name
Next
MsgBox "Databases on " & vServerName & ":" & vbCrLf & strDBs
'===============

Regards,

Rob.

whorsfall

ASKER

Hi,

I tried this helpfull however you still need to know the instance name to connect to. So as posted there it will only connect to the default instance and evaluate that.

Thanks for your help,

Ward

David Todd

Hi,

I've used the free version of SQL Security's SQL Recon or their SQL Ping.

Any particular reason why you want to do this yourself from VB?

Regards
David

whorsfall

ASKER

Hi,

Thanks for your response. Actually I am doing this because I am creating an automated solution that requires scripting. If I have to drive a UI then it will not be of any use to me. So I need it to run and then I can evaluate the state of every database on every instance i find.

Thanks,

Ward

David Todd

Hi Ward,

Just a question: Will anyone install SQL and not tell you? Many products these days are starting to use MSDE and Compact editions as their data-store - Backup Exec is one that comes to mind.

If you do know about all the instances you care about, then you can store this and concentrate on the databases.

Looking at the work gone into SQL Ping and similar, there are a multitude of ways to hide SQL, or at any rate, make it slightly less obvious.

I believe that some of the products I mentioned above have scripting interfaces, or command-line versions.

Cheers
David

ASKER CERTIFIED SOLUTION

RobSampson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carlos_Felipe

Try this:

ServerName = "localhost"
Set objSQLDMOApp = CreateObject("SQLDMO.SQLServer2")
set objInstances = objSQLDMOApp.ListInstalledInstances(ServerName)
For count=1 To objInstances.Count
	WScript.Echo objInstances.Item(count)
Next

Open in new window