Upgrade to 6.3 => NotImplementedException: Cannot derive parameters for the stored procedure SELECT_Objektberechtigungen. Have you loaded the provider that supports SqlCommand?

[madrianr]

Describe the bug

After upgrading to 6.3 I got the following error: NotImplementedException: Cannot derive parameters for the stored procedure SELECT_Objektberechtigungen. Have you loaded the provider that supports SqlCommand?

Insight.Database.Providers.InsightDbProvider.DeriveParametersFromStoredProcedure(IDbCommand command)

Steps to reproduce

Upgrade Package from 6.2.11 to 6.3 and start...

Expected behavior

no error

• Dotnet version: [netcore2.2]
• Database: [SQL Server]
• Library version: [6.3]

[Jaxelr]

Could you post a snippet reproducing the error?

[Romuald-Szymanski]

I have the same issue.

I wrote a snippet in LinqPad to show how to use TVP with Insight.Databse:

async Task Main()
{
    SqlInsightDbProvider.RegisterProvider();
    using var database = new SqlConnection(@"Data Source=192.168.1.5\DW;Initial Catalog=DWNortia;User Id=sa;Password=***;");

    IEnumerable<int> idApporteurs = new[] { 5, 7, 9 };
    var contrats = await database.QueryAsync<Contrat>("penelop.GetContrats", new { apporteurs = idApporteurs });
    contrats.Dump();

}

public class Contrat
{
    [Column("ID_Contrat")]
    public int Id { get; set; }

    [Column("Numero_Contrat")]
    public string NumeroContrat { get; set; }
}

With this kind of T-SQL code

CREATE TYPE penelop.ListeId AS TABLE 
(
    Id INT NULL
);

CREATE PROC penelop.GetContrats
    @apporteurs penelop.ListeId READONLY
AS
    SET NOCOUNT ON;

    SELECT  ID_Contrat, Numero_Contrat
    FROM    dbo.DIM_Contrats contrats
            JOIN @apporteurs apporteurs
                ON contrats.ID_Apporteur = apporteurs.Id
    WHERE contrats.TEK_datesuppression IS NULL;

    SET NOCOUNT OFF;
GO

It works in 6.2.11 but fails in 6.3 with the error described. Hope this helps.

[Jaxelr]

Is this on dotnetcore? which version? Are you using System.Data.SqlClient, if so which version?

I am having trouble reproducing the error, the following linqpad code works for me:

void Main()
{
    var conn = new SqlConnection(MyExtensions.SQLConnectionString);
    SqlInsightDbProvider.RegisterProvider();

    var apporteurs = new List<int>() { 1, 2 };

    try
    {
        var response = conn.Query<Contrat>("GetContrats", apporteurs);

        response.Dump();
    }
    catch (Exception e)
    {
        e.Dump();
    }
}

public class Contrat
{
    public int Id { get; set; }

    public string NumeroContrat { get; set; }
}

Using this sql:

DROP PROCEDURE IF EXISTS GetContrats
DROP TYPE IF EXISTS ListeId
CREATE TYPE ListeId AS TABLE 
(
    Id INT NULL
);
GO

DROP TABLE IF EXISTS DIM_Contrats
CREATE TABLE DIM_Contrats
(
    ID_Contrat int,
    Numero_Contrat varchar(25)
)
GO

CREATE OR ALTER PROC GetContrats @apporteurs ListeId READONLY
AS
    SET NOCOUNT ON;

    SELECT  ID_Contrat Id, Numero_Contrat NumeroContrat
    FROM    dbo.DIM_Contrats contrats
    JOIN @apporteurs apporteurs
        ON contrats.ID_Contrat = apporteurs.Id

GO

INSERT INTO dbo.DIM_Contrats(ID_Contrat, Numero_Contrat)
SELECT 1, '123456'
INSERT INTO dbo.DIM_Contrats(ID_Contrat, Numero_Contrat)
SELECT 2, '654321'

DECLARE @list as ListeId;

INSERT INTO @list 
SELECT 1 UNION 
SELECT 2;

EXEC GetContrats @list --This is just a test to show it works as intended prior to using insight

I am using:

netcore 3.1 Insight.Database 6.3.0 System.Data.SqlClient 4.8.1 SQL Server 2017 (14.0.2027.2)

[Romuald-Szymanski]

My bad ! Linqpad uses System.Data.SqlClient 4.2.0 by default. And it fails with Insight.Db 6.3 But the same configuration in VS2019 works...

Forcing System.Data.SqlClient to 4.8.1 or Microsoft.Data.SqlClient 1.10.19324.4 works like a charm with Insight.Db 6.3 in LinqPad (and VS). Don't know what happened, but it works.

Thanks !

[Jaxelr]

Great to hear, i am closing this one, if you have any other trouble feel free to reach out.

[yegor-n-a]

@jonwagner , @Jaxelr , I encountered exactly the same issue when I was trying to upgrade last week.

Typically, before upgrading, I check the ChangeLog and analyze breaking changes which might be introduced in the current release.

At the moment, ChangeLog looks neglected and does not help very much with such issues...

Is it possible to resurrect updating of ChangeLog?

Thank you.

[madrianr]

I have to stay on V 9.2 because I use Microsoft.NETCore.App 2.2 SDK and there I cannot Forcing System.Data.SqlClient update

do I have to wait until we have upgraded to NET 3.1?

robert

[Jaxelr]

@madrianr Version 9.2 of what library?

[madrianr]

sorry typo -> I use 6.2.11

[jonwagner]

v6.3 added support for the new Microsoft.Data.SqlClient and it's installed when available, but we didn't remove the old System.Data.SqlClient. Existing code should work as-is.

Adding the new provider shouldn't have affected how providers are detected and automatically loaded. I checked for anything that would obviously break that mechanism. Environments such as LinqPad that manage dependencies and assemblies in a non-standard way will often require manually registering a provider. However, standard application packaging where all of the assemblies are in the same folder shouldn't be an issue. I also can't see how that would have broken in 6.3 but I don't know all environments.

@yegor-n-a Can you tell us more about your application structure? Specifically:

• Are you calling RegisterProvider in application startup?
• What type of application do you have? ASP.NET? Executable process?
• When the application is compiled and deployed, what is the structure of the bin directory and where are all of the Insight DLLs installed?

[kegfire]

@jonwagner I have the same problem. You can reproduce this problem in Console app.

1. create an empty Console app.
2. change target platform to .net core 2.2
3. add the last version of insight.Database package (6.3.2)
4. try to get some data from DB

If change the target platform to .net core 3.1 the error disappears.

[jonwagner]

Well, that's really interesting. Not sure how that would happen but it should be easy to reproduce and fix.

[kegfire]

@jonwagner May I hope that the problem will be fixed in the near future?

[jonwagner]

I put it on my chore list for an upcoming weekend. If someone comes and rakes my leaves I'll definitely get to it quickly. :)

[madrianr]

I put it on my chore list for an upcoming weekend. If someone comes and rakes my leaves I'll definitely get to it quickly. :)

[jonwagner]

@madrianr / @kegfire - I created a HelloWorld project in the main branch under netcore2.0 and it seems to run fine. (I'm running on fedora so maybe there's an issue there.) Can you check out that code and get it to break?

Just 2 files and runs fine with dotnet run against my local db.

[jonwagner]

Ah yeah maybe asp.net. Might manage it’s assemblies slightly differently. Let me try that.

On Nov 7, 2020, at 11:36 AM, madrianr notifications@github.com wrote:



Create a empty ASP.NET Core WebApplication - it seems the problem might be in WebApplication (Microsoft.AspNetCore.App??)

[webAppl]https://user-images.githubusercontent.com/10518186/98446620-f51d3680-211e-11eb-947f-bed1e9de54f9.JPG

I cannot run that solution (compiling without error!) - get:

[image]https://user-images.githubusercontent.com/10518186/98446672-61983580-211f-11eb-9673-4cbfe38a032c.png

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/jonwagner/Insight.Database/issues/441#issuecomment-723466541, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAMTO5EVDKHPCXQA2JCOLDTSOVZQNANCNFSM4N7QTKOQ.

[madrianr]

you have to call a Stored Procedure and then you get the error "otImplementedException: Cannot derive parameters for the stored procedure XXX. Have you loaded the provider that supports SqlCommand?"

[jonwagner]

Alright I think I can reproduce it now. It seems that netcoreapp2.0 isn't copying dependency dlls into the bin directory, and that's how Insight auto-registers providers.

[jonwagner]

dotnet publish DOES copy the assemblies to the publish folder.

[jonwagner]

In addition to my screw up (more to come below!)

This is related to:

https://github.com/dotnet/sdk/issues/9197

This issue is documented in netcore2.x in some environments, and fixed in netcore3.0. This issue is closed and netcore2.x is end of life, so I don't expect it to be fixed there.

This tells me that netcore2.x does some special assembly-loading magic when you're running locally with dotnet run vs dotnet publish. It's likely that it's using the additionalProbingPaths in runtimeconfig.json, but leaving all of the Insight assemblies in their own folders. Implementing code to handle this scenario would be pretty brittle and would be specific for netcore2.x and unnecessary for netcore3.0.

Given that this is a scenario specific to netcore2.x, which is already deprecated, I'm not going to take the time to develop a workaround for auto-registration.

So in netcore2.x, you'll need to register the providers manually at code startup.

        static void Main(string[] args) // or whatever
        {
            SqlInsightDbProvider.RegisterProvider();

[jonwagner]

And here's the thing that changed in v6.3. With the addition of Microsoft.Data.SqlClient support, there are now two very similar providers, and the names got mixed up:

In 6.3:

Insight.Database.Providers.SqlInsightDbProvider.SqlInsightDbProvider => System.Data
Insight.Database.SqlInsightDbProvider => Microsoft.Data

So if you had old code I swapped the providers on you, but you probably didn't change your drivers. That wasn't the intent.

Moving ahead we'll go with:

Insight.Database.SqlInsightDbProvider => System.Data to match historical code
Insight.Database.Providers.MsSqlClient => Microsoft.Data to match the pattern for all "non-system" providers

Bringing it back to auto-registration - if you aren't on netcore2.0, we load all of the providers anyway and it just worked. Sometimes magic is hard to debug.

[kegfire]

@jonwagner Thank you for this investigation!

[madrianr]

Thanks

[jonwagner]

WHEW. 6.3.3 is out and all of this should be fixed.

[yegor-n-a]

WHEW. 6.3.3 is out and all of this should be fixed.

@jonwagner Thank you for your time and efforts. Your contributions are greatly appreciated!

P.S. Happy New Year!

[marktilleytlc]

FYI. I have an Azure function app using .Net Core 3.1, Insight.Database 6.3.8, Microsoft.Data.SqlClient 3.01 and had the issue and error message described above. Adding a call to SqlInsightDbProvider.RegisterProvider() fixed it.

[jesserizzo]

I'm still running into this problem with Dotnet 6 and Insight.Database 6.3.9 and an Azure function app. I tried calling RegisterProvider and that didn't help.

[jonwagner]

Best to post a new code sample so we can diagnose

[jesserizzo]

Ok, I figured it out. I needed to call Insight.Database.Providers.MsSqlClient.SqlInsightDbProvider.RegisterProvider();