创建作业的问题（十分古怪），泣求答案，大把散分

adocmd_backup.CommandText := // 2005-8-10/9:46 上生成的脚本
                                 // 由: sa
                                 // 服务器: 192.168.10.2
                                 ' BEGIN TRANSACTION'+
                                 ' DECLARE @JobID BINARY(16)'+
                                 ' DECLARE @ReturnCode INT'+
                                 ' SELECT @ReturnCode = 0'+
                                 ' IF (SELECT COUNT(*) FROM msdb.dbo.syscategories'+
                                 ' WHERE name = N''[Uncategorized (Local)]'') < 1'+
                                 ' EXECUTE msdb.dbo.sp_add_category'+
                                 ' @name = N''[Uncategorized (Local)]'''+                                 // 删除同名的警报（如果有的话）。
                                 ' SELECT @JobID = job_id'+
                                 ' FROM   msdb.dbo.sysjobs'+
                                 ' WHERE (name = N''xwl_backupplan'')'+
                                 ' IF (@JobID IS NOT NULL)'+
                                 ' BEGIN'+
                                 // 检查此作业是否为多重服务器作业
                                 '   IF (EXISTS (SELECT  *'+
                                 '   FROM    msdb.dbo.sysjobservers'+
                                 '   WHERE   (job_id = @JobID) AND (server_id <> 0)))'+
                                 '   BEGIN'+
                                 // 已经存在，因而终止脚本
                                 '     RAISERROR (N''无法导入作业“xwl_backupplan”，'+
                                 '     因为已经有相同名称的多重服务器作业。'', 16, 1)'+
                                 '     GOTO QuitWithRollback'+
                                 '   END'+
                                 '   ELSE'+
                                 // 删除［本地］作业
                                 '     EXECUTE msdb.dbo.sp_delete_job @job_name = N''xwl_backupplan'''+
                                 '     SELECT @JobID = NULL'+
                                 ' END'+                                 ' BEGIN'+                                 // 添加作业
                                 ' EXECUTE @ReturnCode = msdb.dbo.sp_add_job'+
                                 '                       @job_id = @JobID OUTPUT ,'+
                           '                       @job_name = N''xwl_backupplan'','+
                                 '                       @owner_login_name = N''sa'','+
                                '                      @description = N''没有可用的描述。'','+
                                 '                      @category_name = N''[Uncategorized (Local)]'','+
                                 '                    @enabled = 1, @notify_level_email = 0,'+
                                 '                   @notify_level_page = 0,'+
                                 '                       @notify_level_netsend = 0,'+
                                 '                      @notify_level_eventlog = 2,'+
                                 '                       @delete_level= 0'+
                                 ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'+                                 // 添加作业步骤
                                 ' EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,'+
                           '                       @step_id = 1, @step_name = N''第 1 步'','+
                                 '                    @command = N''USE master'+
                                 '                    EXEC sp_addumpdevice ''''disk'''', ''''JSP_BACKUPPLAN'''','+
                                 '                     '''''+edit1.Text+combobox2.Text+'_20050810.dat'''''+
                                 '                    BACKUP DATABASE JSP TO JSP_BACKUPPLAN'','+
                                 '                       @database_name = N''jsp'', @server = N'''','+
                                 '                    @database_user_name = N'''', @subsystem = N''TSQL'','+
                                 '                     @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0,'+
                                 '                     @retry_interval = 0, @output_file_name = N'''','+
                                 '                     @on_success_step_id = 0, @on_success_action = 1,'+
                                 '                     @on_fail_step_id = 0, @on_fail_action = 2'+
                                 ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'+
                                 ' EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1'+                                 ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'+                                 // 添加作业调度
                                 ' EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,'+
                                 '                   @name = N''第 1 调度'', @enabled = 1,'+
                                 '                       @freq_type = 4,@active_start_date = 20050808,'+
                                 '                       @active_start_time = 0,@freq_interval = 1,'+
                                 '                       @freq_subday_type = 1,@freq_subday_interval = 0,'+
                                 '                       @freq_relative_interval = 0,@freq_recurrence_factor = 0,'+
                                 '                       @active_end_date = 99991231, @active_end_time = 235959'+
                                 ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'+                                 // 添加目标服务器
                                 ' EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N''(local)'''+
                                 ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'+                                 ' END'+
                                 ' COMMIT TRANSACTION'+
                                 ' GOTO   EndSave'+
                                 ' QuitWithRollback:'+
                                 ' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'+
                                 ' EndSave:';
    //test
    Filename := ExtractFilePath(Application.Exename)+'path.ini';
    pathini := Tinifile.Create(filename);
    pathini.WriteString('test','test',adocmd_backup.CommandText);
    pathini.Destroy;
    //test
    adocmd_backup.Connection := adoconnection;
    try
      adocmd_backup.Execute;
    except
      on e:exception do showmessage(e.Message);
    end;

解决方案 »

  1.   

    测试楼主的sql代码没有问题.检查一下:
    1. CommandText 的结果是否被截断
    2. 简化脚本,分步调试
       创建作业的脚本是由几段组成的,先执行一段,如果无错误,再把后面的加上去,直到找出是那段有问题.
      

  2.   

    分布调试以上的sql语句；再次确认一下CommandText的内容
      

  3.   

    找到问题了，在delphi中不能执行sql语句中的begintrans语句，goto好像也有问题，我把他们都屏蔽掉了，统统放到异常中处理，调试通过，我把代码贴出来，各位看看有没有意见批评指正
    adoconnection.BeginTrans;
        adocmd_backup.CommandText := // 2005-8-10/9:46 上生成的脚本
                                     // 由: sa
                                     // 服务器: 192.168.10.2
                                     //' BEGIN TRANSACTION'+
                                     ' DECLARE @JobID BINARY(16)'+
                                     ' DECLARE @ReturnCode INT'+
                                     ' SELECT @ReturnCode = 0'+
                                     ' IF (SELECT COUNT(*) FROM msdb.dbo.syscategories'+
                                     ' WHERE name = N''[Uncategorized (Local)]'') < 1'+
                                     ' EXECUTE msdb.dbo.sp_add_category'+
                                     ' @name = N''[Uncategorized (Local)]'''+                                 // 删除同名的警报（如果有的话）。
                                     ' SELECT @JobID = job_id'+
                                     ' FROM   msdb.dbo.sysjobs'+
                                     ' WHERE (name = N''xwl_backupplan'')'+
                                     ' IF (@JobID IS NOT NULL)'+
                                     ' BEGIN'+
                                     // 检查此作业是否为多重服务器作业
                                     '   IF (EXISTS (SELECT  *'+
                                     '   FROM    msdb.dbo.sysjobservers'+
                                     '   WHERE   (job_id = @JobID) AND (server_id <> 0)))'+
                                     '   BEGIN'+
                                     // 已经存在，因而终止脚本
                                     '     RAISERROR (N''无法导入作业“xwl_backupplan”，'+
                                     '     因为已经有相同名称的多重服务器作业。'', 16, 1)'+
                                     //'     GOTO QuitWithRollback'+
                                     '   END'+
                                     '   ELSE'+
                                     // 删除［本地］作业
                                     '     EXECUTE msdb.dbo.sp_delete_job @job_name = N''xwl_backupplan'''+
                                     '     SELECT @JobID = NULL'+
                                     ' END'+                                 ' BEGIN'+                                 // 添加作业
                                     ' EXECUTE @ReturnCode = msdb.dbo.sp_add_job'+
                                     '                       @job_id = @JobID OUTPUT ,'+
                               '                       @job_name = N''xwl_backupplan'','+
                                     '                       @owner_login_name = N''sa'','+
                                    '                      @description = N''没有可用的描述。'','+
                                     '                      @category_name = N''[Uncategorized (Local)]'','+
                                     '                    @enabled = 1, @notify_level_email = 0,'+
                                     '                   @notify_level_page = 0,'+
                                     '                       @notify_level_netsend = 0,'+
                                     '                      @notify_level_eventlog = 2,'+
                                     '                       @delete_level= 0'+
                                     //' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'+                                 // 添加作业步骤
                                     ' EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,'+
                               '                       @step_id = 1, @step_name = N''第 1 步'','+
                                     '                    @command = N''USE master'+
                                     '                    EXEC sp_addumpdevice ''''disk'''', ''''JSP_BACKUPPLAN'''','+
                                     '                     '''''+edit1.Text+combobox2.Text+'_20050810.dat'''''+
                                     '                    BACKUP DATABASE JSP TO JSP_BACKUPPLAN'','+
                                     '                       @database_name = N''jsp'', @server = N'''','+
                                     '                    @database_user_name = N'''', @subsystem = N''TSQL'','+
                                     '                     @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0,'+
                                     '                     @retry_interval = 0, @output_file_name = N'''','+
                                     '                     @on_success_step_id = 0, @on_success_action = 1,'+
                                     '                     @on_fail_step_id = 0, @on_fail_action = 2'+
                                     //' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'+
                                     ' EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1'+                                 //' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'+                                 // 添加作业调度
                                     ' EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,'+
                                     '                   @name = N''第 1 调度'', @enabled = 1,'+
                                     '                       @freq_type = 4,@active_start_date = 20050808,'+
                                     '                       @active_start_time = 0,@freq_interval = 1,'+
                                     '                       @freq_subday_type = 1,@freq_subday_interval = 0,'+
                                     '                       @freq_relative_interval = 0,@freq_recurrence_factor = 0,'+
                                     '                       @active_end_date = 99991231, @active_end_time = 235959'+
                                     //' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'+                                 // 添加目标服务器
                                     ' EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N''(local)'''+
                                     //' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'+                                 ' END';//+
                                     {' COMMIT TRANSACTION'+
                                     ' GOTO   EndSave'+
                                     ' QuitWithRollback:'+
                                     ' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'+
                                     ' EndSave:';}
        //test
        Filename := ExtractFilePath(Application.Exename)+'path.ini';
        pathini := Tinifile.Create(filename);
        pathini.WriteString('test','test',adocmd_backup.CommandText);
        pathini.Destroy;
        //test
        adocmd_backup.Connection := adoconnection;
        try
          adocmd_backup.Execute;
          adoconnection.CommitTrans;
          statusbar2.Panels[2].Text := '备份计划已经成功建立!';
        except
          on e:exception do
          begin
            adoconnection.RollbackTrans;
            showmessage('错误：'+e.Message);
            statusbar2.Panels[2].Text := '错误：'+e.Message;
          end;
        end;