Hello folks,
-- 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
8 Replies
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.

Shweta LodhaPosted Nov 22, 2014, 5:57 AM
Try installing MS Access 2010 64 bit driver. http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/
OR
check this:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6911b996-5a7e-425d-9e08-a820df4a216f/ole-db-provider-cannot-be-used-for-distributed-queries-because-the-provider-is-configured-to-run-in?forum=sqlgetstarted
K P Singh ChundawatPosted Nov 26, 2014, 1:23 AM
Shweta LodhaPosted Nov 24, 2014, 12:44 PM
Is your problem solved?
K P Singh ChundawatPosted Nov 23, 2014, 11:52 PM
K P Singh ChundawatPosted Nov 22, 2014, 12:24 AM
Nimit JoshiPosted Nov 21, 2014, 8:43 AM
http://www.c-sharpcorner.com/UploadFile/4b0136/connectivity-of-excel-2013-to-Asp-Net-web-application/
K P Singh ChundawatPosted Nov 21, 2014, 8:38 AM
Shweta LodhaPosted Nov 21, 2014, 8:01 AM
It may be bit-level issue. Please cross check your office, is it also 64 bit ?
SQL server and Office both should share the same architecture.