Post reply

Simple SQL Query

  • July 6, 2006 at 9:34 am

    #114663

    Hi,

    I wonder if anyone can help me, I am trying to get this bit of code working @MY_DB_NAME varchar(20), @JobID INT

    DECLARE @MaintenancePlanName varchar(20),  

    SET

    @sqlcmd = 'EXECUTE msdb.dbo.sp_add_job @job_id = ' + convert(varchar (20), @JobID)+ ' OUTPUT, @job_name = N''DB Backup Job for DB Maintenance Plan ' + @MaintenancePlanName + ''', @owner_login_name = N''sa'', @description = N''Backup Job for ' +@MY_DB_NAME + ''', @category_name = N ''Database Maintenance'', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0' 

    EXEC (@sqlcmd)

    What I want to do it get the Job_ID OUTPUT to be saved into my variable for the rest of the SP, can you help?

    Thanks

    Mike

     

  • July 6, 2006 at 9:39 am

    #647782

    Try

    EXEC sp_executesql @sqlcmd, N'@JobID INT OUTPUT', @JobID OUTPUT

    Far away is close at hand in the images of elsewhere.
    Anon.

  • July 6, 2006 at 7:07 pm

    #647873

    Since you can't concatenate the arguments in sp_add_job use this (it declares variables for each of your concatenated properties and uses the variables as the arguments):

    DECLARE @JobId BINARY(16) 

    DECLARE @JobName VARCHAR(200)

    DECLARE @Desc VARCHAR(200)

    DECLARE @MY_DB_NAME varchar(20)

    DECLARE @MaintenancePlanName VARCHAR(20)

    SET @MaintenancePlanName = 'MyMaintPlan'

    SET @MY_DB_NAME = 'MyDatabase'

    SET @JobName = 'DB Backup Job for DB Maintenance Plan ' + @MaintenancePlanName

    SET @Desc = 'Backup Job for ' +@MY_DB_NAME

    EXEC msdb.dbo.sp_add_job @job_id = @JobId OUTPUT

    , @job_name = @JobName

    , @owner_login_name = N'sa'

    , @description = @Desc

    , @category_name = 'Database Maintenance'

    , @enabled = 1

    , @notify_level_email = 0

    , @notify_level_page = 0

    , @notify_level_netsend = 0

    , @notify_level_eventlog = 2

    , @delete_level= 0

    Select @JobId

    -

  • July 7, 2006 at 1:56 am

    #647892

    Nice one Jason.

    I should of thought of that

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply