Error "An item with the same key has already been added" when selecting Quick Connect (Oracle or Microsoft SQL Server)

[ntobon]

Issue

When selecting Oracle Quick Connect or Microsoft SQL Server Microsoft SQL Server, the following error occurs:
 

An item with the same key has already been added

image.png
 

Environment

• Alteryx Designer
  • Version 2021.3
• Oracle / Microsoft SQL Server
  • All versions

Cause

There are connections with duplicate name.

Resolution

This behavior is not reproducible in the Designer version - 22.3.
 

Workaround

Remove one or more data connections to resolve the duplication.

[ntobon]

Quick Connection functionality is affected due to the same cause. If a user selects Options, Advanced Options, Manage Data Connections, and selects Add Connection, Oracle, nothing happens.

[wildflower]

@ntobon I'm currently on 2021.3.2 Designer. Can you please lay out the steps for Workaround #2 - Where would I need to 'remove one or more data connections'? In the Managed Connections window within Designer, I do not see any User type connections, just gallery.

Workaround #1 has not worked for me, even after removing all XMLs. The xml files I have are slightly named differently as well:

• C:\ProgramData\Alteryx\Engine\SystemConnections.xml
  • Removal didn't work so I tried renaming it to SystemAlias.xml and that also didn't work.
• %USERPROFILE%\AppData\Roaming\Alteryx\Engine\UserSettings.xml

In regards to the Notes section, "the files are corrupted. Repeat the resolution steps above and create the connections again from the Alteryx GUI."

How can you create the connections via the Alteryx GUI if the error persists?

[ntobon]

@wildflower  Do you have see two or more Gallery connections with the same name? If so, that is the cause of the issue.  Gallery connections are created in Galley Admin.  Please ask you Gallery Admin to Delete the connection with duplicate name, so keep one with unique name. 

[wildflower]

@ntobon Thank you so much for your response! We name our connections after the DB name on gallery for both our UAT and Prod galleries, so there are 'duplicates' only under the different environments (that I can see). I had to remove both galleries and the GalleryAlias.xml in order for both Quick Connects to work again. 

1. Removed Production gallery, all of its connections are gone so there are no more 'duplicates'. 
2. Relaunched Designer > drag input tool > Error message when I select Quick Connect to set up SQL Server or Oracle connection.
3. Removed the UAT gallery so that there are no gallery connections whatsoever. 
4. Relaunched Designer > Error message when I select Quick Connect to set up SQL Server or Oracle connection.
5. Removed %USERPROFILE%\AppData\Roaming\Alteryx\Engine\GalleryAlias.xml
6. Success! Both SQL and Oracle Quick Connects were successful!
7. Readded Prod gallery > Success with SQL Server but not Oracle! I'm unsure if I can pinpoint which Oracle connections are 'duplicates' as they all appear unique.
8. Readded UAT gallery > both break.

At least I now have a workaround whenever I need to add a new local connection! Thank you for your insight.

Did something change for Designer/server, I thought that the Datasource IDs were unique for each connection, not the alias. E.g. this is one of the gallery connections that have the same name but different IDs on UAT and Prod galleries.

[ntobon]

@wildflower  the Datasource IDs are unique for each connection. The cause of the issue is the duplicate name.

[GauravGarg]

I used Alteryx designer itself to find out the duplicate key.

I dragged these files to the designer

• %USERPROFILE%\AppData\Roaming\Alteryx\Engine\UserAlias.xml
• %USERPROFILE%\AppData\Roaming\Alteryx\Engine\UserConnections.xml
• %USERPROFILE%\AppData\Roaming\Alteryx\Engine\GalleryAlias.xm

Found duplicate in UserAlias.xml using the Unique tool under Preparation menu on the designer (D is duplicates, U is unique).  Checked for duplicates on Name and CosmeticName

Alteryx showed me one duplicate in the UserAlias.xml

Then I removed that name from the Options->AdvancedOptions->Manage Data Connections and it resolved the issue.

[arunkumark_21]

Hi, 

For me this issue is happening even in a fresh canvas with no other tool.

[ntobon]

@arunkumark_21  Workaround #1 not always work. Workaround # 2 always works, please try that one. 

[wildflower]

@GauravGarg - My UserAlias.xml and UserConnections.xml have no duplicates. My GalleryAlias would only have duplicates if I added both my UAT and Production Galleries to the managed connections. Databases are named the same in certain Test and Production environments in our company so we've always named the Gallery connections after the DB.

I find these suggested workarounds to be very convoluted and doesn't address root cause. This wasn't an issue prior to moving to 2021.3 Designer and persists to the latest Designer version that we're on now, 2022.3. What changed is my question. And why change a good thing.

[arunkumark_21]

@wildflower  The comment you posted to Gaurav is exactly my problem. We have same names in both Testing and Prod Server gallery connection. And it is not possible to rename/remove connections from the server since many jobs are running in prod using these connection.

Not sure why Alteryx decided to check for Connection name in designer when they have connection Id which is unique!

[javierpicoruiz]

There are NO connections with duplicate name and still have the error. 

I drop an input, select oracle quick connect, and the message appear. 

My version is 2023.1