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

[JeanMartinL]

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...

[v-chuncz-msft]

@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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Hi @Anonymous , 

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

[Anonymous]

Hi @Anonymous ,

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

thanks

Arman

[v-chuncz-msft]

Hi everyone,

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