This week I had an interesting case from a partner trying to use the Capture Logins script from Knowledge Base (KB) article 878449.
When they ran the script on their SQL Server 2012 system they received the follow error:
Invalid object name 'master..sysxlogins'.
Looking at the source code for the script where the error occurs, I could see that the code has two branches. For the newer SQL Server versions it is meant to call the temporary stored procedure seeMigrateSQLLogins, otherwise it should call the temporary stored procedure sp_help_revlogin.
So why was it running the wrong branch of the code?
The branch executed is decided by an if statement which checks the version number information returned from the @@version system variable. When I tested the code using print statements to display what version number the if statement was comparing against on my SQL Server 2012 system, it displayed "P1) " when the code was expecting "11.0".
The problem occurs because the @@version for SQL 2012 SP1 shifts the version number six characters to the right, causing the version number check to fail and the wrong branch of the code to execute.
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
The original code below cannot handle this change. In fact it has to be changed for every version of SQL Server as the layout of the information return by the @@version command might have changed.
declare @version2005 char(5)declare @version2008 char(5)declare @version2008R2 char(5)
--Get the current version of SQL Server runningselect @version2005 = substring(@@version,29,4)select @version2008 = substring(@@version,35,4)select @version2008R2 = substring(@@version,38,4) if (@version2005 in ('9.00','11.0') or @version2008 = '10.0' or @version2008R2 = '10.5')
We could just keep using the same method and extend it to work with SQL Server 2012 SP1 (and tidy up the SQL Server 2012 handling as well). However, this will still need to be updated for each new SQL Server version, see below:
declare @version2005 char(5)declare @version2008 char(5)declare @version2008R2 char(5) declare @version2012 char(5) declare @version2012SP1 char(5)
--Get the current version of SQL Server runningselect @version2005 = substring(@@version,29,4)select @version2008 = substring(@@version,35,4)select @version2008R2 = substring(@@version,38,4)select @version2012 = substring(@@version,29,4)select @version2012SP1 = substring(@@version,35,4) if (@version2005 = '9.00' or @version2008 = '10.0' or @version2008R2 = '10.5' or @version2012 = '11.0' or @version2012SP1 = '11.0')
A better method is to make the code smart and version independent. We can achieve this by using the patindex() function looking for the string #.# (Number, Full stop (period), Number) in the version details and then starting one character before the location returned (to capture two digit major version numbers) and grab the following 4 characters using substring(). By trimming off the left hand spaces using ltrim() we can remove the space at the beginning when the major version number is only a single digit (ie. version 9.0). Finally using cast() to change the data type allows for a numeric comparison of the version to check if it is version 9.0 or greater.
Below is the version proof code which will work for any version and does not need changes unless the method used needs to change:
if cast(ltrim(substring(@@version, patindex('%[0-9].[0-9]%', @@version)-1,4)) as decimal(4,2)) >= 9.0
Try the following on your system. This will display the major and minor version numbers for your SQL Server.
print ltrim(substring(@@version,patindex('%[0-9].[0-9]%', @@version)-1,4))
The archive attached to the bottom of this article contains three script files: the original script (KB878449_Capture_Logins_Original.sql), the modified version (KB878449_Capture_Logins_Modified.sql) and then the final version (to replace the original: KB878449_Capture_Logins.sql) using the patindex() approach.
Hope you find this useful.
PS: I have requested that the script file associated with the KB article be updated with the new version I created.
Posting from Mariano Gomez, The Dynamics GP Blogster
Thanks to both you and Mariano, this information will save me much time searching for a solution. Thanks again for being such a great resource to the community.
If you just want the numeric version for SQL, this command may work little better and it goes back to SQL 2000: SELECT SERVERPROPERTY('productversion')
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.