TSQL batch separator

[richardii]

I'm generating some TSQL from the Enterprise Manager and being warned that I have GO set as my TSQL batch separator. I can't change it though (the option is greyed out). I tried changing it in SQL Query Analyzer (no effect).

How can I change the TSQL batch separator? Or can I 'repair' my TSQL?

Thanks.

I have a lot of syntax errors when I run the script.

The script as generated looks like this:

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'TLMAT DB Restore TLM2100') > 0
PRINT N'The job "TLMAT DB Restore TLM2100" already exists so will not be replaced.'
ELSE
BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'TLMAT DB Restore TLM2100', @owner_login_name = N'sa', @description = N'TLMAT restore database for automated testing.', @category_name = N'Database Maintenance', @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Restore Database', @command = N'exec spForceRestoreDB ''TLM2100'', ''C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TLM2100.dmp''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Drop DB Users', @command = N'sp_dropuser tlm2100$setup
go
sp_dropuser tlm2100$tlmpack
go
sp_dropuser tlm2100$tester
go', @database_name = N'TLM2100', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'Drop DB Logins', @command = N'sp_droplogin tlm2100$setup
go
sp_droplogin tlm2100$tester
go
sp_droplogin tlm2100$tlmpack
go', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 4, @step_name = N'Update User Status To Create Users', @command = N'update usrr set user_status = 3, password = '''' ,previous1='' '',previous3='' '',previous2='' '',previous4='' ''', @database_name = N'TLM2100', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 5, @step_name = N'TLM Security Administration Create SETUP user login', @command = N'"C:\SmartStream Technologies\Security Administrator\2.2\SecurityAdmin.exe" /ENCRYPTOUBLE /DRIVER:SQL Server /SERVER:BRISV0010 /DATABASE:TLM2100 /USER:sa /PASSWORDassword', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Weekly', @enabled = 0, @freq_type = 8, @active_start_date = 20060316, @active_start_time = 30000, @freq_interval = 62, @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 70000
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Internal Test', @enabled = 0, @freq_type = 8, @active_start_date = 20060320, @active_start_time = 130000, @freq_interval = 62, @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 170000
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'rji_test', @enabled = 0, @freq_type = 4, @active_start_date = 20060607, @active_start_time = 92000, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:


and the syntax issues in my log file are given as:

Unclosed quotation mark before the character string 'sp_dropuser
tlm2100$setup

'.
Msg 170, Level 15, State 1, Server BRISV0010, Line 23
Line 23: Incorrect syntax near 'sp_dropuser tlm2100$setup

'.
1> 2> Msg 15008, Level 16, State 1, Server BRISV0010, Procedure sp_dropuser, Line 12
User 'tlm2100$tlmpack' does not exist in the current database.
1> 2> 3> 4> 5> Msg 170, Level 15, State 1, Server BRISV0010, Line 2
Line 2: Incorrect syntax near 'go'.
1> 2> The login 'tlm2100$tester' does not exist.
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> Msg 170, Level 15, State 1, Server BRISV0010, Line 2
Line 2: Incorrect syntax near 'go'.
Msg 170, Level 15, State 1, Server BRISV0010, Line 4
Line 4: Incorrect syntax near ''.
Msg 105, Level 15, State 1, Server BRISV0010, Line 21
Unclosed quotation mark before the character string '

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback



END

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

 

[MartinCroft]

Hi

Are you running this through QA, as when i copied the code and create a database with the same name, it executed fine

 

[richardii]

I'm executing it through a bat file command:

ISQL -u <user> -P <password> -d <Database> -i <the script above> -o <the log file above>

It appears I can log on OK, but I see the syntax errors. Unfortunately I can't use QA to execute it (it has to be done from a Virtual PC without SQL Server installed).

Thanks.

 

[richardii]

It was messed up around the 'go's I removed those and placed all the syntax on one line, it ran through fine and created the job. In fact all I wanted to do was run an existing job (so it was much easier then I thought
Thanks.