CODEWITHSUNDEEP
.netsql-serversqlclrsqlbulkcopy
Stackoverflowuser
Stackoverflowuser

Reputation: 243

Error when importing data from Excel to SQL Server with CLR

SQL DBA here, not a C# or a programmer guy. Below is some code I found online and tried to adapt to my needs:

public partial class StoredProcedures

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void ExcelTransfer(String FileName, String WorkBook, String TableName)

    {

        using (SqlConnection cn = new SqlConnection("context connection = true"))

        {

            cn.Open();

            // Connection String to Excel Workbook

            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES'";

            // Create Connection to Excel Workbook

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))

            {

                OleDbCommand command = new OleDbCommand("Select Number FROM [" + WorkBook + "$]", connection);
                connection.Open();

                // Create DbDataReader to Data Worksheet

                using (DbDataReader dr = command.ExecuteReader())

                {

                    // Bulk Copy to SQL Server

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cn))

                    {

                        bulkCopy.DestinationTableName = TableName;

                        bulkCopy.WriteToServer(dr);

above code is registered in SQL Server with:

CREATE ASSEMBLY ImportFromFile_XLS
FROM '<path to .dll>'
WITH PERMISSION_SET = UNSAFE
GO

CREATE procedure readExcel (@str1 nvarchar(255), @str2 nvarchar(255), @str3 nvarchar(255))
AS
EXTERNAL NAME ImportFromFile_XLS.StoredProcedures.ExcelTransfer

When I execute

EXEC dbo.readExcel 'C:\SQL_DATA\WorkBook.xls', 'WorkBook', 'testTable'

I get the error message:

Msg 6522, Level 16, State 1, Procedure dbo.readExcel, Line 0 [Batch Start Line 46] A .NET Framework error occurred during execution of user-defined routine or aggregate "readExcel": System.InvalidOperationException: The requested operation is not available on the context connection. System.InvalidOperationException: at System.Data.SqlClient.SqlBulkCopy.CreateOrValidateConnection(String method) at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DbDataReader reader)
at StoredProcedures.ExcelTransfer(String FileName, String WorkBook, String TableName) .

What am I doing wrong here?

Upvotes: 2

Views: 147

Answers (3)

Solomon Rutzky
Solomon Rutzky

Reputation: 48874

As others have mentioned in comments, Context Connection is not valid for using with SqlBulkCopy, as noted in the documentation — Context Connections and Regular Connections - Restrictions (noted by @AlwaysLearning). A standard connection string is required.

HOWEVER, the documentation and comments are only partially correct. The Context Connection is only invalid for the destination connection. It is, on the other hand, valid for the source connection. That won't help here as the connection in question is the destination, but it should still be documented (and if I get a chance, I will update the Microsoft documentation to be more specific).

Upvotes: 1

Stackoverflowuser
Stackoverflowuser

Reputation: 243

Change the connection from context connection to a real connection string as per suggestions above from AlwaysLearning and siggemannen.

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22283

No need in any CLR for your scenario. Just plain vanilla T-SQL.

The only precondition is that Microsoft ACE OLEDB provider needs to be installed. You need just one of the following three versions 12.0, 15.0, or 16.0.

The fully qualified path "C:\SQL_DATA\WorkBook.xlsx" is relative to the SQL Server instance server.

T-SQL

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
   'Excel 12.0 Xml; HDR=YES;
    Database=C:\SQL_DATA\WorkBook.xlsx',
    [Sheet1$]);

Upvotes: 1

Related Questions