Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    39

    Question Unanswered: 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!

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    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.

  3. #3
    Join Date
    Nov 2003
    Posts
    39
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •