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
run WMI queries and process or another DOS or Windows process from SQL Server using the SQL xp_cmdshell function. This tutorial walks you through how to setup xp_cmdshell on the server and execute differenttype sof...
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.
This is a tutorial on using the SAP .Net Connector, NCO 22.214.171.124 for .Net 4 and Visual Studio 2010.
No comments yet.