Solved

SQL Job Running the Upgrade Step, but the Step Fails

Posted on 2010-08-31
4
962 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now