SQL Job Running the Upgrade Step, but the Step Fails

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???
Who is Participating?
brian_appliedcpuConnect With a Mentor Author Commented:
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.
Ryan McCauleyData and Analytics ManagerCommented:
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?
brian_appliedcpuAuthor Commented:
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?
brian_appliedcpuAuthor Commented:
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.
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.

All Courses

From novice to tech pro — start learning today.