[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 825
  • Last Modified:

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.
0
jmkbrown
Asked:
jmkbrown
  • 7
  • 5
1 Solution
 
Ron MalmsteadInformation Services ManagerCommented:
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
0
 
Ron MalmsteadInformation Services ManagerCommented:
You might also want the service name..

Wscript.Echo "ServiceName: " & objItem.ServiceName

e.g.   'MSSQL$SQLEXPRESS'
0
 
jmkbrownAuthor Commented:
I’m still unable to connect to the instance, “.” In this case. I’ve also tried (local) & (localhost) as well.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Ron MalmsteadInformation Services ManagerCommented:
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
0
 
jmkbrownAuthor Commented:
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.
0
 
jmkbrownAuthor Commented:
Oh, 2000 & 2003 server. 32bit.
0
 
Ron MalmsteadInformation Services ManagerCommented:
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

0
 
jmkbrownAuthor Commented:
The issue is with  sServer = "127.0.0.1\Altiris"

Not all sql servers will have an Altiris instance

0
 
Ron MalmsteadInformation Services ManagerCommented:
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

0
 
jmkbrownAuthor Commented:
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
0
 
Ron MalmsteadInformation Services ManagerCommented:
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.
0
 
Ron MalmsteadInformation Services ManagerCommented:
"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
0
 
JonMnyCommented:
this worked on all of my servers some with named others without all have multiple.

I used the services to get the server names so should work on all server types 2000 and 2003
strComputer = "."

Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colRunningServices = objWMIService.ExecQuery("Select * from Win32_Service")
For Each objService in colRunningServices 
   If InStr(objService.DisplayName,"SQL Server")>0 And InStr(LCase(objService.pathname),"sqlservr.exe")>0  Then
   sServer=".\" & Replace(objService.Name,"MSSQL$","")
    if sServer=".\MSSQLSERVER" then sServer="."
    Set Rs = CreateObject("ADODB.Recordset")
    strConnectString = "PROVIDER=SQLOLEDB.1;Integrated Security=SSPI;Server=" & sServer
msgbox strConnectString
     SQL = "SELECT @@VERSION,SERVERPROPERTY('productversion'),SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')"  
    Rs.Open SQL, strConnectString, 3, 3
    Rs.MoveFirst
 Do While Not Rs.EOF
 Wscript.echo Rs(0)
 Rs.MoveNext
 Loop 
 
End If
next
 
 
msgbox "finished"

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now