Simple SQL Query

• mike roberts-342685

  Valued Member

  Points: 71

  More actions

  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

   

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  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.

• Jason-

  SSCrazy

  Points: 2795

  More actions

  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

  -

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  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.