Capture Logins Script from KB 878449 generates Invalid object name error


David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

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.

Original script Excerpt

declare @version2005 char(5)
declare @version2008 char(5)
declare @version2008R2 char(5) 
--Get the current version of SQL Server running
select @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:
Modified Script Excerpt

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 running
select @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:
Final Script Excerpt

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.

David

PS: I have requested that the script file associated with the KB article be updated with the new version I created.

KB878449_Capture_Logins.zip

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

13 thoughts on “Capture Logins Script from KB 878449 generates Invalid object name error

  1. 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.
    Sheila

    Like

  2. 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')

    Like

  3. I was having this same issue with the original script and then tried yours. Now I get this error. Must declare the scalar variable “@version”. I am using SQL2014. Where can I declare the variable? Im not that good with these scripts, so might need simple steps to follow.

    Thanks

    Like

  4. David,

    Thanks for the assist. Couldn’t get a good answer on the forums until searching with space between capture logins. Without the space you get the same stale non working post.

    Really helpful post and great explanation.

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.