CODEWITHSUNDEEP
c#sql-serverstored-proceduresoledb
Malo
Malo

Reputation: 1566

C# calling stored procedure with OLEDB connection not working

I have a stored procedure which read data from eExcel file, when executing this stored procedure, it works well and read all the data and fill the SQL table

CREATE PROCEDURE Importdata2
AS
BEGIN
    DECLARE @sql NVARCHAR(50)

    SET @sql = 'SELECT * FROM [' + 'Sheet1' + '$]'

    INSERT INTO Branches (Description)
    EXEC (
            'select distinct  Department 
     FROM OPENROWSET(
    ''Microsoft.ACE.OLEDB.12.0''
     ,''Excel 12.0 Xml;Database=' + 'C:\ATTapp\ATToutput files\test.xls' + ';HDR=YES''
     ,''' + @sql + ''') where Department not in (select description from Branches)'
            )
END 
GO

I need to call this stored procedure from C# code like this:

try
{
    using (DBConnection con = new DBConnection())
    {
        SqlCommand command = new SqlCommand("ImportData2", con.Connection);
        command.CommandType = CommandType.StoredProcedure;

        command.ExecuteNonQuery();
    }
}
catch (Exception err)
{
    Logger.log("Error in Importdata function:" + err);
}

The problem is when calling this stored procedure from C#, it returns a SQL exception:

System.Data.SqlClient.SqlException (0x80131904): Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SimpleAttendance.frmLogin.CallImportDataSP(String filetype, String filepath, String query) in c:\Users\Amal\Desktop\Attendance app\SimpleAttendance\SimpleAttendance\SimpleAttendance\frmLogin.cs:line 127 ClientConnectionId:3758d5ca-1589-4bff-a006-f9e93dafce8b Error Number:7303,State:1,Class:16}

Upvotes: 2

Views: 2394

Answers (1)

andrews
andrews

Reputation: 2173

Try changing your spT code to this:

CREATE PROCEDURE Importdata2
AS
BEGIN
    DECLARE @sql NVARCHAR(max) = ''
    DECLARE @sqlFull NVARCHAR(max) = ''

    SET @sql = 'SELECT * FROM [' + 'Sheet1' + '$]'
    SET @sqlFull = '
            INSERT INTO Branches ([Description])
            select distinct  Department 
     FROM OPENROWSET(
    ''Microsoft.ACE.OLEDB.12.0''
     ,''Excel 12.0;Database=' + 'C:\ATTapp\ATToutput files\test.xls' + ';HDR=YES''
     ,''' + @sql + ''') where Department not in (select description from Branches)'

    print 'Full SQL: ' + @sqlFull
    EXEC (@sqlFull)
END
GO

I've moved EXEC out of the INSERT INTO and now feed EXEC with complete dynamic SQL string.

If you get syntax errors, notice what print statement prints. Try this version with both .xlsx and .xls files. Note, the Excel file should exist on disk before you actually run your spT. Note, I've also removed Xml from the OPENROWSET connection string because in my OPENROWSET calling code which works I don't have it.

Upvotes: 1

Related Questions