I found the problem but still not sure why it doesn't work!

I used the USER_NAME() and SUSER_NAME() functions to check the before and after and found that SUSERNAME() returned...

Yes, when I run your code snippet I get the same error message as per my original post.

Yes, it is a Windows user account not a SQL login and the account exists in both Server A and Server B (the source database server).

Thanks for the code snippet Eric.

GSquared (10/21/2011)

---

Is there any reason to not just delete the row with incomplete data? If you don't, you'll end up with two identical rows, and that's either a violation...

GSquared (10/21/2011)

---

Is the precendence such that the one you want to replace will always have a zero-length string as the value, and there will always be at most one other...

Jeff/Magoo,

Thanks both for your suggestions; I've very quickly tested Magoo's code against my sample data and it does return the expected results. I'm going to need a bit of time...

The example I gave was really simplified but another example is where a product, which is made of many components, is itself a component of another product. Again, this is...

Yes to the question about only returning leaf level products, I'll try and explain. "Product A" consists of three components (Part a1, Part a2 and Part a3). Most customers will...

Jeff Moden (5/29/2011)

---

Gosh... what would you want if Part Z had more than just...

Well, initial testing on my production data looks good! So thank you very much for your help with this problem 😀

Using "Part D" as the example the result would be the following components:

Part X

Part W

Part Y

Just to clarify, the error is not the duplication (resolved using DISTINCT), it is that only the very last level in the structure is returned even though the Part D...

To reproduce the error (or rather incorrect results) I've added some more items to the sample table:

IF OBJECT_ID(N'tempdb..#ProductAssembly ') > 0

DROP TABLE #ProductAssembly ;

GO

--Create temp...

Thanks, it works using the sample data but not on my production data....digging around now to try and figure out why it doesn't!