SQL Server Performance Forum – Threads Archive

backup job failed

Hi, when i try to run the job it is failing and I am getting the followng message. Message
Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed. This is happening for only a particular database and all the other jobs are fine.Even for this database if I create a new job it is working fine. Can how help me on this.

It says it is syntax error…
Post the code?
What is the type of command you are using in job step? make sure it is tsql…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Also check if what job don’t need exclusive access to the database.
Are you sure is backup job? Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no warranties for accuracy.

If that database is used with . or any other special character then use [ ] before and after the database name. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

USE [msdb]
GO
/****** Object: Job [CLASSTEST DB Maint Plan – Daily] Script Date: 04/09/2007 14:15:41 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 04/09/2007 14:15:41 ******/
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’CLASSTEST DB Maint Plan – Daily’,
@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’AMERICAsqladmin’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [CLASS DB Maint Plan – Daily] Script Date: 04/09/2007 14:15:41 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email protected], @step_name=N’CLASSTEST DB Maint Plan – Daily’,
@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’/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance PlansCLASS DB Maint Plan – Daily" /set "PackageCLASSTEST DB Maint Plan – Daily.Disable;false"’,
@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 @[email protected], @name=N’CLASSTEST DB Maint Plan – Daily’,
@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=20070323,
@active_end_date=99991231,
@active_start_time=10500,
@active_end_time=235959
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:

What is the database name> Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

I don’t see anthing wrong with this code but it may be something to do the code which is used for db backup in your maintenance plan and it could be special character issue in db name or file name as Satya mentioned….
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

The db name is CLAS

CLAS is not a reserved word for SQL server… it could be in .net or c# Did you try to run the same backup script which used in SSIS package in Query Window?
If not try and see what you get…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

THats the best and first option to see what is the problem, if not try to recreate that maintenance plan or a seperate plan for this databsae alone. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

The maintenance plan is edited from the client which has service pack 2 installed on it and the actual server doesnt have service pack installed on that, so it caused the problem.We installed the service pack 2 and it fixed the problem. Thanks

It is very interesting solution… sp2 resolved syntax error !!!…
Thanks for sharing the solution…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Ha, we should gone for that as a first question as SP2 (latest fix) has resolved most of the maintenance plan issues. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>