This Vbs script will write the following SMS client information to a Microsoft Excel spreadsheet:
Name, Client, Client Type, Client Version, IP Address, Last Logon User Domain
Last Logon User Name, MAC Addresses, Net bios Name, Operating System Name and Version, Previous SMSUUID, Resource Domain or Workgroup, SMS Assigned Sites,
SMS Installed Sites and SMS Unique Identifier.
The machine information will be collected from a text file called MachineList.Txt. You can also modify the script to prompt you for an individual machine name using the example found at the end of this post.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
intRow = 2
objExcel.Cells(1, 1).Value = "Name"
objExcel.Cells(1, 2).Value = "Client"
objExcel.Cells(1, 3).Value = "Client Type"
objExcel.Cells(1, 4).Value = "Client Version"
objExcel.Cells(1, 5).Value = "IP Address"
objExcel.Cells(1, 6).Value = "Last Logon User Domain"
objExcel.Cells(1, 7).Value = "Last Logon User Name"
objExcel.Cells(1, 8).Value = "MAC Addresses"
objExcel.Cells(1, 9).Value = "Net bios Name"
objExcel.Cells(1, 10).Value = "Operating System Name And Version"
objExcel.Cells(1, 11).Value = "Previous SMSUUID"
objExcel.Cells(1, 12).Value = "Resource Domain Or Workgroup"
objExcel.Cells(1, 13).Value = "SMS Assigned Sites"
objExcel.Cells(1, 14).Value = "SMS Installed Sites"
objExcel.Cells(1, 15).Value = "SMS Unique Identifier"
On Error Resume Next
Set Fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fso.OpenTextFile("MachineList.Txt")
Do While Not (InputFile.atEndOfStream)
strComputer = InputFile.ReadLine
Set objWMIService = GetObject("winmgmts://" & strComputer & "\root\sms\site_xxx")
' Change xxx to your site code
Set colItems = objWMIService.ExecQuery("Select * from SMS_R_System",,48)
For Each objName in colItems
objExcel.Cells(intRow, 1).Value = objName.Name
objExcel.Cells(intRow, 2).Value = objName.Client
objExcel.Cells(intRow, 3).Value = objName.ClientType
objExcel.Cells(intRow, 4).Value = objName.ClientVersion
objExcel.Cells(intRow, 5).Value = objName.IPAddresses
objExcel.Cells(intRow, 6).Value = objName.LastLogonUserDomain
objExcel.Cells(intRow, 7).Value = objName.LastLogonUserName
objExcel.Cells(intRow, 8).Value = objName.MACAddresses
objExcel.Cells(intRow, 9).Value = objName.NetbiosName
objExcel.Cells(intRow, 10).Value = objName.OperatingSystemNameandVersion
objExcel.Cells(intRow, 11).Value = objName.PreviousSMSUUID
objExcel.Cells(intRow, 12).Value = objName.ResourceDomainORWorkgroup
objExcel.Cells(intRow, 13).Value = objName.SMSAssignedSites
objExcel.Cells(intRow, 14).Value = objName.SMSInstalledSites
objExcel.Cells(intRow, 15).Value = objName.SMSUniqueIdentifier
intRow = intRow + 1
Set colItems = Nothing
Set objWMIService = Nothing
objExcel.Selection.Interior.ColorIndex = 19
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True
Collecting SMS client information for an individual prompted machine name:
To prompt for an individual machine name comment out the following in the script above as shown in the example:
'On Error Resume Next
'Set Fso = CreateObject("Scripting.FileSystemObject")
'Set InputFile = fso.OpenTextFile("MachineList.Txt")
'Do While Not (InputFile.atEndOfStream)
Then comment out the line that reads:
'strComputer = InputFile.ReadLine
as shown in the example above and replace it with this:
strComputer = InputBox("Enter Machine Name To Query")
Then comment the Loop statement at the very end of the script.
When you are upgrading an existing SMS 2.0 installation to SMS 2003 you may find that there are machines that are installed and assigned to different sites. To a lesser degree you may find that some of your older clients may be installed or assigned to an old site database.
In order to monitor your upgrade process it is necessary to find which machines are stuck in between sites so that you can accurately report on your progress. The SQL query below will allow you to quickly find those machines that are being processed or are having issues with the upgrade. It will also allow you to find any machines that are left in an orphaned state long after your SMS upgrade or SMS server replacement or rename.
Discovery.Netbios_Name0 'Machine Name',
Assinged.Sms_Assigned_Sites0 'Assigned Site',
Installed.Sms_Installed_Sites0 'Installed Site'
From System_Sms_Assign_Arr Assinged
Join System_Disc Discovery on Assinged.ItemKey = Discovery.ItemKey
Join System_Sms_Instal_Arr Installed on Assinged.ItemKey = Installed.ItemKey
Not Like Installed.Sms_Installed_Sites0
When configuring SMS 2003 to run in an environment which utilizes a firewall or routers it is necessary to know what ports you should allow in order for your SMS infrastructure to function properly. Here you will find ports used by SMS as well as Microsoft SQL Server, Active Directory Discovery and Microsoft Windows NT.
Site Server to child and secondary sites as well as SMS SQL Server.
445 - Server Message Block (SMB)
389 - Lightweight Directory Access Protocol (LDAP)
636 - LDAP (Secure Sockets Layer (SSL) connection)
Proxy Management point to parent SQL Server
1433 - TCP (SMS Site Server to SQL Server)
389 - LDAP
636 - LDAP (Secure Sockets Layer (SSL) connection)
Advanced Client to Management Point and Distribution Point
80 - Hypertext Transfer Protocol (HTTP)
139 - Client sessions (for non BITS-enabled DPs)
445 - Server Message Block (for non BITS-enabled DPs)
389 - UDP (User Datagram Protocol) LDAP Ping
389 - TCP LDAP
636 - TCP LDAP (Secure Sockets Layer (SSL) connection)
3268 - TCP (Explicit connection to Global Catalog)
Remote Control System service (Wuser32)
2703 - TCP SMS Remote Chat
2703 - UDP SMS Remote Chat
2701 - TCP SMS Remote Control (Control)
2701 - UDP SMS Remote Control (Control)
2702 - TCP SMS Remote Control (Data)
2702 - UDP SMS Remote Control (Data)
2704 - TCP SMS Remote File Transfer
2704 - UDP SMS Remote File Transfer
Remote Control UDP *
137 - Name resolution
138 - Messaging
139 - Client sessions
* Only applies if you use NetBIOS over TCP/IP for SMS Remote Control
Microsoft SQL Server
1433 - TCP SQL server
139 - TCP Named pipes
Active Directory Discovery methods
389 - TCP LDAP
389 - UDP LDAP
636 - TCP LDAP (Secure Sockets Layer (SSL) connection)
135 - TCP RPC Endpoint Mapper
135 - UDP RPC Endpoint Mapper
3268 - TCP Global Catalog LDAP
3269 - TCP Global Catalog LDAP (Secure Sockets Layer (SSL) connection)
88 - TCP Kerberos
88 - UDP Kerberos
Microsoft Windows NT UDP
53 – UDP Domain Name System (DNS)
67 – UDP Dynamic Host Configuration Protocol (DHCP)
135 – TCP Remote procedure call (RPC)
138 – UDP Windows Internet Name Service (WINS)
138 – UDP NetBIOS Datagram Service Computer Browser
139 – TCP NetBIOS Datagram Service Messenger
Check out these NStorm Games and download your favorites including the
Ever popular Elf Bowling games that were once thought of as a Virus files and was even reported to the Symantec Security Response center
I was reading a Hey, Scripting Guy! Article on “How Can I Insert a Column into a Spreadsheet” some time ago and discovered how to send Visual Basic Scripts (Vbs) output to a Microsoft Excel spreadsheet. After reading the article I decided to adapt some of my Vbs scripts to use excel as the output rather than sending the results to a text file or dumping it to the screen. After learning more on the subject I decided that this was the way to go for most of the scripts I have written over time.
The task of converting them is sometimes daunting and time consuming but the effort is not wasted because the results set is nice and tidy. Here is a link to the article mentioned above if you are interested in some basic syntax:
For example if you have this very basic Vbs script:
And you want to modify it to send its results output back to a Microsoft Excel spreadsheet rather than sending the results to the Windows scripting host dialog box or even a text file you would add the following before the original script shown above:
' Create The Excel Application Object.
' Make Excel Visible Through The Application Object.
' Set The Application Object To Allow Data To Be Added.
' Sets Row 2 Of The Spreadsheet For The Data Because Row 1 Contains The Header Info.
' Sets The Column Name As "Machine Name" To Cell A1.
objExcel.Cells(1, 1).Value = "Machine Name"
Then you would add this after your original script above:
' Data Is Entered By The Query To Cell A2 (intRow + 1).
objExcel.Cells(intRow, 1).Value = strComputer
' Adds Data To The Appropriate Cell A1 Row 2.
Now the final script would look like this:
' Your Existing Vbs Script Goes Here.
Note: I removed my explanatory comments from the final example shown above. You can however add them back for future reference.
You can execute the code above as it is but since we are replacing the screen echo with an Excel Object we can remove the line that reads “Wscript.Echo strComputer” completely from our script.
As a result of my newly found ‘technique’ I will be posting some of my scripts as time goes by using Microsoft Excel as the output.
There have been many queries posted over the years that allow you to find machines in your SMS database that have not reported hardware or software inventory data in a timely manner. Here you will find a slight twist on the queries that have been published or posted in the past by myself and others.
The SQL query below will search the SMS database for machines that have not uploaded their Hardware ‘OR’ Software inventory in the last week or seven days. It is basically combining the individual Hardware inventory reporting and Software inventory reporting queries I have posted on myITforum over the years. I like to think of it as killing two birds with one stone as the old saying goes.
The key in writing such queries is using the DateDiff and GetDate() SQL functions. The GetDate() function retrieves the current system date and time as reported by the SQL Server being used. The DateDiff function returns the difference between two date or named values of a specified interval.
In the query below we are asking the SQL query parser to calculate the time interval argument based on a specified number of days (D) as qualified by the 7 (Days) in our query below. We are also directing it to use the LastHwScan and LastUpdateDate tables timestamps as the dates (Named Values rather than a specified date such as 11/07/2005) to calculate.
The query performs a brief mathematical calculation of the current day based on the day values read or enumerated in the LastHWScan and the LastUpdateDate table timestamps for the machines that are returned in the query results set. In other words it is directing the parser to return to us all the machines where the WorkstationStatus_Data LastHwScan and the SoftwareInventoryStatus LastUpdateDate timestamps are greater than (>) or more than the current day in which the query is executed.
If you want to modify the query to find for example all the machines that have last reported in 7 or more days you would change the GetDate() value to >= 7 which designates greater than or equal to 7 days as in the example below:
Where DateDiff (D, WD.LastHwScan, GetDate()) >= 7
SD.Name0 'Machine Name',
WD.LastHwScan 'Last Hardware Scan',
SIS.LastUpdateDate 'Last Software Scan'
From System_Disc SD
Join WorkstationStatus_Data WD on SD.ItemKey = WD.MachineID
Join SoftwareInventoryStatus SIS on SD.ItemKey = SIS.ClientId
Where DateDiff (D, WD.LastHwScan, GetDate()) > 7
Or DateDiff (D, SIS.LastUpdateDate, GetDate()) > 7
If you are currently running either Microsoft SQL server 7.0 Service Pack 4 or later or Microsoft SQL sever 2000 Service Pack 3 or later and your SMS server(s) have been upgraded to SMS 2003 Service Pack 2 you can upgrade your SMS 2003 infrastructure to use Microsoft SQL server 2005 (SQL 2K5) as its database backend.
The only prerequisites from a SQL server aspect provided by Microsoft is that you have SQL server 7.0 SP 4 or SQL server 2000 SP3 or greater installed and SMS 2003 Service Pack 2 has been installed on your site server(s). Not until SP 2 for SMS 2003 was released were you able to use SQL 2K5 as your backend database server.
If you are installing your SMS infrastructure from scratch you may use SQL 2K5 as your database backend as long as you perform the following upgrade path:
1. Install Microsoft SQL Server 2000 and apply SP 3a or SP 4.
2. Install SMS 2003 SP 1
3. Apply SMS 2003 SP 2
4. Upgrade SQL Server 2000 to SQL Server 2005
5. Apply SQL Server 2005 SP 1 (Optional)
Note: The following are disabled by default when you install or upgrade to SQL 2K5 but do not directly affect SMS 2003 as it is installed out of the box:
If you need to compare and sort multiple plain text based files to remove duplicate lines such as a list of workstations or user names Ultra-Edit can assist you. Basically any file that you can open (Or copy and paste into the Ultra-Edit window preferably in a readable format) can be sorted and have duplicate lines stripped out or removed.
You can however create a batch file using the MS-DOS command FC.exe (File Compare) or a script file in UNIX, Perl or Vbs to remove duplicated lines however as I have learned in the past can be quite time consuming. If you have a copy of Ultra-Edit why bother with writing complicated scripts when you can use its built in function that is so easy to use.
Note: You can also create a Macro in Ultra-Edit to remove duplicate lines or sort files alphabetically or a combination of both.
To use Ultra-Edit to remove duplicate lines and sort the results alphabetically follow the instructions below:
Note: I have included an unsorted sample file (Sample List) at the end of this post that you can use to test this functionality where the line Machine9855 appears three times and Machine9865 appears twice which you can copy into the Edit1 window as a test.
1. Start the UltraEdit application.
(Program Files\IDM Computer Solutions\UltraEdit-32\Uedit32.Exe)
2. Create a new file (Ctrl+N) if you are not presented with a new Edit1 file window.
If you only have one file to sort and remove duplicate lines from simply open the file in UltraEdit and skip to step 4.
3. Copy and paste the contents of your text file(s) into the Edit1 window or use the “Sample List” below.
4. Select ‘File’ and then ‘Sort’.
5. From the context menu select ‘Advanced Sort/Options…’
6. In the ‘Sort Order’ leaf select either ‘Ascending’ or ‘Descending’ to suit your needs.
7. Place a check in the box ‘Remove Duplicates’ as well as the ‘Numeric Sort’ if you want the file to then be sorted using the ‘Sort Order’ that you have selected in the previous step.
8. Then select ‘Sort’ to start the process.
9. Save the file or copy and paste the File1 window as needed into an existing file.
Ultra-Edit (Shareware) can be downloaded from their home page below:
Get your daily dose of Dilbert at Dilbert.com - The Official Dilbert Website by Scott Adams - Dilbert, Dogbert and Coworkers! and read Scott Adams Dilbert Blog.
You can also view the comic strip of the day, shop the Dilbert store and read the archived DNRC (Dogbert's New Ruling Class ) newsletters or sign up to get them delivered directly to your In-Box.
Also on the page you can find the Comic archive, E-Cards, Games, News And History, The Characters and Desktop Diversions
The following Visual Basic Script will delete all of the files older than one week or seven days from your SMS or SQL backup directory.
With the exponential growth of today’s SMS databases they can consume massive amounts of valuable disk space on your SMS infrastructure servers. If you have your site maintenance task ‘Backup SMS site server’ enabled and are performing several backups a week over a period of time the directory structure can grow quite large.
The same can be said of your SQL database backups as well and the script below can be modified to delete these files using the specified criteria by changing the ‘Set Directory’ to the location of your Microsoft SQL Server’s \MsSql\Backup directory.
It can also be modified to delete files from specific Inboxes as well. Because I have written the script to be as simple as possible there is no built in logic for file specifications and therefore it will delete files in the root folder indiscriminately.
To change the root directory to another folder other than "C:\SMSBackup" change C:\SMSBackup to the directory you want to purge. You can also modify the script to purge files older than the default specified using a different time stamp by changing the Day to another value rather than 7.
Note: Save the file as PurgeOldSmsBackups.Vbs or another name of your choice but be sure to save it with the .Vbs file extension.
Set Directory = Fso.GetFolder("C:\SMSBackup")
Set Files = Directory.Files
For Each Modified in Files
If DateDiff("D", Modified.DateLastModified, Now) > 7 Then Modified.Delete
MsgBox "Completed Processing Files From: " & Directory & ""
To delete an individual machine from the SMS database using the SMS console it is necessary to create a collection using the direct membership rule wizard or base your new collection on a newly created Query. The first method is slow and can be time consuming, the second option just doubles your work and it the least desired method for most people.
The other automatic means in which you can remove or otherwise delete the machine(s) from the SMS database is to let the SMS_SQL_MONITOR service automatically remove the machine after it has reached its predefined Site maintenance task ‘Delete Aged Discovery Data’ and ‘Delete Aged Inventory History ‘ specifications if you have left it enabled. It is enabled by default and is set to 90 days. You can however change this to a more reasonable time period appropriate for your sites hierarchy if needed.
Below you will find a SQL query that will allow you to delete machines from the SMS database as the task “Delete Special” performs the removal process. It is important to note here that when you delete machines from a collection within the SMS console the machine is deleted from the database however the History table is not purged or removed. When you use the task Delete Special the machine is not only removed from the SMS database but the history for the machine is also deleted.
NOTE: You must uninstall the SMS client software to ensure that it does not report back in. If you are simply removing the machine name(s) from the SMS database because your site support staff has informed you that the machines were retired or re-imaged then this is not necessary. It is important to also note here that this is not a Microsoft supported means for deleting machines from the SMS database and should be used in a non production environment.
When you are done use the query in my earlier post entitled: ‘Searching Your SQL Database For A Specified Column String’ and search for the machine(s) that you just deleted to ensure that they have in fact been purged.
To delete multiple machines using the query that follows you can simply change the line that reads: Where Name0 = 'Machine_Name' To the following: Where Name0 in ('Machine_One', 'Machine_Two') as in the Deleting Multiple Machines SQL Query found at the end of this post.
Insert DeletedMachines (SmsId)
Where Name0 = 'Machine_Name'
And Sms_Unique_Identifier0 is not null
Delete System_Disc from System_Disc
Where Name0 = 'Machine_Name'
Delete System_Data from System_Data
Where Name0 in ('Machine_One', 'Machine_Two')
When installing or enabling most of your SMS components it is necessary leave them alone for a time after you initiate them or let them sit for a while. This is necessary to allow them to fully install and configure themselves to be set up for business before opening the doors to the world.
It seems like every thing in the SMS world is governed by a specific hour time slot. For example SMS clients Client Configuration Installation Manager (CCIM) cycle executes every 25 hours for SMS 2003 Legacy Clients and it executes every 23 hours for older SMS 2.0 clients.
The same can be said for those times when you make changes to your Sms_Def.Mof file because it depends on the CCIM poling cycle interval which as we have mentioned is 23 hours. Many times I have seen people posting to the various newsgroups that they made changes to the Mof file “A little while ago’ and the SQL database has not been updated with the new inventory and all I can say is ‘Let it Fester’.
Hardware and software inventory are quiet similar as well in that it takes approximately 30 minutes after your clients receive the instructions to begin collecting software inventory before they begin the process. Likewise it takes approximately 15 minutes for the client to begin processing their hardware inventory after they receive the instruction to do so.
Most people especially American baby boomers know who Fester is. He is that lovable Uncle Fester played by Jackie Coogan (1914-1984) in the black and white Television series The Addams Family that ran from September 1964 to April 1966.
Jackie Coogan first appeared briefly in the Charlie Chaplin silent movie A Day's Pleasure (1919) then he was given a starring role along side Chaplin in his feature The Kid (1921). At any given time in the world today because the Television show was syndicated you can see the whole Addams Family in action.
When it comes to SMS infrastructure the word Fester means to let it set and boil or brew and grow. The dictionary defines the word Fester as: To be inflamed; to grow virulent, or malignant; to grow in intensity; to rankle. You can see the similarities here and that is what should capitalize on.
The Fester factor is a very broad term as applied to SMS infrastructure and you should always keep the term in the back of your head and whenever possible let things Fester when working with SMS components. Take your time and don’t get in a hurry. We all remember the old the humorous Acronym for SMS - ‘Slow Moving Software’ now lets add the Fester factor to that very distinguished group of things to always remember.
If you are like me and have to communicate with people across the United States (or anywhere throughout the world for that matter) in different time zones it is sometimes necessary to know the current time in their particular time zone.
My biggest problem is that I get meeting notices that reference time zones such as 13:00 EST and I have to calculate the time difference between EST and CST which is 1 hour ahead of my time zone. That was before Microsoft released their Microsoft Time Zone Utility in October of 2004.
The Microsoft Time Zone utility runs in your system tray and with a simple right mouse click allows you to easily view the time and date for various time zones around the world.
The Microsoft Time Zone Utility states:
“You can also quickly and easily add your own personal locations to customize Microsoft Time Zone the way you want.”
This is true to a certain extent in that you can add or remove only the values contained in the DefaultLocations.Xml file when you select the ‘Options’ tab. You can modify the file directly but it is easier and safer to modify the MyLocations.Xml because it is smaller in size and you will not run the risk of damaging the original Xml source file.
I downloaded the utility soon after it became available and added key cities in the four different time zones across the continental United States to personalize it. This was sufficient for my purposes however I wanted to modify it to show time zones along with their associated acronyms rather than cities in known time zones. This just made more sense to me especially since I could not find the actual cites in the list provided that I communicate with regularly.
As a result I set out to find a way to modify ‘My Locations’ as provided by the utility to suit my particular needs. The Xml file associated with ‘Show My Locations’ is the MyLocations.Xml file and it is located in the Documents and Settings\Application Data\Microsoft\Time Zone\188.8.131.52 directory.
Follow the steps below to modifying the Microsoft time zone utility to show US time zones:
1. Download and install the Timezone.Msi executable from the following Webpage:
2. After it is installed if it starts automatically exit from it in order to make your changes.
3. Open the MyLocations.Xml file and save it as MyLocations.Old.
4. Open the original MyLocations.Xml file and make the changes per your requirements.
5. Save the file and restart the Microsoft Time Zone utility. Then right mouse click the taskbar icon and choose ‘Show My Locations’ and the changes you have made will be shown.
If you want to show the four different continental United States time zones copy the text below into the MyLocations.Xml file and save it:
<cityList xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<City name="Pacific Standard Time: PST" timezone="4" default="True"/>
<City name="Mountain Standard Time: MST" timezone="10" default="True"/>
<City name="Central Standard Time: CST" timezone="20" default="True"/>
<City name="Pacific Standard Time: EST" timezone="35" default="True"/>
If you want to add key cities to your MyLocations.Xml file you can use the following:
Pacific Standard Time (PST) Los Angeles
Mountain Standard Time: (MST) Denver
Central Standard Time: (CST) Kansas City
Pacific Standard Time: (EST) New York
I originally posted a SQL query that allowed you to determine what version of SQL server you are running in an article entitled “Determining SQL Server Service Pack Versions” on myITforum four years ago today back on 4/2/2002:
Since that time several new SQL service packs as well as versions have appeared from Microsoft. It was just by coincidence that I thought of updating that query last night but I waited for the anniversary date (Today) of the original to come before posting it. I knew that I had written one before and after a quick search on myITforum I located my original query and copied it to be modified to reflect the new SQL version numbers.
There are of course several simple means for determining your SQL version numbers via a SQL query the simplest is to run the following in the Query analyzer:
A slightly more complex one would be the following:
A fancier one liner still would be using the Trim function as in the following:
Select Ltrim(Right(Left(@@Version,38),9)) 'SQL Version'
However the original query I wrote way back when would work for most SQL installations today but when it comes to SMS of course the older versions are no longer supported. Yet I have retained them and updated them slightly from the original for those who do not run SMS or Microsoft Operations Manager (MOM) and for the casual SQL user.
I for example still have the original media for SQL version 6.5 in addition to service packs 4 and 5 for it as well as SQL 7.0, 2000 and 2005 with selected service packs as well most of which is on original Microsoft select media. I do not have them all installed but in a moments time I can create a Microsoft Virtual Server machine and load them so that I can have them available.
To run or execute the SQL query below simply copy and paste the SQL query text into the SQL query analyzer (Isqlw.Exe from the start > run line) and press the F5 keyboard shortcut to start the query parser.
Note: The query bellow will execute in any of the databases you have loaded including the Master, Model, Msdb, Northwind, Pubs, Tempdb or your SMS_xxx database.
Declare @SQL VarChar(25)
Declare @Version VarChar(50)
Select @Version = @@Version
Select @SQL = Case
-- SQL 6.0
When @Version Like '%6.00.121%' Then 'SQL Server 6.0'
When @Version Like '%6.00.124%' Then 'SQL Server 6.0 SP 1'
When @Version Like '%6.00.139%' Then 'SQL Server 6.0 SP 2'
When @Version Like '%6.00.151%' Then 'SQL Server 6.0 SP 3'
-- SQL 6.5
When @Version Like '%6.50.201%' Then 'SQL Server 6.5'
When @Version Like '%6.50.213%' Then 'SQL Server 6.5 SP 1'
When @Version Like '%6.50.240%' Then 'SQL Server 6.5 SP 2'
When @Version Like '%6.50.258%' Then 'SQL Server 6.5 SP 3'
When @Version Like '%6.50.281%' Then 'SQL Server 6.5 SP 4'
When @Version Like '%6.50.415%' Then 'SQL Server 6.5 SP 5'
When @Version Like '%6.50.416%' Then 'SQL Server 6.5 SP 5a'
When @Version Like '%6.50.479%' Then 'SQL Server 6.5 SP 5a (Update)'
-- SQL 7.0
When @Version Like '%7.00.623%' Then 'SQL Server 7.0'
When @Version Like '%7.00.699%' Then 'SQL Server 7.0 SP 1'
When @Version Like '%7.00.842%' Then 'SQL Server 7.0 SP 2'
When @Version Like '%7.00.961%' Then 'SQL Server 7.0 SP 3'
When @Version Like '%7.00.1063%' Then 'SQL Server 7.0 SP 4'
-- SQL 2000
When @Version Like '%8.00.194%' Then 'SQL Server 2000'
When @Version Like '%8.00.384%' Then 'SQL Server 2000 SP 1'
When @Version Like '%8.00.534%' Then 'SQL Server 2000 SP 2'
When @Version Like '%8.760.0%' Then 'SQL Server 2000 SP 3'
When @Version Like '%8.760.0%' Then 'SQL Server 2000 SP 3A'
When @Version Like '%8.00.2039%' Then 'SQL Server 2000 SP 4'
-- SQL 2005
When @Version Like '%9.00.1399%' Then 'SQL Server 2005'
-- SQL Version Unknown
Else '<* Version Unknown *>'
Print 'Version Detected: ' + @SQL