"An item with the same key has already been added" Error on SSRS When Trying to Set Dataset

Question

When i try to set the Dataset in SSRS IDE, i get the error you see in the snapshot.

The query works totally fine in SQL Server Management Studio, i wonder where did i go wrong?!

The connection to DB is well established.

---

OPTIONAL:

In case you want to have a look at my query (its too long), i checked it very well. Nothing wrong in it:

SELECT Customer.customerID, Customer.companyName,  CustomerInvoice.dueDate, CustomerInvoice.cuInvoiceID, CustomerQuote.PONumber, Product.productName, CASE WHEN (SELECT     isTaxPaid
                                                     FROM         SupplierQuoteProducts
                                                     WHERE     productID = CustomerQuoteProducts.ProductID) = 1 THEN CustomerQuoteProducts.unitPrice * 1.15
                                                     WHEN (SELECT     isTaxPaid
                                                     FROM         SupplierQuoteProducts
                                                     WHERE     productID = CustomerQuoteProducts.ProductID) = 0 THEN CustomerQuoteProducts.unitPrice
                                                     ELSE CustomerQuoteProducts.unitPrice
                                                     END AS "unitPrice", 
                      CustomerQuoteProducts.qty, CustomerQuoteProducts.isTaxPaid, PaymentMethod.paymMethDesc, CustomerInvoice.customerQuoteID, CustomerInvDetail.paidDate, CustomerInvDetail.clearedDate,
                      CustomerInvDetail.notes, CustomerInvDetail.sentDate, PaymentExpected.payExpectedTitle, PaymentStatus.paymentStatusTitle, 
CASE WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty > 0 AND 
CustomerQuoteProducts.isTaxPaid > 0 THEN (((CustomerQuoteProducts.unitPrice * 1.15) * 1.15) * CustomerQuoteProducts.qty) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty <= 0 AND 
CustomerQuoteProducts.isTaxPaid > 0 THEN ((CustomerQuoteProducts.unitPrice * 1.15) * 1.15) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND 
CustomerQuoteProducts.isTaxPaid > 0 THEN ((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty) * 1.15) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND 
CustomerQuoteProducts.isTaxPaid > 0 THEN (CustomerQuoteProducts.unitPrice * 1.15) WHEN
(SELECT     Count(isTaxPaid)
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND 
CustomerQuoteProducts.isTaxPaid > 0 THEN ((CustomerQuoteProducts.unitPrice * 1.15) * CustomerQuoteProducts.qty) WHEN
(SELECT     Count(isTaxPaid)
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND 
CustomerQuoteProducts.isTaxPaid > 0 THEN (CustomerQuoteProducts.unitPrice * 1.15) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty > 0 AND 
CustomerQuoteProducts.isTaxPaid <= 0 THEN (((CustomerQuoteProducts.unitPrice * 1.15)) * CustomerQuoteProducts.qty) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty <= 0 AND 
CustomerQuoteProducts.isTaxPaid <= 0 THEN ((CustomerQuoteProducts.unitPrice * 1.15)) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND 
CustomerQuoteProducts.isTaxPaid <= 0 THEN ((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty)) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND 
CustomerQuoteProducts.isTaxPaid <= 0 THEN (CustomerQuoteProducts.unitPrice) WHEN
(SELECT     Count(isTaxPaid)
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND 
CustomerQuoteProducts.isTaxPaid <= 0 THEN ((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty)) WHEN
(SELECT     Count(isTaxPaid)
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND 
CustomerQuoteProducts.isTaxPaid <= 0 THEN (CustomerQuoteProducts.unitPrice) END AS [TotalPrice], CASE WHEN row_number() OVER (partition BY 
CustomerInvoice.cuInvoiceId
ORDER BY newid()) = 1 THEN (CASE WHEN CustomerShipping.isTaxPaid > 0 THEN (CustomerShipping.shippingPrice * 1.15) 
WHEN CustomerShipping.isTaxPaid <= 0 THEN (CustomerShipping.shippingPrice) END) END AS [ShippingCost],
CASE WHEN row_number() OVER (partition BY 
CustomerInvoice.cuInvoiceId
ORDER BY newid()) = 1 THEN (CASE WHEN CustomerShipping.isTaxPaidForOrigPr > 0 THEN (CustomerShipping.origShipPrice * 1.15) 
WHEN CustomerShipping.isTaxPaidForOrigPr <= 0 THEN (CustomerShipping.origShipPrice) END) END AS [ShippingOrigCost],
CustomerShipping.isTaxPaid,
CustomerShipping.isTaxPaidForOrigPr,
CustomerShipping.shippingDate, CustomerShipping.trackingNumber, ShippingMethod.shippingVia, CustomerShipping.desAddress,
CustomerShipping.desCity, CustomerShipping.desPOBox, CustomerShipping.desPostalCode, CustomerShipping.desProvince, CustomerShipping.descName,
CustomerShipping.packageContent, CustomerShipping.cuShippingID, Country.countryName, CustomerShipping.packageDepth,
CustomerShipping.packageHeight, CustomerShipping.packageWeight, CustomerShipping.packageWidth, CustomerShipping.pickUpLocation
FROM         CustomerInvoice INNER JOIN
                      CustomerInvDetail ON CustomerInvoice.cuInvoiceID = CustomerInvDetail.cuInvoiceID INNER JOIN
                      CustomerQuote ON CustomerQuote.CustomerQuoteID = CustomerInvoice.CustomerQuoteID INNER JOIN
                      CustomerQuoteProducts ON CustomerQuoteProducts.CustomerQuoteID = CustomerQuote.CustomerQuoteID INNER JOIN
                      CustomerShipping ON CustomerShipping.CustomerQuoteID = CustomerInvoice.CustomerQuoteID INNER JOIN
                      PaymentStatus ON PaymentStatus.paymentStatusID = CustomerInvDetail.paymentStatusID INNER JOIN
                      Customer ON Customer.CustomerID = CustomerQuote.CustomerID INNER JOIN
                      Product ON CustomerQuoteProducts.productID = Product.productID INNER JOIN
                      Country ON Country.countryID = CustomerShipping.countryID INNER JOIN
                      ShippingMethod ON ShippingMethod.shippingMethodID = CustomerShipping.shippingMethodID INNER JOIN
                      PaymentExpected ON PaymentExpected.paymentExpectedID = CustomerInvDetail.paymentExpectedID INNER JOIN
                      PaymentMethod ON PaymentMethod.paymentMethodID = CustomerInvoice.paymentMethodID
WHERE  CustomerInvoice.cuInvoiceID = @cuInvID

Solution

After formatting your script a bit, I noticed that there are 2 columns with the same name that you are selecting. Make sure to change the final name & that every column when you do run your statement in Management Studio has a unique name.

That being said, the two columns I noticed have duplicate names are customerquoteproducts.istaxpaid and customershipping.istaxpaid

I hope that helps!

Comments

[Beginner_Pal]

I see, but these similar columns reside in totally different tables! I mention the prefix, so the system shouldn't be confused ... Thanks for your help, i will check it out...

[Mike M]

yeah, another crazy, unhelpful error in Microsoft BI... I also had the same problem and this worked

[Donald Webb]

If you wrap your outer select SMSS will give you a duplicate column name warning and save some headaches. SELECT * FROM ( <your select> ) results