Truncating VersionString info returned from SQLDMO.SQLServer

I'm trying to pull SQL Server version and edition information as a part of an inventory script I'm working on.  The problem is that when using the VersionString function I get a lot of data in return and want to truncate it down.  All I really want from the data is "Microsoft SQL Server 2005" (or whatever version) from the top line and "Standard Edition" (or whatever Edition) from the bottom line.

So when I run this:

<script>
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set SQLFile = objFSO.OpenTextFile("SQL Version.txt", 8, True)

strDBServerName = "ServerName"
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True
objSQLServer.Connect strDBServerName
strVersionInfo = objSQLServer.VersionString

SQLFile.WriteLine(strVersionInfo)
SQLFile.WriteLine
SQLFile.Close
</script>

It will write all this to the file:

<result>
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
      Mar 23 2007 16:28:52
      Copyright (c) 1988-2005 Microsoft Corporation
      Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
</result>

Again, all I really want from this data is "Microsoft SQL Server 2005" from the top line and "Standard Edition" from the bottom line.  To achieve this, I've tried using Left and Mid functions on the string of data before I write it to the file.

<script>
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set SQLFile = objFSO.OpenTextFile("SQL Version.txt", 8, True)

strDBServerName = "ServerName"
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True
objSQLServer.Connect strDBServerName
strVersionInfo = objSQLServer.VersionString

'This copies the first 26 characters into a string
strVersion = Left(strVersionInfo, 26)

'Usually the 126th character is the start of the edition information so I grab 10 characters of that
strEdition = Mid(strVersionInfo, 126, 10)

'I then combine the two together
strSQLVer = strVersion & strEdition

'And use a ugly set of If...Then...ElseIf... functions to output the desired text.
If strSQLVer = "Microsoft SQL Server 2005 Standard E" Then
      strSQLClean = "Microsoft SQL Server 2005 Standard Edition"

ElseIf strSQLVer = "Microsoft SQL Server 2005 Enterprise" Then
      strSQLClean = "Microsoft SQL Server 2005 Enterprise Edition"

ElseIf strSQLVer = "Microsoft SQL Server 2005 Express Ed" Then
      strSQLClean = "Microsoft SQL Server 2005 Express Edition"

ElseIf strSQLVer = "Microsoft SQL Server  2000sktop Engi" Then
      strSQLClean = "Microsoft SQL Server 2000 Desktop Engine (MSDE)"

ElseIf strSQLVer = "Microsoft SQL Server  2000andard Edi" Then
      strSQLClean = "Microsoft SQL Server 2000 Standard Edition"

Else strSQLClean = "SQL Edition Unknown"
End If

SQLFile.WriteLine(strSQLVer)
SQLFile.WriteLine
SQLFile.Close
</script>

Technically it works, but I know it can be done better using VBScript Regular Expressions (RegExp).  Unfortunately I'm kind of new to a lot of this and keep banging my head on a wall when trying to get the RegExp function to work properly.

Looking back at the original output of VersionString, I can see two things that can be used for the RegExp Pattern:

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
      Mar 23 2007 16:28:52
      Copyright (c) 1988-2005 Microsoft Corporation
      Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

On the top line, the " - " between 2005 and 9.00.3054.00 can be used to say "get the part of the string before the " - ".  Then on the last line the " on Windows" can be used to get the Edition.  But again, I'm not familiar enough with how to write out a RegExp function to get it done.

Can anyone give me an example of how to use RegExp to crop this string down?
LVL 2
JenH2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobSampsonCommented:
Hi there. Perhaps this would work.  I've taken the assumption that strVersionInfo includes the end of line characters vbCrLf.  This should allow us to split that string by the new line characters, and then we can parse each line, looking for what we need.
So then we loop through those lines, and look for "SQL Server", and if found, only get the amount of text up to the first "-" character.
Then we also look for "on Windows", and if found, only get the amount of text up to that string.

See how this goes.

Regards,

Rob.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set SQLFile = objFSO.OpenTextFile("SQL Version.txt", 8, True)
 
strDBServerName = "ServerName"
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True
objSQLServer.Connect strDBServerName
strVersionInfo = objSQLServer.VersionString
 
For Each strLine In Split(strVersionInfo, VbCrLf)
	If InStr(strLine, "SQL Server") > 0 Then
		strSQLVer = Trim(Left(strLine, InStr(strLine, "-") - 1))
	ElseIf InStr(strLine, "on Windows") > 0 Then
		strSQLEd = Trim(Left(strLine, InStr(strLine, "on Windows") - 1))
	End If
Next
 
SQLFile.WriteLine(strSQLVer & " " & strSQLEd)
SQLFile.WriteLine
SQLFile.Close

Open in new window

0
JenH2Author Commented:
Unfortunately no, I didn't use any vbCrLf to add line feeds.  The string that objSQLServer.VersionString returns is unlike any string I've ever seen (not saying much, I've only been scripting for about a year).  The line feeds are built into the string.  When I used the Mid function <strEdition = Mid(strVersionInfo, 126, 10)> all I did was add the characters of the first three rows, then added 3 more characters for the tabbed spacing of the last three rows.

I did just test the script you posted and it only writes "Microsoft SQL Server 2005", but not the "Standard Edition".

Thats why I feel like I have to use RegExp to get the data out of the string, I just don't know how.
0
RobSampsonCommented:
Hmmm, I don't know about that....I think as long as we can find out what actually separates the lines, the above should work.  Just to test, see what happens if you change this line:
For Each strLine In Split(strVersionInfo, VbCrLf)

to this
For Each strLine In Split(strVersionInfo, VbCr)

or this
For Each strLine In Split(strVersionInfo, VbLf)

Regards,

Rob.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

JenH2Author Commented:
For Each strLine In Split(strVersionInfo, VbLf) worked!  Thank you so much.  I've never used a Split function before, but I think I see how it works.  I'm guessing the two parameters are the data string you want to split up, then the second is the character to use in spiting up the the data.  That could be useful when dealing with CSV files.

So here is the final code used to truncate the data:
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set SQLFile = objFSO.OpenTextFile("SQL Version.txt", 8, True)
 
strDBServerName = "ServerName"
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True
objSQLServer.Connect strDBServerName
strVersionInfo = objSQLServer.VersionString
 
For Each strLine In Split(strVersionInfo, VbLf)
        If InStr(strLine, "SQL Server") > 0 Then
                strSQLVer = Trim(Left(strLine, InStr(strLine, " -") - 1))
        ElseIf InStr(strLine, "on Windows") > 0 Then
                strSQLEd = Trim(Left(strLine, InStr(strLine, "on Windows") - 1))
        End If
Next
 
SQLFile.WriteLine(strSQLVer & "-" & strSQLEd)
SQLFile.WriteLine
SQLFile.Close

Open in new window

0
JenH2Author Commented:
Thank you for the quick solution, it turned out to be much easier than I was thinking it would be.  :)
0
RobSampsonCommented:
>> I'm guessing the two parameters are the data string you want to split up, then the second is the character to use in spiting up the the data.

Hi, that's right.  If you did have a CSV file, you'd go through each line:
strLine = objCSVFile.ReadLine

then you can "split" that line into an array using the Split function. The second parameter is the delimiter, so if the CSV has commas:
arrFields = Split(strLine, ",")

or if it has tabs (which would be a TSV):
arrFields = Split(strLine, vbTab)

This makes arrFields an array, so you can just loop through the array to get the fields.

For Each strField in arrFields
   WScript.Echo strField
Next

Or, another way is to reference each field by number (note that the array starts at element zero, and UBound returns the maximum element number, the amount of fields minus one):

For intField = 0 To UBound(arrFields)
  WScript.Echo "Field number " & intField + 1 & " is " & arrFields(intField)
Next

Note I have output intField + 1 above just for the sake of human readability.  Say there were 10 fields, the array would be referenced with arrFields(0) through to arrFields(9), but when you output it, I like it to say
Field 1 is arrFields(0)

It's like Excel starting at row 1, instead of row zero.

Hope that helps. Oh, thanks for the grade by the way.

Regards,

Rob.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.