Drop Table Gets skipped in a query batch

I have a situation in SQL Svr 2k that I can't explain. I have a query batch that runs in a SQL step in a DTS package. All steps execute except the Drop Table statements. Here is a section of the code;

************************************************** ****
INSERT INTO Oral_VitDhistory ( MeNumber, DrugType, Territory, Quantity, SalesAmt, DataType, RXDate )
SELECT tbl_Oral_VitDHistory.MENumber, tbl_Oral_VitDHistory.DrugType, tbl_Oral_VitDHistory.Territory, tbl_Oral_VitDHistory.Quantity, tbl_Oral_VitDHistory.SalesAmt, tbl_Oral_VitDHistory.DataType, tbl_Oral_VitDHistory.RXDate
FROM Data_Warehouse..tbl_Oral_VitDHistory WHERE tbl_Oral_VitDHistory.Territory Is Not Null

drop table Data_Warehouse..NDC24month_Cost
drop table Data_Warehouse..NDC24month_Count

/* Process New Rx */

select MeNumber, zip, RXTypecode, DrugTYpe, NRX1 into NDC24month_Cost from Data_Warehouse..NDC24month where RxTypecode = '$ B'

Insert into tbl_Oral_VitDHistory(MeNumber, DrugType, Quantity, SalesAmt) select a.MeNumber,
a.DrugType, a.NRX1 as Quantity, b.NRX1 as SalesAmt from Data_Warehouse..NDC24month_Count as a, NDC24month_Cost
as b where a.Menumber = b.MeNumber and a.DrugType = b.DrugType
************************************************** *******

The drop table statements appear to get skipped and the code fails because the column names should have been changed as the dropped tables should have been recreated with a different column name.

This used to work fine. Then suddenly it stopped working. No changes to the SQL installation from when it worked to when it stopped. The batch also fails in the QA if cut and pasted and ran from there.

I ran accross this once before in a Stored Proc with the Drop table statement and had to put the statement in a different procedure.

Anybody ever run accross this? I have searched the MS KB to no avail.

Thanks in advance to the genius who can help a guy out!Howdy,

I have come across this before - usually the best way to get around it is to break the code into separate sprocs & then call each in turn from a main sproc. Even using BEGIN TRAN...COMMIT TRAN wont help....it seems to get a bee in its bonnet and thats it......

Also, if its in DTS package etc , break up the code into logical chunks with end of batch "GO" commands. This will mean separate sprocs if you run it in one sproc currently.

Cheers,

SG.|||Thanks for your help!

Thats about where I thought I would have to go with it.
You confirmed my suspicions!

Have a great one!

Originally posted by sqlguy7777
Howdy,

I have come across this before - usually the best way to get around it is to break the code into separate sprocs & then call each in turn from a main sproc. Even using BEGIN TRAN...COMMIT TRAN wont help....it seems to get a bee in its bonnet and thats it......

Also, if its in DTS package etc , break up the code into logical chunks with end of batch "GO" commands. This will mean separate sprocs if you run it in one sproc currently.

Cheers,

SG.