SQL Job Failing When Not Ran in 32bit Mode

Question

I've created a maintenance plan to backup the local databases. If I execute the maintenance plan, it successfully completes and backs up to the specified folder.

When I try to schedule it through the SQL Agent job, it will fail unless the "Use 32 bit runtime" option is selected in the job step. If it is selected, it runs without any issues.

I've ensured that the 64bit SQL paths are before their 32bit versions in the PATH environmental variable.

Both SQL and the server are 2008 R2 64bit. The databases are on the local server, and so is the backup path.

Job Definition:

USE [msdb]
GO

/****** Object:  Job [Database Backups and Maintenance.DB Backups]    Script Date: 04/07/2015 16:10:27 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 04/07/2015 16:10:27 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Database Backups and Maintenance.DB Backups', 
        @enabled=1, 
        @notify_level_eventlog=2, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'Database Maintenance', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DB Backups]    Script Date: 04/07/2015 16:10:27 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DB Backups', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'SSIS', 
        @command=N'/SQL "Maintenance Plans\Database Backups and Maintenance" /SERVER DBSERVER /CHECKPOINTING OFF /SET "\Package\DB Backups.Disable";false /REPORTING E', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Database Backups and Maintenance.DB Backups', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20150325, 
        @active_end_date=99991231, 
        @active_start_time=210000, 
        @active_end_time=235959, 
        @schedule_uid=N'f9491d4e-a0c5-44fe-be65-333b32c3d74e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Create a new maintenance plan (within SSMS) with a very simple read-only query and execute via SQL Agent. If that works w/out 32 bit run time option, then there is some dependency on the 32 bit version package exection of your maintenance plan. For example, verify connection managers, etc. — nshah, Apr 07 '15 at 19:28

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

To indicate the package should run in 32 bit mode, try editing the command to include the /86 flag

@command=N'/SQL "Maintenance Plans\Database Backups and Maintenance" /SERVER DBSERVER /CHECKPOINTING OFF /SET "\Package\DB Backups.Disable";false /REPORTING E /X86',

edited May 23 '17 at 12:06

Community

1
1

answered Apr 07 '15 at 20:56

billinkc

59,250
9
102
159

SQL Job Failing When Not Ran in 32bit Mode

1 Answers1