SQL Job Running the Upgrade Step, but the Step Fails

Posted on 2010-08-31
Medium Priority
Last Modified: 2012-05-10
In SSMS, EXEC spCent, and the user-defined stored procedure runs successfully. When the same SQL command is executed from a SQL Job, the job fails, and job history is like the following:

...on file 1.
[SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'Cent', file 'Cent_log' on file 1.
[SQLSTATE 01000] (Message 4035) Converting database 'Cent' from version 611 to the current version 655.
[SQLSTATE 01000] (Message 944)  Database 'Cent' running the upgrade step from version 611 to version 621.
[SQLSTATE 01000] (Message 951)  Database 'Cent' running the upgrade step from version 621 to version 622.
[SQLSTATE 01000] (Message 951)  Database 'Cent' running the upgrade step from version 652 to version 653.
[SQLSTATE 01000] (Message 951)  Database 'Cent' running the upgrade step from vers...  The step failed.

As mentioned above, the stored proc can be successfully executed from within SSMS. The stored proc looks like this:

ALTER PROCEDURE [dbo].[spCent]
     PURPOSE: Replace the current Cent database with the most recent backup provided by the regional office.
    @AccDate AS DATE = '01/01/2010',
    @Count   AS INT  = 0,
    @Err     AS INT  = 0
    -- Extract the BAK files from the zipped file.
    EXEC @Err = xp_cmdshell '7z x E:\Cent\Cent.zip -oE:\Cent -y'

    IF @Err = 0 BEGIN
        RESTORE DATABASE Cent FROM DISK = 'E:\Cent\Cent.bak'

        IF @@ERROR = 0 BEGIN
            RESTORE DATABASE Cent2 FROM DISK = 'E:\Cent\Cent2.bak' WITH REPLACE

            IF @@ERROR = 0 BEGIN
                                                                WHEN 2 THEN CONVERT(DATE, DATEADD(D, -3, GETDATE()))    -- Monday
                                                                ELSE CONVERT(DATE, DATEADD(D, -1, GETDATE()))           -- Tuesday thru Saturday

                SET @Count = (SELECT COUNT([ID]) FROM Cent2.dbo.Access WHERE AccessDate >= @AccDate)

                IF @Count = 0
                    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DB_EMail',
                                                   @recipients = 'me@xyz.com',
                                                         @body = 'The backup used to restore database >>> Cent <<< is outdated. The most-current data was entered on',
                                                        @query = 'SELECT MAX([AccessDate]) FROM Cent2.dbo.Access',
                                                      @subject = 'WARNING: Old BAK File'
                    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DB_EMail',
                                                   @recipients = 'me@xyz.com',
                                                         @body = 'Successfully unzipped and restored database >>> Cent <<<',
                                                      @subject = 'Unzip/Restore: Successful'
                EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DB_EMail',
                                               @recipients = 'me@xyz.com',
                                                     @body = 'Unable to RESTORE the #2 database for >>> Cent <<<',
                                                  @subject = 'WARNING: Problem with Backup File'
            EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DB_EMail',
                                           @recipients = 'me@xyz.com',
                                                 @body = 'Unable to RESTORE database >>> Cent <<<',
                                              @subject = 'WARNING: Problem with Backup File'
        EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DB_EMail',
                                       @recipients = 'me@xyz.com',
                                             @body = 'Cannot find Cent.zip for >>> Cent <<<',
                                          @subject = 'WARNING: No Cent.zip'

The SQL Job config is simply:

         Owner: <domain_name>\Administrator'
      Category: Database Maintenance
       Enabled: True
          Type: T-SQL
    # of Steps: 1
       Command: EXEC spCent
    On Success: Quit job reporting success
    On Failure: Quit job reporting failure
 Retry Attemps: 0
Retry Interval: 0

The problem occurs whether I manually fire-off the SQL Job or wait for it to run at its scheduled time. The Job used to work until I added the 2nd RESTORE DATABASE command-line. That is when the problem began. Regardless of adding the 2nd RESTORE statement, the stored proc will run fine within SSMS. Any ideas on how to resolve this problem???
Question by:brian_appliedcpu
  • 3
LVL 28

Expert Comment

by:Ryan McCauley
ID: 33568442
Why don't you just run the first RESTORE command with REPLACE - why wait until it fails before attempting to do the REPLACE? I'm not sure why it would fail as a SQL Agent step and not as an SSMS script, but that may get you back to a single RESTORE statement.

Could it be a permissions difference between the user that you're logging into SSMS with and the proxy user that's executing the SQL Agent steps? If you log in to SSMS as the proxy user and attempt to execute the SP, do you have different results?

Author Comment

ID: 33569242
If I return to the original stored proc, the one that had only the single RESTORE statement, my SQL Job is still having the same problem. And a newly created Job gets the same error as well.

The Agent Service is running under the Network Service account and the Job is as well. This account has full control to the resources that you see listed in the stored proc.

How do I go about double-checking which proxy user?

Author Comment

ID: 33576278
I applied a proxy user, but it did not resolve the issue. And according to the following Microsoft site (http://msdn.microsoft.com/en-us/library/ms189064.aspx), it does not apply anyway, since I am using T-SQL within my stored procs.

Then as a test, I temporarily assigned the sysadmin role to all logins and user accounts; the SQL account, Network Service account, and Windows account (which owns the SQL Job), but the SQL Job continues to fail with the same error messages, even though I can successfully run the stored proc from within SSMS.

Accepted Solution

brian_appliedcpu earned 0 total points
ID: 33592260
Don't know why I didn't think of specifying an "Output file", instead of relying on the "Job History" all this time. The output file definitely gave me the complete info that I needed. Unlike the job history, which continued to truncate the log (e.g. [SQLSTATE 01000] (Message 951)  Database 'Cent' running the upgrade step from vers...  The step failed), the output file revealed that the problem was much further "down the line". The history lead me to think that the failure was within the intial RESTORE statement of the stored proc, but the output file showed that the failure was actually with the @query argument of the sp_send_dbmail procedure.

There's nothing wrong with the SQL statement assigned to @query. It will execute as a standalone statement without failure (plus keep in mind that the overall stored proc will run without failure within SSMS). But to workaround the failure when using the SQL Job, I declared a new parameter (@MaxDate) within the stored proc, and prior to the execution of sp_send_dbmail, I set @MaxDate = the same SELECT statement used by @query:

SET @MaxDate = (SELECT MAX([AccessDate]) FROM Cent2.dbo.Access)

Then leaving the existing @body argument almost exactly as-is, only concatenating @MaxDate to the @body argument:

@body = 'The backup used to restore database >>> Cent <<< is outdated. The most-current data was entered on ' + (SELECT CONVERT(VARCHAR(20), @MaxDate))

So, I am no longer utilizing the @query argument, and the SQL Job now executes without failure.

Thanks to the help of the output file, this issue is resolved.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question