sql - versión - "Ya se ha agregado un elemento con la misma clave" Error en SSRS al intentar establecer un conjunto de datos

ya se agrego un elemento con la misma clave visual studio

Question

Cuando intento configurar el conjunto de datos en el IDE de SSRS, aparece el error que se ve en la instantánea.

La consulta funciona totalmente bien en SQL Server Management Studio, me pregunto ¿dónde me equivoqué?

La conexión a DB está bien establecida.

OPCIONAL:

En caso de que quiera ver mi consulta (es demasiado larga), la revisé muy bien. No hay nada malo en ello:

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

Answer 1

Después de formatear un poco su script, noté que hay 2 columnas con el mismo nombre que está seleccionando. Asegúrese de cambiar el nombre final y que cada columna cuando ejecute su declaración en Management Studio tenga un nombre único .

Dicho esto, las dos columnas que noté tienen nombres duplicados son customerquoteproducts.istaxpaid y customershipping.istaxpaid

¡Espero que eso ayude!

Answer 2

Me encontré con este post hoy cuando recibí el mismo problema. Mi problema, sin embargo, fue un poco diferente.

Tenía una tabla temporal y una declaración If en mi SP. Así que mi SP se veía algo como esto:

DECLARE @myCheck as int
SET @myCheck = 0
SELECT @myCheck = CASE WHEN [checkfield] = ''xxx'' then 1 else 0 end
FROM checktable
WHERE ...
SELECT myfields.*
into #temptable
FROM mytable
WHERE ...
IF @myCheck = 1 --if ssrs does''nt want ot refresh your fields, remove hte if as it does not see past this...
select *
FROM #temptable
LEFT JOIN tableA
ON ...
ELSE
select *
FROM #temptable
LEFT JOIN tableB
ON ...

Así que para "actualizar" mis campos en SSRS, comenté la declaración If y mantuve una de mis consultas con los nombres de los campos. Solo tuve que acordarme de volver a añadir todo después ...

Usando Visual Studio 2012 / SQL 2012