need help getting syntax error

• sanketahir1985

  SSCarpal Tunnel

  Points: 4473

  More actions

  October 1, 2009 at 3:15 am

  #98012

  hi guys

  when i parse foll. code i get syntax error near ':' (highlighted in code)

  My code is

  declare @drive as char(1)

  declare @cmd as nvarchar(4000)

  select @drive = drive1 from tracedb..drivename

  --select convert(char(25),getdate(),120) --112

  set @cmd = 'declare @cmdstr1 sysname,@cmdstr2 sysname,@t1 nvarchar(12)'+ CHAR(10)

  set @cmd = @cmd + 'select @t1= replace(replace(replace(convert(char,getdate(),120),'[font="Arial Black"]:[/font]',''),' ',''),'-','')' + CHAR(10) /*here is syntax error near ':'*/

  set @cmd = @cmd + 'set @cmdstr1=''ren '+ @drive+':\profilertrace\oldfiles\*.trc *.'' + @t1 + '''''+ CHAR(10)

  set @cmd = @cmd + 'exec master..xp_cmdshell @cmdstr1,no_output'+ CHAR(10)

  set @cmd = @cmd + 'set @cmdstr2=''move '+ @drive+':\profilertrace\oldfiles\*.* '+ @drive+':\profilertrace\oldfiles\archive\'''+ CHAR(10)

  set @cmd = @cmd + 'exec master..xp_cmdshell @cmdstr2,no_output'

  --Creating job step 5

  exec sp_add_jobstep @job_name= 'Audit Trace Job'

  ,@step_id= 5

  ,@step_name= 'Rename Files'

  ,@command= @cmd

  ,@on_success_action= 3 --Go to next step

  ,@on_fail_action= 4 --go to @on_fail_step_id

  ,@on_fail_step_id= 7

  go

  what is the soln for this

  Sanket Ahir
  Don't run behind the success, Try to be eligible & success will run behind u......

• Girish Bhat

  Ten Centuries

  Points: 1323

  More actions

  October 1, 2009 at 3:36 am

  #1060345

  Use the below line instead of the erroneous line:

  set @cmd = @cmd + 'select @t1= replace(replace(replace(convert(char,getdate(),120),'':'',''''),'' '',''''),''-'','''')' + CHAR(10) /*here is syntax error near ':'*/

  Basically since you are constructing T-SQL statements, you need to use two single quotes instead of one. Otherwise the single quote will be considered as part of the string terminator.

  Girish