SQL Run query - Missing provider Error

[Piotr_Gajewski]

Hi, after migrating from “Windows Legacy” to “Windows” project in Uipath, I have a problem with connecting to our Sql database. There is an error with Sql Provider, which was previously working:

I have read that this connected with new .Net settings, so I’ve tried to import that namespace again and also, I have imported “Microsoft.Data.SqlClient”:

But after that, I’ve started to getting another error:

So I don’t know what is the problem here, no Sql settings were changed.
How can I overcome this error in new Studio projects, since I need to connect to our Database?

[loginerror (Maciej Kuźmicz)]

Hi @Piotr_Gajewski

Have you also updated the Database package to the latest 1.7.1 version?

[Piotr_Gajewski]

Hi Maciek, yes, database packages are up to date (v1.71).

[loginerror (Maciej Kuźmicz)]

Could you please double check that this is set like so:

[marian.platonov]

Create a new Connect to Database activity in the Windows project compatibility and use the Wizard to build again the Microsoft.Data.SqlClient connection.

Let us know the results.

[Piotr_Gajewski]

So I have double checked imports and they look good:

I also tried to use “Connect to database” activity, but with no success:

Error message: “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SNI_PN11, error: 26 - Error Locating Server/Instance Specified)”

Another thing that I’ve just noticed when checking console/output during the compilation, there was an error (which is not stopping the program to compile). I’m not sure what is means and how to solve it, but it seems, thats it is connected with sql activity somehow:

[loginerror (Maciej Kuźmicz)]

Hi @Piotr_Gajewski

Were you able to overcome these errors?

If not, it might be a good idea to clear the %userprofile%\.nuget\packages folder (or rename it for a moment) and allow Studio to redownload the dependencies. It could be that some dependencies were not downloaded correctly and that is causing you these headaches.

[Piotr_Gajewski]

Hi Maciek,

finally i have some time so i went back to the sql provider issue, I already deleted all the files in said folder (%userprofile%.nuget\packages), updated all packages in my libraries (windows project) but still with no luck.

I have some new tips - while compiling my libraries, I noticed the below errors, which I believe are related to this issue:

---

01/09/2023 16:14:17 => [Info] INITIALIZATION: Validating arguments.
01/09/2023 16:14:17 => [Debug] INITIALIZATION: Loading dependencies.

01/09/2023 16:14:17 => [Error] Bad IL format. The format of the file ‘C:\Users\piotr.gajewski.nuget\packages\UiPath.database.activities\1.7.1\lib/net5.0-windows7.0/runtimes/win-x64/native/Microsoft.Data.SqlClient.SNI.dll’ is invalid.

01/09/2023 16:14:18 => [Error] Assembly with same name is already loaded
01/09/2023 16:14:18 => [Error] Assembly with same name is already loaded
01/09/2023 16:14:18 => [Error] Assembly with same name is already loaded

01/09/2023 16:14:18 => [Debug] INITIALIZATION: Loaded dependency assembly ‘C:\Users\piotr.gajewski.nuget\packages\azure.core\1.18.0\lib/net5.0/Azure.Core.dll’.

---

and also:

01/09/2023 16:14:18 => [Debug] INITIALIZATION: Loaded dependency assembly ‘C:\Users\piotr.gajewski.nuget\packages\UiPath.database.activities\1.7.1\lib/net5.0-windows7.0/UiPath.Database.dll’.

01/09/2023 16:14:18 => [Debug] INITIALIZATION: Unable to load the specified dependency ‘C:\Users\piotr.gajewski.nuget\packages\UiPath.database.activities\1.7.1\lib/net5.0-windows7.0/runtimes/win-x64/native/Microsoft.Data.SqlClient.SNI.dll’. Reason was: ‘LoadFromFailure’.

01/09/2023 16:14:18 => [Debug] INITIALIZATION: Loaded dependency assembly ‘C:\Users\piotr.gajewski.nuget\packages\closedxml\0.95.4\lib/netstandard2.0/ClosedXML.dll’.

---

So there are 4 errors during the libraries compilation process and 1 note about unable to load dependencies. Any further help is greatly appreciated. Peter

[loginerror (Maciej Kuźmicz)]

@Piotr_Gajewski

01/09/2023 16:14:17 => [Error] Bad IL format. The format of the file ‘C:\Users\piotr.gajewski.nuget\packages\UiPath.database.activities\1.7.1\lib/net5.0-windows7.0/runtimes/win-x64/native/Microsoft.Data.SqlClient.SNI.dll’ is invalid.

This error does indeed provide a small hint. I’ve seen this here and the solution looked to be to install a 64 bit driver for the connection:

Database Connect Activity Broken when Migrating to Windows activity

Hi Maciej, Good guess on the driver, for me it was that the issue. Never thought of checking the version because we have been using the 32 bit with legacy and it was setup a long time ago. Did this just happen to be the case with this activity set (UiPath.Database) or are generally all newer activities Net5+ using 64 bits as a default? Just curious. @taxguy33 I have mine setup on the System, however I use the Oracle Client 19c at the machine level. Might not be your setup. Oracle Database 19c…

[Piotr_Gajewski]

Hi, so I installed Oracle drivers and also .Net 4.8 drivers but that did not solve the issue, the “Bad IL format” error still exists, however I was experimenting with Connection String and finally I found out that this was a problem!

I have used below pattern and it’s worked:
“Data Source=IpAddress\optima,PortNumber;Initial Catalog=master;User ID=varDataUser;Password=varDataPassword;Trust Server Certificate=true”

where:

• IpAddress is like: 192.xyz.x.xyz
• PortNumber is like: 12345
• varDataUser: User login
• varDataPassword: User password

Last parameter was crucial: “Trust Server Certificate=true” as it was connected with SSL protocol, which by default was turned off in old UiPath activities but turned on in new (Windows projects) activities.

Thanks everyone for support! Peter