Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Job Running the Upgrade Step, but the Step Fails

Posted on 2010-08-31
4
Medium Priority
?
1,020 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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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 …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

879 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