Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Dynamics Business Central migration forum / Unable to publish to B...
Dynamics Business Central migration forum
Suggested answer

Unable to publish to Business Central server with Microsoft.Data.SqlClient reference

(0) ShareShare
ReportReport
Posted on by KoenH76 5

Dear Sir/Madam,

We are experiencing problems when we use Microsoft.Data.SQLClient in our AL script after upgrading to v22.

The script compiles fine locally but fails to publish the package to the server.

It then returns the following error:

Reason: Extension compilation failed
src/PageExtension/HelloWorld.al(53,23): error AL0132: 'DotNet "Microsoft.Data.SqlClient.SqlCommand"' does not contain a definition for 'CommandTimeout'

and

src/PageExtension/HelloWorld.al(55,23): error AL0132: 'DotNet "Microsoft.Data.SqlClient.SqlCommand"' does not contain a definition for 'CommandText'

Does someone experience the same issues? 

Kind regards,

Koen van Hoesel

  • Suggested answer
    MJV365 Profile Picture
    MJV365 18 on at
    Unable to publish to Business Central server with Microsoft.Data.SqlClient reference
    Hi Koen,
    I was finally able to compile, publish and execute a stored procedure using Microsoft.Data.SqlClient.
     
    Here is what I did:
     
    Assembly Probing Path:
        "al.assemblyProbingPaths": [
            ".\\.netpackages",
          
            "C:\\Program Files\\dotnet\\shared\\Microsoft.AspNetCore.App\\6.0.15",
          
            "C:\\Program Files\\dotnet\\shared\\Microsoft.NETCore.App\\6.0.15",
          
            "C:\\Program Files\\dotnet\\shared\\Microsoft.WindowsDesktop.App\\6.0.15",
         
            "C:\\Program Files\\Microsoft Dynamics 365 Business Central\\220\\Service",
            
            "C:\\Program Files\\Microsoft Dynamics 365 Business Central\\220\\Service\\Add-ins"
          
          ]    

    in the dotnet.al file:
     
        assembly(Microsoft.Data.SqlClient)
        {
            Version = '5.0.0.0';
            Culture = 'neutral';
            //PublicKeyToken = '23ec7fc2d6eaa4a5';
            type(Microsoft.Data.SqlClient.SqlParameter; SqlParameter) { }
            type(Microsoft.Data.SqlClient.SqlConnection; SqlConnection) { }
            type(Microsoft.Data.SqlClient.SqlCommand; SqlCommand) { }
        }
     
    successful example:
     
        local procedure GetCryptoAccountNo(VendorNo: Code[20]) EncryptedAccountNo: Text[1000]
        var
            SQLParameter: DotNet SqlParameter;
            ConnectionString: Text[1024];
            SQLConnection: DotNet SqlConnection;
            SQLCommand: DotNet SqlCommand;
        begin
            VendorNo := '11106';
            ConnectionString := 'Data Source=MyServerName';
            ConnectionString := ConnectionString + ';Initial Catalog=MyDatabaseName';
            ConnectionString := ConnectionString + ';Integrated Security=True';
            ConnectionString := ConnectionString + ';TrustServerCertificate=True';
            SQLConnection := SQLConnection.SqlConnection(ConnectionString);
            SQLConnection.Open;
            SQLCommand := SQLCommand.SqlCommand('procGetEncryptedAccountNumber ' + VendorNo + ', KHProd'); //Constructor
            SQLCommand.Connection := SQLConnection;
            EncryptedAccountNo := SQLCommand.ExecuteScalar;
        end
     
  • MJV365 Profile Picture
    MJV365 18 on at
    Unable to publish to Business Central server with Microsoft.Data.SqlClient reference
    I notice on this post regarding this issue that they indicated that the problem is solved in BC22.3.
     
     
    I am still having the issue in 22.5, but I just notice I somehow got a Canadian build for 22.5, so I am wondering if that is making a difference.
     
        Version: CA Business Central 22.5 (Platform 22.0.60117.0 + Application 22.5.59966.60134)
     
     
    'DotNet "Microsoft.Data.SqlClient.SqlCommand"' does not contain a definition for 'CommandType'
    'DotNet "Microsoft.Data.SqlClient.SqlCommand"' does not contain a definition for 'CommandText'
    'DotNet "Microsoft.Data.SqlClient.SqlCommand"' does not contain a definition for 'CommandTimeout'
    'DotNet "Microsoft.Data.SqlClient.SqlParameter"' does not contain a definition for 'ParameterName'
    Argument 1: cannot convert from 'Integer' to 'DotNet "System.Data.SqlDbType"'
    'DotNet "Microsoft.Data.SqlClient.SqlParameter"' does not contain a definition for 'Direction'
    'DotNet "Microsoft.Data.SqlClient.SqlParameter"' does not contain a definition for 'Size'
    'DotNet "Microsoft.Data.SqlClient.SqlParameter"' does not contain a definition for 'Value'
     
     
  • MJV365 Profile Picture
    MJV365 18 on at
    Unable to publish to Business Central server with Microsoft.Data.SqlClient reference
    This link helped me to get it compiled and published.
     
     
    However, at runtime, BC22 (22.3) could not load the assembly
     
    If requesting support, please provide the following details to help troubleshooting:
    A call to <MyFunction> failed with this message: Could not load file or assembly 'System.Data.SqlClient, Version=0.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The system cannot find the file specified.
     
    Placing the .dll in the Sevice directory, the GAC, the Add-ins, and even inetpub.wwwroot.MyBC22WebClient did not resolve the issue.
     
    Probably BC22 cannot load any version of this .dll.
     
    My understanding is that they are using .net 6.0 for BC22.
     
     
     
  • Community member Profile Picture
    Community member 2 on at
    Unable to publish to Business Central server with Microsoft.Data.SqlClient reference
    Here is the same problem... 
    neither with System.Data.SqlClient nor Microsoft.Data.SqlClient 
    i have the same error : 
    src/Dotnets/dotnet.al(9,14): error AL0452: The type 'System.Data.SqlClient.SqlConnection' could not be found in assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
     
    there is someone that has solved ? 
     
  • MJV365 Profile Picture
    MJV365 18 on at
    Unable to publish to Business Central server with Microsoft.Data.SqlClient reference
    Hi Koen,
     
    I do not have an answer for you, but I have the same issue.
     
    I cannot find the Microsoft.Data.SqlClient on the server we compile the app on.  
     
    Do you know how to get it onto the machine?
     
    I have been using System.Data. for the Sqlclient since BC19, which also compiles on my machine.
     
    However, the server, when it compiles the app, is using a different System.Data which does not contain the SqlClient, so I need to get Microsoft.Data.SqlClient onto the machine.
     
    Thanks,
     
    Michael


  • Martí Sala Profile Picture
    Martí Sala 12 on at
    Unable to publish to Business Central server with Microsoft.Data.SqlClient reference
    Hi Koen!
    I have the same error! Did you find a solution to this?
     
     
    Thanks!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Community Spotlight of the Month

Kudos to Mohamed Amine Mahmoudi!

Blog subscriptions now enabled!

Follow your favorite blogs

TechTalk: How Dataverse and Microsoft Fabric powers ...

Explore the latest advancements in data export and integration within ...

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 284,876 Super User

#2
Ludwig Reinhard Profile Picture

Ludwig Reinhard Microsoft Employee

#3
Martin Dráb Profile Picture

Martin Dráb 225,425 Super User

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans