Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

TSQL batch separator

Status
Not open for further replies.

richardii

Programmer
Jan 8, 2001
104
GB
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" /ENCRYPT:DOUBLE /DRIVER:SQL Server /SERVER:BRISV0010 /DATABASE:TLM2100 /USER:sa /PASSWORD:password', @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:

 
Hi

Are you running this through QA, as when i copied the code and create a database with the same name, it executed fine
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top