0

The following script works within a SQL Server 2014 Management Studio in a Stored Procedure but not when I call the stored proc via a C# app .NET Framework 4.8. SQL Code:

create proc getData
as
Insert INTO tmpLeaveImport ([CarName], Year, Make , Model) 
    SELECT *  FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0',
    'Excel 12.0;Database=E:\Cars\Cars.xlsx',
    'SELECT * FROM [Car Report$]')

From C# I get the following error:

System.Data.SqlClient.SqlException: 'Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)". OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)" returned message "Unspecified error".'

When this code is executed in C#:

  //Tried the conn strign with Integrated Security=yes and SSPI
  string ConnString = @"Data Sournce=MySQLServerDB;Initial Catalog=DBName;Integrated Security=true;";
  using (SqlConnection conn = new SqlConnection(ConnString))
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                conn.Open();
                cmd.CommandText = "getData";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
            }
            conn.Open();
        }
Danimal111
  • 1,976
  • 25
  • 31
  • The code is calling a different stored procedure in the second case. Are you sure `importText` works? Perhaps C# connects to a different server? Both queries run on the server, not C#. SQL queries are sent to the server for execution, they aren't executed by C#. It's SQL Server that complains about this call. – Panagiotis Kanavos Sep 15 '20 at 15:02
  • SSMS is nothing but a client tool anyway. It doesn't execute the queries either, it sends them to the server for execution. Perhaps the wrong stored procedure was use? Perhaps `MySQLServer` doesn't have the correct ACE version installed, as the error complains? Perhaps the *account* used to run the queries in each case has restrictions and can't load the ACE driver? – Panagiotis Kanavos Sep 15 '20 at 15:05
  • Thanks @PanagiotisKanavos I edited the sproc names to not reveal data and forgot to change it in all locations. I am sure the stored procedure works, as I can run it in SSMS and it imports the rows from the xlsx. It does not work when I run it via C#. – Danimal111 Sep 15 '20 at 15:33
  • 1
    FWIW do not use OpenRowSet() but SqlBulkCopy from C#. – Cetin Basoz Sep 15 '20 at 15:34
  • 1
    @DanB again, you don't run it via C#. You run it on SQL Server. The request is sent by C#, using the connection string and credentials you provide. If the *account* has no permissions to load that OLEDB driver, `OPENROWSET` will fail. If you google for this error you'll find several possible duplicates – Panagiotis Kanavos Sep 15 '20 at 15:39
  • 1
    I'd use a library like [xcelDataReader](https://github.com/ExcelDataReader/ExcelDataReader) to read the Excel data and send it to the server with SqlBulkCopy instead. ACE is finicky, and Excel is *not* a database. You may get into trouble if you install the x86 version of the driver as SQL Server is typically installed as a x64 service. You may not even be able to install the correct version if another Office component targeting a different architecture is used. – Panagiotis Kanavos Sep 15 '20 at 15:42
  • Is the Excel file open perhaps? – Panagiotis Kanavos Sep 16 '20 at 06:44

1 Answers1

1

Short Version

According to this possibly duplicate question the Excel file may be open. Or this could be a more serious error.

Don't use OPENROWSET to import Excel data into SQL Server. Use a library like ExcelDataReader to read it without using the Access Engine and insert it to the target table with SqlBulkCopy. You'll avoid a lot of pain.

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        using (var bcp = new SqlBulkCopy(connString))
        {
            bcp.DestinationTableName ="SomeTable";
            bcp.WriteToServer(reader);
        }
    }
}

Long Version

In both cases, the stored procedure runs on SQL Server, not on the client. SSMS is just another client as far as SQL Server is concerned. Assuming the same server is used in both cases, what's different is that the account that executes the stored procedure is different in each case.

With SSMS, it's the developer's account which quite often has sysadmin privileges on the server. With C#, the account may be the end user's, or the application pool account that runs a web site, which has very restricted privileges. SQL Server's default service account is a restricted account too.

This matters because the Access Engine is a COM component. To use it, applications need to look it up in the registry, which requires its own permissions. If you search SO for the error you got you'll see questions where the choice of service account affected whether Access Engine can be used or not. In other cases, the file was open.

Another potential problem is that ACE must target the same architecture as any previous Office components installed on a machine. If you have a x86 Office application, you can only install the x86 version of ACE. That's because you can't use COM component created for one architecture from a process that targets another one.

This also means you can't use an x86 ACE in a x64 installation of SQL Server, which is the most common installation option in the last 10+ years.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • @PatagiotisKanavos, thank you for this detailed explanation! It got me going on the right path. I added my debugging steps to handle an encoding error. – Danimal111 Sep 16 '20 at 15:15