OK This is a weird one

  • The developer DBA and I killed an hour last night trying to make this work and failed. I showed it to another production DBA today and they too were flummoxed. Four servers, four databases total, all on SQL Server 2005, max SP level was 3 with no cumulative updates.

    Here's the situation. We have an empty staging table we are trying to load. The staging table has a foreign key relationship to another table. The key is a composite of two fields, a varchar(8) and an int. The other table in the relationship doesn't have a primary key, but the fields referenced by the foreign key are in a unique clustered index. We are trying to load the table with rows, and to ensure that the foreign key is not messed up, we take the data we are loading (from a work DB on the same server) and inner join it to the remote table on the foreign key so only rows that are validly selected in an inner join get sumbitted to the INSERT.

    No dice. Foreign key violation. We tried to create a temp table, use a cursor, all sorts of things. It's to the point where I selected a single row that I know is in the source table and joins properly to the referenced table, so that the value is there. I then try to insert just that one row into the staging table. Nope, foreign key violation, even though I just proved that the row existed in the unique index being referenced.

    We finally got the data to insert using a CTE like this:

    WITH Contracts

    AS (SELECT

    ROW_NUMBER() OVER (PARTITION BY TRADING_PARTNER_ID,CORP_ITEM_NUM ORDER BY ROW_UPDATE_STP DESC, ContractID DESC) RowNumber,

    CORP_ITEM_NUM,

    TRADING_PARTNER_ID,

    ContractID

    FROM

    WorkDB.dbo.ContractItems_Staging cs WITH (NOLOCK)

    WHERE

    EXISTS ( SELECT

    1

    FROM

    dbo.CUSTOMER_CATALOG cc WITH (NOLOCK)

    WHERE

    cc.CORP_ITEM_NUM = cs.CORP_ITEM_NUM AND

    cc.TRADING_PARTNER_ID = cs.TRADING_PARTNER_ID ))

    INSERT INTO #ContractItems -- a temp table we made

    (

    Trading_Partner_ID,

    Corp_Item_Num,

    ContractID

    )

    SELECT

    TRADING_PARTNER_ID,

    CORP_ITEM_NUM,

    ContractID

    FROM

    Contracts c

    WHERE

    RowNumber = 1 ;

    INSERT INTO dbo.ContractItems

    (

    Trading_Partner_ID,

    Corp_Item_Num,

    ContractID,

    IsRebateEligible,

    ChangedDate,

    ChangedUser

    )

    SELECT

    Trading_Partner_ID,

    Corp_Item_Num,

    ContractID,

    0,

    GETDATE(),

    'WARP'

    FROM

    #ContractItems cs

    WHERE

    ContractID > 0 AND

    NOT EXISTS ( SELECT

    1

    FROM

    dbo.ContractItems ci

    WHERE

    ci.Corp_Item_Num = cs.Corp_Item_Num AND

    ci.Trading_Partner_ID = cs.Trading_Partner_ID ) ;

    But I can't explain why a simple INSERT....SELECT would not work and this had to be used. Three people looked this over pretty well, and all agree we are doing it "by the book". Is there some obscure setting we are missing, is this a bug, or what? I don't think it's a code issue as we have done this over and over throughout the years and all three of us agree that the code is valid and should work.

  • I am still perplexed by this one. For clarity, here's a rundown of the situation.

    This select against the staging table returns one row.

    select * from WorkDB.dbo.ContractItems_Staging cc WHERE

    cc.Trading_Partner_ID = 5 AND

    cc.corp_item_num = 1000074

    This select against the destination table returns zero rows.

    select * from dbo.ContractItems cc WHERE

    cc.Trading_Partner_ID = 5 AND

    cc.corp_item_num = 1000074

    This select against the table referenced by the foreign key returns one row.

    select * from dbo.CUSTOMER_CATALOG cc WHERE

    cc.Trading_Partner_ID = 5 AND

    cc.corp_item_num = 1000074

    To prove that the data is the same, I joined the staging table to the table referenced by the foreign key. This returns one row.

    select * from WorkDB.dbo.ContractItems_Staging cs

    inner join CUSTOMER_CATALOG cc on

    cc.CORP_ITEM_NUM = cs.Corp_Item_Num AND

    cc.TRADING_PARTNER_ID = cs.Trading_Partner_ID

    where

    cc.Trading_Partner_ID = 5 AND

    cc.corp_item_num = 1000074

    And for reference this is the foreign key code:

    ALTER TABLE [dbo].[ContractItems] WITH CHECK ADD CONSTRAINT [FK_ContractItems_CUSTOMER_CATALOG] FOREIGN KEY([Corp_Item_Num], [Trading_Partner_ID])

    REFERENCES [dbo].[CUSTOMER_CATALOG] ([CORP_ITEM_NUM], [TRADING_PARTNER_ID])

    GO

    ALTER TABLE [dbo].[ContractItems] CHECK CONSTRAINT [FK_ContractItems_CUSTOMER_CATALOG]

    GO

    Given this background, I see no reason why this would fail:

    INSERT INTO dbo.ContractItems

    (

    Trading_Partner_ID,

    Corp_Item_Num,

    ContractID,

    IsRebateEligible,

    ChangedDate,

    ChangedUser

    )

    SELECT

    cs.Corp_Item_Num,

    cs.Trading_Partner_ID,

    ContractID,

    0 IsRebateEligible,

    GETDATE() ChangedDate,

    SUSER_SNAME() ChangedUser

    FROM

    WorkDB.dbo.ContractItems_Staging cs

    INNER JOIN dbo.CUSTOMER_CATALOG cc

    ON cs.Corp_Item_Num = cc.CORP_ITEM_NUM AND

    cs.Trading_Partner_ID = cc.TRADING_PARTNER_ID

    WHERE

    cc.Trading_Partner_ID = 5 AND

    cc.corp_item_num = 1000074

    But it DOES fail with this:

    Msg 547, Level 16, State 0, Line 2

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ContractItems_CUSTOMER_CATALOG". The conflict occurred in database "...", table "dbo.CUSTOMER_CATALOG".

    The statement has been terminated.

    I did a DBCC CHECKDB on the database with no errors.

    What the heck am I missing here????

  • INSERT INTO dbo.ContractItems

    (

    Trading_Partner_ID,

    Corp_Item_Num,

    ContractID,

    IsRebateEligible,

    ChangedDate,

    ChangedUser

    )

    SELECT

    cs.Corp_Item_Num,

    cs.Trading_Partner_ID,

    ContractID,

    0 IsRebateEligible,

    GETDATE() ChangedDate,

    SUSER_SNAME() ChangedUser

    Are the items in bold in the wrong order?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • I think I am going to kill that developer tomorrow....

    (I say this knowing that he reads this forum and probably will see this before we talk 😛 )

    All I can say is that three people missed it and so I can't say I am a total idiot.

  • jeff.mason (9/15/2010)


    I think I am going to kill that developer tomorrow....

    Why wait that long? A 5 minute start is all he should get... :w00t:

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • He's in training today so I have to wait. Actually I will be happy to get him to buy lunch or something....

  • jeff.mason (9/15/2010)


    He's in training today so I have to wait. Actually I will be happy to get him to buy lunch or something....

    I strongly recommend pork chops for lunch. 😉 Heh... for you, too! You all missed it. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yeah, I know, I feel stupid too. What can I say, it was 11:30 PM, we are tired, and stuck in a rut for troubleshooting. We attacked the WHERE clause and did every form of INSERT we could think of, but no one noticed that the staging table and the destination table were in different column orders. Hopefully it happens to all of us sometime.

  • jeff.mason (9/15/2010)


    Yeah, I know, I feel stupid too. What can I say, it was 11:30 PM, we are tired, and stuck in a rut for troubleshooting. We attacked the WHERE clause and did every form of INSERT we could think of, but no one noticed that the staging table and the destination table were in different column orders. Hopefully it happens to all of us sometime.

    Yep, I think we've all been there, done that. But... did this actually fix your problem? (I would assume so...)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • The CTE fixed it in live, but now we know that it just did it because of the order. I had reproduced the problem on stage and was trying to solve it this morning, and yes, as soon as I fixed the code it worked exactly like I was expecting it to work all along.

    I don't know what I would do when stuck in a rut without a place like this. With so many of you folks around, SOMEONE is bound to find the groaner being overlooked. This place is awesome.

  • And to be explicit, that last post was also intended as a thank-you to Wayne....

  • You're quite welcome. Glad I could help.

    jeff.mason (9/15/2010)


    I don't know what I would do when stuck in a rut without a place like this. With so many of you folks around, SOMEONE is bound to find the groaner being overlooked. This place is awesome.

    Quite true. Both about the place, and all of the folks that hang out here just helping others.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Steve posted a link a few months ago to a study that found out that working 40 hours / week for 2 months gave the same exact output as working 60 hours/week for the same 2 months. So that 160 EXTRA hours was for nothing on the long run.

    When the body is overworked it does stupid mistakes and misses the obvious.

    You now have the perfect proof. There's no way in hell 3 smart people would miss this at 10 AM on a thuesday morning after a good night's sleep.

  • jeff.mason (9/15/2010)


    Yeah, I know, I feel stupid too. What can I say, it was 11:30 PM, we are tired, and stuck in a rut for troubleshooting. We attacked the WHERE clause and did every form of INSERT we could think of, but no one noticed that the staging table and the destination table were in different column orders. Hopefully it happens to all of us sometime.

    Heh... oh no... please don't feel stupid. We've ALL made a similar or worse mistake and I was just having a little fun because this mistake made me feel less alone in the world of making mistakes. 😉

    Thanks for the very cool feedback, Jeff. I'm the same way... I love this site and I love the people on this site.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply