issue with GO statement is SQL batch

[GoogleCodeExporter]

We have a batch script which creates steps in SQL job.

The script is being truncated at a GO statement which is part of a step 
of the job.

example script:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Daily 
job', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=3, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'
dml statements
GO  
dml statements '

I am using RoundhousE.v0.7.0.276 on Windows server 2003.

How can i prevent the script being split incorrectly.

Original issue reported on code.google.com by [email protected] on 22 Nov 2010 at 3:33

[GoogleCodeExporter]

Thanks for pointing this out. I will add this to my tests and get you a fix. 
This should work, being that GO is between two ' marks. But it appears it is 
not detecting that it is. It may be that N' is what is getting it to have 
issues.

Original comment by [email protected] on 22 Nov 2010 at 7:43

• Changed state: Accepted

[GoogleCodeExporter]

I added this to the tests and it worked perfectly. I think I'm going to need 
more information on exactly what your script looks like. You can send it to me 
at ferventcoder at gmail if you don't feel comfortable with putting it on here. 

The script above works and doesn't split on the go in between the tick marks. I 
think it may be something else in the dml statements it is erroring on.

Original comment by [email protected] on 22 Nov 2010 at 7:57

[GoogleCodeExporter]

Just to show you what the job output looks like:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Daily 
job', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=3, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'
dml statements
GO  
dml statements '

GO

Produces this:
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Daily 
job', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=3, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'
dml statements
GO  
dml statements '

 |{[_REMOVE_]}| 

Original comment by [email protected] on 22 Nov 2010 at 7:58

[GoogleCodeExporter]

Hi,
Please find attached the full exact script that failed due to the GO statement.
This scripts works find via SQL query analyser.

Original comment by [email protected] on 23 Nov 2010 at 1:56

Attachments:

• failedscript.sql
• failed.log