SQL 2005 and OPENROWSET
Author: jason
Date: 2008-04-09 00:16:37
Category: Technical

I use the command OPENROWSET to push Excel files into a table. To do this I need to change the "Surface Area" of the SQL server. Here are the steps needed. I'm kind of old-fashioned, so I'll do it with sql query commands, from inside the Management Studio.

Surface Area
Open the "SQL Server Management Studio" from the Start Menu, Programs, "Microsoft SQL Server 2005" folder
Click Connect



Click on "New Query" at the top
Enter the below commands and click Execute
Code

USE master;
GO
EXEC sp_configure 'show advanced option', '1'




Enter the below commands and click Execute
Code

RECONFIGURE;
EXEC sp_configure;




Enter the below commands and click Execute. After confirming the option was added close the query sub-window by clicking the middle right-hand X
Code

USE master
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', '1';
RECONFIGURE WITH OVERRIDE;




Open the Registry Editor
Click on Start Run, type "regedit"
Navigate to
Code

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0


Right-click in the right pane, select New DWORD Value
Code

Name: DisallowAdHocAccess
Value: 0





Close the registry editor. You are ready for a OPENROWSET. If you have a table setup, and an Excel with columns that match, here is the command.

Code

INSERT INTO mydatabase.dbo.mytable
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\reports\myexcel.xls', [sheet1$])



References
Managing the "Surface Area" of SQL Server 2005
SQL Server Surface Area Configuration
OPENROWSET (Transact-SQL)

OPENDATASOURCE Authority on SQL Server 2005




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009