Siusic Dot Com

For a long time I find it is very confusing when it comes to deal with T-SQL errors because T-SQL error sometimes prevent all the statements in a batch from being executed, sometimes it just prevent the statements after the error statement from being executed, and sometimes it doesn’t prevent any statement execution except for the error statement itself. It may cause problem when you have a process, like an ETL process for example, that takes a long time to run and each statement in the process depends on the result of the previous statement. In this kind of process you may not want to use transaction to rollback changes already done when an error occurs because it will

take you a long of time to re-run it from the beginning. What you want is probably that whenever error occur the process terminate right there so that you can have a chance to examine the error and fix it and then restart the process right from the failing statement. This is where the GO statement can help.

The GO statement is not a SQL statement. It is a command recognized by the OSQL and ISQL utilities and SQL Query Analyzer. It signals to these utilities that it is the end of the current T-SQL batch and a new T-SQL batch starts. You can use the GO statement in a T-SQL job step or in an Execute SQL Task in DTS. However using GO in this two environments is totally different from using it in Query Analyzer. Consider the following statements

create table test1 (seq int identity(1,1), firstname char(20))
select * from test1
insert into test1 (firstname) values ('Andrew')
GO

exec nonexist
GO

insert into test1 (firstname) values ('Somebody‘)
select * from test1
GO

select * from test1
drop table test1
GO

create table test2 (seq int identity(1,1), firstname char(20), lastname char(20))
insert into test2 (firstname,lastname) values ('andrew', 'chen')
select * from test2
drop table test2
GO

The store procedure “nonsexist” is not defined. If you run all these statements in query analyzer you will find all of them will be executed except for this one, “exec nonexist”. Executing this statement will return an error telling you that the store procedure doesn’t exist. But the occurrence of this error doesn’t prevent the execution of the rest of the statements in query analyzer. However if you put all the statements into a SQL job step or an Execute SQL task in a DTS package and execute the job or DTS, you will find execution stops on “exec nonexist”. You can verify that by doing “select * from test1″ after running your job or DTS and you will see “Somebody” doesn’t get inserted.

The GO statement after “exec nonexist” is critical. If it is not there the execution will continue through to the statement before the next GO statement. If you put the following statements into SQL job step or Execute SQL Task in DTS and run them.

create table test1 (seq int identity(1,1), firstname char(20))
select * from test1
insert into test1 (firstname) values ('Andrew')
GO

exec nonexist --The GO statement after this is removed

insert into test1 (firstname) values ('Somebody‘)
select * from test1 –terminated
GO

select * from test1
drop table test1
GO

create table test2 (seq int identity(1,1), firstname char(20), lastname char(20))
insert into test2 (firstname,lastname) values ('andrew', 'chen')
select * from test2
drop table test2
GO

You will find execution stop on “select * from test1 –terminated”. It can be verified by doing a “select * from test” after execution. You will find “Somebody” get inserted in this case.

The conclusion is a T-SQL job step and Execute SQL Task in DTS will stop on the batch where error occurs. It doesn”t matter what kind of error it is. The batches after the error batch will not be executed (remember this is not the case for query analyzer). But the statements after the error statements in the same batch may or may not be executed depends on the type of the error (please refer to additional source). You probably want to place a GO statement after each SQL statement to make sure no statements are executed after error occurs. But that doesn’t work sometimes when your statements use variables.

By selectively placing the GO statement between SQL statements and using some kind of logging mechanism, for instants you can have a logging table and insert something into it after each SQL statement, you can make a process traceable when error occurs. You will know where it fail and stop exactly and you can fix it and restart the process from the failing point. Of course you may have to twist the process a little bit so that it can be restarted from there.

Here are the additional source that may help on understanding the topic

http://www.vbip.com/books/1861002319/chapter_2319_03.asp
http://forums.devx.com/archive/index.php/t-19449.html

Related Posts:

Estimated Rollback Completion: 0%. Estimated Time Remaining: 0 Seconds.

How to Kill AutoShrink Process

Error 1222 Lock Request Time Out Period Exceeded When Set up Replication

MySQL Replication and MS SQL Log Shipping

MySQL Update Statement