K P  Singh Chundawat

K P Singh Chundawat

  • 748
  • 1k
  • 209.2k

Getting error of 'Microsoft.ACE.OLEDB.12.0'

Nov 21 2014 6:43 AM

Hello folks,

 
 I am currently using SQL Server R2 in Windows 7 64 Bit OS.
 I want to update my existing database table  (WANT TO UPDATE ONLY ) from spreadsheet (Excel) workbook.
 
i am using MS Office 7. 
 
 
my Stored Procedure Looks like: 
-- ================================================

-- Template generated from Template Explorer using:

-- Create Procedure (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- This block of comments will not be included in

-- the definition of the procedure.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

---C:\foldername\spreadsheetname.xls

---C:\Users\Public\Documents\DemoUpdate.xlsx

-- =============================================

-- Author: KP Singh

-- Create date: 21/Nov/2014

-- Description: Update tbAddress anf tbSupplierMaster from Excel Spreadsheet

-- =============================================

CREATE PROCEDURE usp_undateSupplierandaddress

-- Add the parameters for the stored procedure here

--@filepath varchar(100)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

BEGIN TRANSACTION

UPDATE tbSupplierMaster

SET tbSupplierMaster.SupplierCode = ExcelTable.SupplierCode,

tbSupplierMaster.SupplierName = ExcelTable.SupplierName,

tbSupplierMaster.TinNo = ExcelTable.TinNo,

tbSupplierMaster.CST = ExcelTable.CST

FROM tbSupplierMaster

INNER JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\Users\Public\Documents\DemoUpdate.xlsx;',

'SELECT SupplierIDp, SupplierCode, SupplierName, TinNo, CST FROM [Sheet1$]') AS ExcelTable

ON YourTable.ID = ExcelTable.ID

WHERE (tbSupplierMaster.SupplierIDp = ExcelTable.SupplierIDp

)

COMMIT

-- Insert statements for procedure here

END

GO

 But when i run this query then i get the following errors:
 
Msg 7308, Level 16, State 1, Procedure usp_undateSupplierandaddress, Line 17
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
 
Please give me any kind of solution ..
 
 
Thanks in Advance
 
KP Singh
 
 
 
 

Answers (8)