FIX: DTS "Execute SQL Task" Terminates Prematurely Without Completing All Commands in Batch

ID: Q238523

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 55660 (SQLBUG_70)

SYMPTOMS

The Data Transformation Services (DTS) "Execute SQL Task" terminates without completely processing all the commands in a batch. If the task calls a stored procedure that performs multiple data modification operations, the task can return prematurely after the first command, even before all the other commands are executed.

CAUSE

The task returns after the first command is completed since the "Execute SQL Task" option expects only one result set.

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The English version of this fix should have the following file attributes or later:



   File name    Platform
   ----------------------

   s70720i.exe  Intel
   s70720a.exe  Alpha

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the above files may also contain additional files.

WORKAROUND

Instead of calling the procedure from a "Execute SQL Task", call the procedure using OSQL.exe. This call can be added to a DTS package using "Execute Process Task".

Setting the NOCOUNT option ON, inside the stored procedure, may help since the DONE_IN_PROC messages are not sent and the procedure may complete without any other messages. See the MORE INFORMATION section for additional details on how this can help.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

MORE INFORMATION

Prior to this fix, DTS considered the "Execute SQL Task" as a single operation. The task only expected one result set to return from the batch. As soon as the first DONE_IN_PROC message or result set was encountered, the DTS client processed the results and terminated execution of the batch. More information about DONE_IN_PROC settings can be found in SQL Server Books Online. Subsequent commands in the batch, stored procedure or trigger were cancelled. This fix makes DTS "Execute SQL Task" process all result sets until the batch successfully completes.

The behavior can be a bit misleading as it may appear that more than one result set was processed before the premature cancellation occurred. If you use the following TSQL example:


create table tblTest( iID int)
go

declare @iID	int
set @iID = 0

while @iID < 100000
begin
	insert into tblTest values(@iID)
	set @iID = @iID + 1
end
go

When you execute the preceding batch from ISQLW you see a series of messages returned in the result window like the following:

"(1 row(s) affected)"

However, if you enable the NOCOUNT option, the messages no longer appear. The difference is that the DONE_IN_PROC is no longer returned for each Insert command in the loop. The DONE_IN_PROC carries the rows affected back to the client as the commands are executed. SQL Server fills an entire tabular data stream (TDS) packet before flushing it to the client.

There are other factors involved in sending the result set to the client. For example, lets say that 1000 DONE_IN_PROC messages fit in a 512 byte TDS packet. The first return to the client would occur after 1000 inserts take place. However, if your packet size was 4096 the first return would not occur until 8000 rows were inserted.

NOTE: (4096/512 = 8 packets * 1000 per TDS packet = 8000 rows).

REFERENCES

SQL Server Books Online; topic: "SET"; search on: "NOCOUNT".

For more information on client/server TDS interaction, refer to the following article in the Microsoft Knowledge Base:

Q180775 INF: Client Effects on SQL Server Throughput

The concepts described in the article and related links apply to any DB-Library, ODBC or SQL OLEDB client.

Additional query words:

Keywords : SSrvTrans kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug