ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming

Excel and SQL Server Integration using OLEDB

Updated on August 12, 2012

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 Budget
from 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.


    0 of 8192 characters used
    Post Comment

    No comments yet.