cancel
Showing results for 
Search instead for 
Did you mean: 

BUG: Report Server refresh fails if data source are created differently

On Report Server October 2020 with November 18th Patch

Reproduction Steps:

1) Create a report with 2 tables using SQL Server data sources

2) Modify the data source of 1 table to declare the hostname/db name on 2 lines instead of 1.

One table should look like this:

 

Source = Sql.Database("HOSTNAME", "DATABASENAME"),

 

And the other table should look like this:

 

Source = Sql.Databases("HOSTNAME"),
DATABASENAME = Source{[Name="DATABASENAME"]}[Data],

 

3) Use a merge operator to join the 2 tables (no need to expand it)

 

Table.NestedJoin(#"Removed Other Columns", {"COL1"}, #"T1", {"COL1"}, "T2", JoinKind.LeftOuter)

 

4) Refresh the report, it works correctly in Desktop

5) Deploy the report on Report Server and refresh it, you will get the following error:

 

[0] -1056505856: COM error: mscorlib, An item with the same key has already been added..
[1] -1055129594: The current operation was cancelled because another operation in the transaction failed.
[2] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.

 

 

Changing the data sources in step #2 to use the same method (e.g. declaration on 1 line) fixes the issue.

Edit: More details in this thread: https://community.powerbi.com/t5/Report-Server/COM-error-mscorlib-An-item-with-the-same-key-has-alre...

Status: New
Comments
Community Support

@JeanMartinL 

 

Thanks for sharing the workaround. To get faster and better technical support for this issue, you may directly leave a comment on this post.

 

Microsoft

Thanks @JeanMartinL, I was able to build a repro based on your instructions.

New Member

I have the same issue when combining (Append) 2 Queries that are using native query on the same database.

 

Both queries have the same format and only 1 step

 

Query 1

Sql.Database(Server, DB_Name, [Query="SELECT UserKey,  RoleKey, StartDate FROM UserRoles Where RoleType = 'Admin'])

Query 2

Sql.Database(Server, DB_Name, [Query="SELECT UserKey,  RoleKey, StartDate FROM ManagerRoles Where RoleType = 'Admin'"])

If I just keep these 2 datasets, everything is fine with refresh.

 

But issue appears when I combine these 2 queries as below

Table.Combine({Query1, Query2})

I get this error when refresh is failed

"COM error: mscorlib, An item with the same key has already been added.  "

 

Could you please check this

Microsoft

Hi @armando86 , 

 

can you confirm that you are using the updated version released on 11/18? This sounds similar to an issue fixed with that release.

 

 Thanks

New Member

Hi @gboreki ,

 

Yes we applied the latest patch and it fixed issues with Scheduled Refresh except for this one.

 

thanks

Arman

Community Support

Hi everyone,

 

That's a known issue and will be investigated for a fix in the January 2021 PBIRS release.