C# can't get datas from stored procedure with opendatasource and Microsoft.ACE.OLEDB.16.0-CodePudding

I have a strange issues reading data from an Eexcel file. I made a query in SQL Server with OPENDATASOURCE:

    SELECT COMPETENZA, [CODICE CLEINTE], [RAGIONE SOCIALE], MODELLO, VARIANTE, 
  TIPOLOGIA, ESSENZA, FINITURA, TAPPEZZERIA, COMMESSA, ROUND([% MOL SU RICAVI NETTI] * 100,2) as MARGINE
  FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0', 
   'Data Source=K:\UTENTI\SAMUELE\temp id commesse\ID Commesse produzione.xls;Extended Properties=EXCEL 12.0')...['Consuntivi produzione$']
  WHERE MODELLO IS NOT NULL AND [% MOL SU RICAVI NETTI] IS NOT NULL

If I execute this query in a stored procedure, it works fine.

But when I execute from C# by an HttpGet I get this issues:

System.Data.SqlClient.SqlException (0x80131904): Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)".

This is my C# code:

[HttpGet]
    public async Task<JsonResult> GetMarginiCommessaListJSON()
    {
        var yourdata = GetMarginiCommessaList();

        return Json(new { data = yourdata });
    }

    public List<MarginiCommessa> GetMarginiCommessaList()
    {
        SqlDataReader reader;
        SqlConnection sqlConn = new SqlConnection(_configuration["dbConnectionString"]);
        string sqlCmdStr = "MARGINI_COMMESSE";
        SqlCommand sqlCmd = new SqlCommand(sqlCmdStr, sqlConn);
        sqlCmd.CommandType = CommandType.StoredProcedure;
        List<MarginiCommessa> allestList = new List<MarginiCommessa>();

        try
        {
            using (sqlConn)
            {
                sqlConn.Open();
                reader = sqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    allestList.Add(new MarginiCommessa
                    {
                        anno = reader.GetValue(0).ToString(),
                        codCli = reader.GetValue(1).ToString(),
                        nomCli = reader.GetValue(2).ToString(),
                        modello = reader.GetValue(3).ToString(),
                        variante = reader.GetValue(4).ToString(),
                        tipologia = reader.GetValue(5).ToString(),
                        essenza = reader.GetValue(6).ToString(),
                        finitura = reader.GetValue(7).ToString(),
                        tapezzeria = reader.GetValue(8).ToString(),
                        commessa_qta = reader.GetValue(9).ToString(),
                        margine = reader.GetValue(10).ToString()
                    });
                }
                reader.Close();
                sqlConn.Close();
            }

        }
        catch (Exception e)
        {
            System.IO.File.WriteAllText(GlobalVariables.errorFolderLocation   "GetMarginiCommessaListJSON.txt", e.ToString());
        }

        return allestList;
    }

And this is my Stored procedure:

ALTER PROCEDURE [dbo].[MARGINI_COMMESSE]
AS
BEGIN
    SET NOCOUNT ON;

  SELECT COMPETENZA, [CODICE CLEINTE], [RAGIONE SOCIALE], MODELLO, VARIANTE, 
  TIPOLOGIA, ESSENZA, FINITURA, TAPPEZZERIA, COMMESSA, ROUND([% MOL SU RICAVI NETTI] * 100,2) as MARGINE
  FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0', 
   'Data Source=K:\UTENTI\SAMUELE\temp id commesse/ID Commesse produzione.xls;Extended Properties=EXCEL 12.0')...['Consuntivi produzione$']
  WHERE MODELLO IS NOT NULL AND [% MOL SU RICAVI NETTI] IS NOT NULL
  
END

I really don't understand why in SQL Server it works fine and in C# not. How can I fix this?

CodePudding user response：

OP #1:

I have a strange issues reading datas from an excel file. I made a query in mssql with OPENDATASOURCE: If i execute this query in a stored procedure, it works fine.

OP #2:

But when i execute from c# by an HttpGet i get this issues:

Firstly, that's an apples and oranges comparison. On one hand you are using MySQL and the other SQL Server. The latter case you also have a web app using ADO.NET in order to reach the stored proc.

Now I can't vouch for MySQL or whatever OPENDATASOURCE is doing in both occasions but one thing is certain - you appear to be using ACE OLEDB "Jet" in a server setting which may be the cause of your issues. Crashes aside, using ACE might have licensing issues. See below.

Microsoft (my emphasis):

Although such programmatic development can be implemented on a client system with relative ease, a number of complications can occur if Automation takes place from server-side code such as Microsoft Active Server Pages (ASP), ASP.NET, DCOM, or a Windows NT service.

NOTE: In this context, the Access Database Engine Redistributable and Access Runtime are considered Microsoft Office components.

...and:

Developers who try to use Office in a server-side solution need to be aware of five major areas in which Office behaves differently than anticipated because of the environment.

...and most importantly (my emphasis):

Besides the technical problems, you must also consider licensing issues. Current licensing guidelines prevent Office applications from being used on a server to service client requests, unless those clients themselves have licensed copies of Office. Using server-side Automation to provide Office functionality to unlicensed workstations is not covered by the End User License Agreement (EULA).

Conclusion

So even if you fix the technical issue, be mindful of that last pointer.

Conclusion

See also