asked on

Script to check SQL Server Version Informaiton

I'm using the following to deploy a script to my SQL servers to check version information.

sServer = "127.0.0.1"

' Set the connection string, open the connection and get rows.
oCn.ConnectionString = "PROVIDER=SQLOLEDB.1;Integrated Security=SSPI;Server=" & sServer
oCn.open

I'm having an issue with servers that have an instance defined. For example, my Altiris SQL Server. It's NTALTSQL\Altiris. Simply using 127.0.0.1 will not work. However, if i use 127.0.0.1\Altiris it works fine. Yes, I’m running it locally on the server. J

What I'm looking for is some type of wildcard to specify that it connects to whatever instance is there, if any.

Ron Malmstead

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement10")
Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM SqlServiceAdvancedProperty where SQLServiceType = 1 AND PropertyName = 'VERSION'",,48)
For Each objItem in colItems
Wscript.Echo "-----------------------------------"
Wscript.Echo "SqlServiceAdvancedProperty instance"
Wscript.Echo "-----------------------------------"
Wscript.Echo "PropertyName: " & objItem.PropertyName
Wscript.Echo "PropertyStrValue: " & objItem.PropertyStrValue
Next

Ron Malmstead

You might also want the service name..

Wscript.Echo "ServiceName: " & objItem.ServiceName

e.g. 'MSSQL$SQLEXPRESS'

jmkbrown

ASKER

I’m still unable to connect to the instance, “.” In this case. I’ve also tried (local) & (localhost) as well.

Ron Malmstead

If you are running the script on localhost.... leave the script as is.

"." ...means localhost.

What windows server version is this ?

Troubleshoot WMI - http://msdn.microsoft.com/en-us/library/aa394603(v=VS.85).aspx

jmkbrown

ASKER

It works on SQL boxes without a named instance. But with a named instance is where they fail. So, NTALTSQL\Altiris works…but NTALTSQL does not. It needs to detect the instance that’s running and use that.

jmkbrown

ASKER

Oh, 2000 & 2003 server. 32bit.

Ron Malmstead

ok...different approach then.

Try the below vbs script.

sServer = "127.0.0.1\Altiris"
Set Server = CreateObject("ADODB.Connection")

strConnectString = "PROVIDER=SQLOLEDB.1;Integrated Security=SSPI;Server=" & sServer
SQL = "SELECT @@VERSION,SERVERPROPERTY('productversion'),SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')"

Set Rs = CreateObject("ADODB.Recordset")

Rs.Open SQL, strConnectString, 3, 3


Rs.MoveFirst

Do While Not Rs.EOF

Wscript.echo Rs(0)

Rs.MoveNext

Loop

Open in new window

jmkbrown

ASKER

The issue is with sServer = "127.0.0.1\Altiris"

Not all sql servers will have an Altiris instance

Ron Malmstead

You should just use "." if it's being ran against localhost...
It shouldn't matter because this script is literally querying sql itself.

Try the below like this from command line.

cscript.exe C:\thisscript.vbs "."
cscript.exe C:\thisscript.vbs "server1"
cscript.exe C:\thisscript.vbs "server2"
cscript.exe C:\thisscript.vbs "192.168.1.6"

The script assumes you have permissions...

set args=wscript.arguments
strcomputer = args(0)
sServer = args(0)

Set Server = CreateObject("ADODB.Connection")

strConnectString = "PROVIDER=SQLOLEDB.1;Integrated Security=SSPI;Server=" & sServer
SQL = "SELECT @@VERSION,SERVERPROPERTY('productversion'),SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')"

Set Rs = CreateObject("ADODB.Recordset")

Rs.Open SQL, strConnectString, 3, 3


Rs.MoveFirst

Do While Not Rs.EOF

Wscript.echo Rs(0)

Rs.MoveNext

Loop

Open in new window

jmkbrown

ASKER

It worked on NTAsgard, but not NTALTSQL. Asgard doesn’t have an instance, AltSQL does. That’s the only difference I can see.

Screen shots attached
NTAALSQL.bmp
NTAsgard.bmp

Ron Malmstead

The difference may be in authentication type.

For example.... NTAsgard you may have permissions if SQL is running under Windows authentication.
NTAALSQL.... may be using SQL username/pw, in which case you would need to supply cred's in the connection string.

I typically run my sql servers with both windows and sql authentication.
I use Sa logon for everything admin, and I use windows auth for all user apps.

Ron Malmstead

"Asgard doesn’t have an instance" - Meaning what ?

Sql is not installed ?
The script connects to the default instance.

If you use SQL Server Management Studio, on any of these servers... the same authentication type you use on a particular server, is the same implementation that would be needed in the script.

See screen capture...

xuserx2000-448816.flv

ASKER CERTIFIED SOLUTION

JonMny

membership

This solution is only available to members.

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

Start Free Trial