BUG: SQL 2008 Express Edition - Stored Procedure sp_DBMaintenance

shajric — Mon, 25 May 2009 08:10:00 GMT

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)

Senad Hajric

BUG: SQL 2008 Express Edition - Stored Procedure sp_DBMaintenance