October 1, 2009 at 3:15 am
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......
October 1, 2009 at 3:36 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply