none
Issue with "SQL Server Agent - Jobs" while creating job from script RRS feed

  • Question

  • Hi All,

    I am not sure if its right form for this query or not.

    With SQL Server 2005 SP3, I am creating a job from T-SQL script with NVARCHAR(max) datatype and @schedule_uid but its failed when we use SQL Server 2005 SP4.

    TSQL Code

    Create procedure [dbo].[Job1] 

          @DBName     varchar(50)
          as declare 
          @DBSTR NVARCHAR(max),
          @UID varchar(50),
          @LastJobID varchar(50)
         
          set @UID = NEWID()
          
                select @LastJobID =  job_id FROM msdb.dbo.sysjobs_view WHERE name =  + @DBName + '_Delete_Snapshot'
                      if @LastJobID is not null
                      EXEC msdb.dbo.sp_delete_job @job_id=@LastJobID, @delete_unused_schedule=1
          
          
          select @DBSTR = '
               USE [msdb]

                        BEGIN TRANSACTION
                        DECLARE @ReturnCode INT
                        SELECT @ReturnCode = 0

                        IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N''Jobs'' AND category_class=1)
                        BEGIN
                        EXEC msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name=N''Jobs''
                        END
                   USE [' + @DBName +']
                        DECLARE @jobId BINARY(16)
                        EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=''' + @DBName + '_Delete'', 
                                                    @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''Jobs'', 
                                                    @owner_login_name=N''sa'', @job_id = @jobId OUTPUT
                        IF (@@ERROR not in(0) OR @ReturnCode not in(0)) GOTO QuitWithRollback
                        
                        EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Step1'', 
                                        @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''TSQL'', 
                                        @command=N''' + '
                      SET ANSI_NULLS ON

                                        SET QUOTED_IDENTIFIER ON

                                        SET ANSI_PADDING ON

                                        declare @SnapID Uniqueidentifier,
                                        @TName varchar(50),
                                        @SQLStr Varchar(255),
                                        @snapID1 varchar(50)

                      If 
                      (Select sum(open_transaction_count) 
                      from sys.dm_exec_requests
                      where database_id = DB_ID(''''' + @DBNAME + ''''') and session_id > 50) is null
                      Or
                      (Select sum(open_transaction_count) 
                      from sys.dm_exec_requests
                      where database_id = DB_ID(''''' + @DBNAME + ''''') and session_id > 50) = 0
                      Begin

                              DECLARE SnapPlan_ID CURSOR FOR
                                                          SELECT snapshot_id  from Plan where obsolete = ''''Y'''' 
                                                          OPEN SnapPlan_ID;
                                                          FETCH NEXT FROM SnapPlan_ID into @snapID
                                                          While @@FETCH_STATUS =0
                                                          Begin
                                                                      DECLARE SnapTable CURSOR FOR
                                                                      select name from sys.sysobjects where xtype = ''''U'''' and name like ''''%_Data'''' 
                                                                      OPEN SnapTable;
                                                                      FETCH NEXT FROM SnapTable into @TName
                                                                      While @@FETCH_STATUS =0
                                                                      Begin
                                                                                                Set @snapID1 = @SnapID
                                                                                                Select @SQLStr = ''''Delete FROM '''' + @TName + '''' WHERE Snapshot_id = convert(Uniqueidentifier,'''''''''''' + @snapID1 +'''''''''''')'''' 
                                                                                                Exec (@SQLStr)
                                                                      FETCH NEXT FROM SnapTable into @TName
                                                                      End
                                                                      CLOSE SnapTable
                                                                      DEALLOCATE SnapTable
                                                          Delete  from Plan_Snapshots where snapshot_id =  + @snapID 
                                                          FETCH NEXT FROM SnapPlan_ID into @snapID
                              End
                              CLOSE SnapPlan_ID
                              DEALLOCATE SnapPlan_ID
                      End''' + ', 
                                        @database_name=N''' + @DBNAME + ''', 
                                        @flags=0
                      IF (@@ERROR not in(0) OR @ReturnCode not in(0)) GOTO QuitWithRollback
                      EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
                      IF (@@ERROR not in(0) OR @ReturnCode not in(0)) GOTO QuitWithRollback
                      EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N''SnapDelSchedule'', 
                                        @enabled=1, 
                                        @freq_type=4, 
                                        @freq_interval=1, 
                                        @freq_subday_type=4, 
                                        @freq_subday_interval=15, 
                                        @freq_relative_interval=0, 
                                        @freq_recurrence_factor=0, 
                                        @active_start_date=20111215, 
                                        @active_end_date=99991231, 
                                        @active_start_time=0, 
                                        @active_end_time=235959, 
                                        @schedule_uid=N''' + @UID +'''
                      IF (@@ERROR not in(0) OR @ReturnCode not in(0)) GOTO QuitWithRollback
                      EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''(local)''
                      IF (@@ERROR not in(0) OR @ReturnCode not in(0)) GOTO QuitWithRollback
                      COMMIT TRANSACTION
                      GOTO EndSave
                      QuitWithRollback:
                            IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
                      EndSave:
          
          '
    exec sp_executesql @dbstr  
         
       

    Regards

    Deepak Goyal

    Thursday, June 28, 2012 5:30 AM

Answers

  • Hi Deepak_Goyal,

    >>Msg 8145, Level 16, State 1, Procedure sp_add_jobschedule, Line 0 @schedule_uid is not a parameter for procedure sp_add_jobschedule.

    Regarding to the error message,  SQL 2005 uses @schedule_id (an integer) and SQL 2008 uses @schedule_uid (a uniqueidentifier).

    Seems you are using SQL Server 2005. So please try to change the parameter name and data-type to the correct value, the parameter is @schedule_id and not @schedule_uid.

    Regards,
    Amber zhang
    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Friday, June 29, 2012 9:30 AM
    Moderator

All replies

  • Hello Deepak,

    Please avoid such double post or if, please provide a link to it: Issue with "SQL Server Agent - Jobs" while creating job from script in SQL Tools.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Thursday, June 28, 2012 5:33 AM
    Moderator
  • Hi Olaf,

    I want to move post from SQL tool to Database Engine. As I have no idea how to move it so I done this.

    Please guide me how to create link for it. 

    Thanks in advance.

    Deepak Goyal

    Thursday, June 28, 2012 5:36 AM
  • What is the error you are getting?

    Regards, Matt Bowler MCITP, My blog

    Thursday, June 28, 2012 8:57 AM
  • Error Message:

    Msg 8145, Level 16, State 1, Procedure sp_add_jobschedule, Line 0
    @schedule_uid is not a parameter for procedure sp_add_jobschedule.

    Thursday, June 28, 2012 11:26 AM
  • Hi Deepak_Goyal,

    >>Msg 8145, Level 16, State 1, Procedure sp_add_jobschedule, Line 0 @schedule_uid is not a parameter for procedure sp_add_jobschedule.

    Regarding to the error message,  SQL 2005 uses @schedule_id (an integer) and SQL 2008 uses @schedule_uid (a uniqueidentifier).

    Seems you are using SQL Server 2005. So please try to change the parameter name and data-type to the correct value, the parameter is @schedule_id and not @schedule_uid.

    Regards,
    Amber zhang
    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Friday, June 29, 2012 9:30 AM
    Moderator
  • Duplicate post - http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/954050bd-1973-485a-a3fa-c037d1ffbdb3/#56a003f2-ef52-4295-b4e6-16a0cc05b68f

    Both @schedule_id & @schedule_uid is OUTPUT variable, so can not provide input to that.

    MSDN at below link clears that @schedule_uid is not a parameter in SQL 2005, SQL 2005 uses @schedule_id (an integer) and SQL 2008 uses @schedule_uid (an uniqueidentifier).

    http://msdn.microsoft.com/en-us/library/ms366342(v=sql.90).aspx

    Please click the Mark as Answer or Vote As Helpful  if a post solves your problem or is helpful!

    Friday, June 29, 2012 12:21 PM
  • Hi Deepak_Goyal,

    >>Msg 8145, Level 16, State 1, Procedure sp_add_jobschedule, Line 0 @schedule_uid is not a parameter for procedure sp_add_jobschedule.

    Regarding to the error message,  SQL 2005 uses @schedule_id (an integer) and SQL 2008 uses @schedule_uid (a uniqueidentifier).

    Seems you are using SQL Server 2005. So please try to change the parameter name and data-type to the correct value, the parameter is @schedule_id and not @schedule_uid.

    Regards,
    Amber zhang
    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Hi, In my case

    1) In my script I have use NVARCHAR(max) in variable for storing my query. When I print this variable in SQL Server 2005 SP3, it shows full query what I had passed but its shows only 255 char while using  SQL Server 2005 SP4

    2) As per BOL, SQL Server 2005 supports @schedule_id and SQl Server 2008 supports @schedule_uid, but in my infrastructure @schedule_uid also works on SQL Server 2005 SP3 but its not work on SQL Server 2005 SP4

    Regards

    Deepak Goyal 

    Tuesday, July 3, 2012 10:08 AM
  • Can you post the result of SELECT @@TEXTSIZE?

    I suspect what is happening is that your string is being truncated before being passed to the executesql statement. You could verify this by using a print or select instead of the execute and see what is happening to your string. To get this to work on my machine I removed a bunch of the whitespace from the string...


    Regards, Matt Bowler MCITP, My blog

    Tuesday, July 3, 2012 11:19 PM