Excel and SQL Server Integration using OLEDB
Configure the Microsoft.ACE.OLEDB.12.0 to insert or update data in an Excel spreadsheet from SQL Server by using the OPENROWSET command in Transact-SQL to provide an elegant and easy to use interface. There are a couple of easy steps to perform to be able to use this OLEDb interface technology which are provided below.
First you have to set a couple Registry parameters which can be managed with these two commands:
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
You also have to enable the Distributed Queries in the Surface Area Configuration using this set of commands
SP_CONFIGURE 'show advanced options', 1
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
Of course you also have to tell the data provider that you are dealing with an Excel file and not an Access database which is the default using the Extended Properties clause:
Select ProductCategory, ProductName,sum(case when TransactionType = ‘Sales’ then Amount else 0 end) as Sales,sum(case when TransactionType = ‘Budget’ then Amount else 0 end) as Budgetfrom OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,‘Excel 12.0;Database=C:\GizmoSales.xlsx;Extended Properties=”Excel 12.0 Xml;HDR=YES”;’, ‘Select * from [SalesData$]‘)group by ProductCategory, ProductName
When referencing the Excel sheet T-SQL you have to use this syntax if not you will get an error stating that the sheet doesn't exist:
If In Excel the sheet name is : Temp (as an example)
then in T-SQL you need to reference it as [Temp$]
Other than that you can use openquery, openrowset or opendatasource as you would in previous versions of Excel and SQL Server.
More by this Author
Use this guide to locate an iOS app's sandbox on a Mac. This guide also provides an overview of an iOS app file structure.
How to Develop an Excel App using the VSTO Interop services (com) and C#. This hub also demonstrates how to add a ribbon and interact with the app from the ribbon.
A free and complete software development proposal template with budget, ROI and CAGR formulas and instructions. Detailed instructions to help you prepare a winning software development proposal.
No comments yet.