Solved

SQL Job Running the Upgrade Step, but the Step Fails

Posted on 2010-08-31
4
981 Views
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:

Message:
Executed as user: NT AUTHORITY\NETWORK SERVICE.
...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
AS
    -- 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
                SET @AccDate = (SELECT CASE DATEPART(DW, GETDATE()) WHEN 1 THEN CONVERT(DATE, DATEADD(D, -2, GETDATE()))    -- Sunday
                                                                WHEN 2 THEN CONVERT(DATE, DATEADD(D, -3, GETDATE()))    -- Monday
                                                                ELSE CONVERT(DATE, DATEADD(D, -1, GETDATE()))           -- Tuesday thru Saturday
                                                                END)

                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'
                ELSE
                    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'
                END
            ELSE
                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'
            END
        ELSE
            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'
        END
    ELSE
        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
                GO
    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???
0
Comment
Question by:brian_appliedcpu
  • 3
4 Comments
 
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?
0
 
LVL 2

Author Comment

by:brian_appliedcpu
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?
0
 
LVL 2

Author Comment

by:brian_appliedcpu
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.
0
 
LVL 2

Accepted Solution

by:
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Linked Server Issue with SQL2012 3 28
SQL Insert parts by customer 12 42
SSAS Hierarchy with columns with folder names 10 19
SQL Database Restore 2008 R2 1 12
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 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