BUG: SQL 2008 Express Edition - Stored Procedure sp_DBMaintenance

The following error message occurs when you execute the sp_DBMaintenance stored procedure on master database:

"Msg 50000, Level 16, State 1, Procedure sp_DBMaintenance, Line 104
SQL2005 or higher is required for sp_expressmaint"

The Transact-SQL code for the sp_DBMaintenance stored procedure incorrectly uses the substring for check of server version. The following code in the stored procedure causes the error to occur:


   -- check SQL2005 or higher
   IF (select SUBSTRING(@@version,(CHARINDEX('-',@@version)+2),1))<9
 BEGIN                       
    RAISERROR('SQL2005 or higher is required for sp_expressmaint',16,1)
      SET @ret = 1
    GOTO CLEANUP 
 END

The length argument of substring is only one character but the version number of sql 2008 is 10 (two characters) therefore return value is less than 9 and error messsage occurs.
  
To work around this problem, change the length value to 2.

SUBSTRING(@@version,(CHARINDEX('-',@@version)+2),2)


Posted May 25 2009, 10:10 AM by shajric

Comments

SQL2005 or higher is required for sp_expressmaint | Nobody Listens Anyway wrote SQL2005 or higher is required for sp_expressmaint | Nobody Listens Anyway
on 11-23-2010 5:46

Pingback from  SQL2005 or higher is required for sp_expressmaint | Nobody Listens Anyway

developers.de is a .Net Community Blog powered by daenet GmbH.