When creating a data set in Reporting Services or Report Builder, you might encounter the following error:
Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct
Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct
When you click on the details button, you’ll see An item with the same key has already been added.
This error occurs when the result set returned by the data set query has more than one column with the same name. Please make sure that each column is given a distinct name. You can use alias if you have columns with same name from different tables.
I checked for duplicated column names -
ReplyDeleteUsing SQL 2008 R2 - Have a fairly large query 626 lines that joins 4 tables and has many CASE statements. In the SQL Query windows the query runs to completion and returns 50 columns of data. When attempting to cut and paste the query into a dataset in SSRS and produce a list of fields I get the error "An item with the same key has already been added". I checked to make sure there were no duplicate column names.
I created a stored procedure and was able to create the dataset but was not able to get a field list - same error. The stored procedures executes in the designer window as does the query.
Any help is greatly appreciated.
Hey Mark,
DeleteWould it be OK if I ask you to post the query here?
I'm having a similar problem here in Power Query in Excel connecting to a DB2 for i via ODBC.
DeleteMy problem is also with a data set from Case statements, but I've also created a copy of the dataset in a temp table, still getting the error. No duplicate column names, returns fine in other applications. Any ideas? Here is the query. Any ideas?
SELECT
CONO AS "CompanyNumber",
TRTP AS "TransactionType",
TKEN AS "TokenUsedToUniquelyIdentifyRecord",
-- SSEQ AS "SplitTransactionSequenceNumber",
INVN AS "InvoiceNumber",
CHRFILE_MM.IDATEUSA(INDE) AS "InvoiceDate",
ORDR AS "ManufacturingOrderNumber",
SHSQ AS "ShipmentSequence",
CUNO AS "CustomerNumber",
CUCL AS "CustomerClass",
SLCD AS "SalesCode",
SRNO AS "SalesrepNumber",
TRNO AS "TerritoryNumber",
ITNO AS "ItemNumber",
ITCL AS "ItemClass",
ITAC AS "ItemAccountingClass",
WHID AS "WarehouseCode",
SCRF AS "SpecialChargeReference",
TXCD AS "TaxCode",
TRAM AS "Amount",
CHGC AS "Company",
TRIM(CHGA) AS "GL_Account",
ICHF AS "InvalidFlag",
'Charge' AS "AmountType",
TRIM(CHGC)||'-'||TRIM(CHGA)||'-1' AS "GL_AccountID"
FROM AMFLIB.OEITXN
UNION ALL
SELECT
CONO AS "CompanyNumber",
TRTP AS "TransactionType",
TKEN AS "TokenUsedToUniquelyIdentifyRecord",
-- SSEQ AS "SplitTransactionSequenceNumber",
INVN AS "InvoiceNumber",
CHRFILE_MM.IDATEUSA(INDE) AS "InvoiceDate",
ORDR AS "ManufacturingOrderNumber",
SHSQ AS "ShipmentSequence",
CUNO AS "CustomerNumber",
CUCL AS "CustomerClass",
SLCD AS "SalesCode",
SRNO AS "SalesrepNumber",
TRNO AS "TerritoryNumber",
ITNO AS "ItemNumber",
ITCL AS "ItemClass",
ITAC AS "ItemAccountingClass",
WHID AS "WarehouseCode",
SCRF AS "SpecialChargeReference",
TXCD AS "TaxCode",
-TRAM AS "Amount",
OFFC AS "Company",
TRIM(OFFA) AS "GL_Account",
IOFF AS "InvalidFlag",
'Offset' AS "AmountType",
TRIM(OFFC)||'-'||TRIM(OFFA)||'-1' AS "GL_AccountID"
FROM AMFLIB.OEITXN
I have the exact same problem. I just spent the morning recreating an SSIS package, because MS would not let me edit it.
ReplyDeleteI just spent an hour fighting with this, and I have to recreate the entire report, apparently.
You do have duplicate field names.
ReplyDeleteWay to point it out and actually help the guy
DeleteLOL... CompanyNumber is duplicated.
Delete