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