Written by:
2009/06/21 02:56 PM 

Fortunately, MSSQL has a few methods of accessing other data.

• You can export the data into a format that MSSQL can understand like MS Access. But this also has it's problems and is very time consuming.
• You can cut-‘n-paste the data. In my experience this works well but has limited success, and you have to know what you’re doing.
• You can use DTS or SSIS by using the import wizard to create a DTS or SSIS package.
• You can create a linked server
• You can use OpenRowset or OpenDatasource.

In this post we will be concentrating on OpenRowset and OpenDatasource.

Enable OpenRowset and OpenDatasource.

For this to work you have to enable the use of OpenRowset and OpenDatasource. To do this run the SQL Server Surface Area Configuration Tool. Then select Surface Area Configuration for Features. In the dialog box that appears, expand your server tree and the database engine tree. Then select the Ad Hoc Remote Queries branch. Make sure the Enable Openrowset and Opendatasource checkbox is checked as in the picture below.

Note: Be aware of the security implications of allowing Openrowset and Opendatasource

OpenRowset

The OPENROWSET feature in SQL Server and SQL Express provides a fast and easy way to open an OLE DB compatible data source, such as an Excel sheet or dBASE data, directly from your SQL script. Coupled with the "SELECT * INTO" command, the OPENROWSET feature can import data from your other data sources into a table in SQL Server or SQLExpress. You may even want to just use the data in a view or join.
Openrowset should be used in cases when access in not required on a regular basis, i.e.. as in ad hoc queries. When data is accessed several times, a linked server should then be used.

The OpenRowset function in its simplest form carries the following syntax.
OPENROWSET (provider_name,provider_string,query_syntax)

For more information on Openrowset visit the Microsoft MSDN Library here.

So to open a MS Excel 97-2003 file use something like this, making sure the Excel file is on a drive and path accessible by the SQL server with sufficient rights:

select * FROM OPENROWSET(
  'Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=D:\SQL Data\RHC\Book3.xls;HDR=YES',
  'SELECT * FROM [DatQuestionaire$]') a
inner join datQuestion b on a.Questionaireid = b.questionaireid

For Excel 2007 we just use a different OLEDB driver.

select * FROM OPENROWSET(
  'Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=D:\SQL Data\RHC\Book1.xlsx;HDR=YES',
  'SELECT * FROM [Sheet1$]')

Note: You might receive this error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
If so a quick work around is import the following registry file to make it work:
(Here is the file content)

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0]
"AllowInProcess"=dword:00000001
"DynamicParameters"=dword:00000001

The execution might be a bit slow at first, but once the query plan is built execution is pretty descent.

OpenDatasource

Opendatasource is similar to Openrowset above. It allows ad hoc connection and querying to an external data source, such as Excel via installed drivers. Opendatasource syntax is slightly different than Openrowset.
OPENDATASOURCE ( provider_name, init_string )

For more information on Opendatasource go to the Microsoft MSDN Page here.

To open an excel file using Opendatasource you might use something like this:

SELECT *
FROM
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\SQL Data\RHC\Book2.xls;
Extended Properties=''Excel 8.0''')...[Sheet1$]

Remember for Excel 2007 use a different driver:

SELECT *
FROM
OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\SQL Data\RHC\Book1.xlsx;
Extended Properties=''Excel 12.0''')...[Sheet1$]

Queries

Now you can use any SQL query. Perhaps you want to limit the Excel data with a where clause. Use Openrowset. (Note: I haven't tested every single query), as in:

select * FROM OPENROWSET(
  'Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=D:\SQL Data\RHC\Book1.xlsx;HDR=YES',
  'SELECT * FROM [Sheet1$] where [ID] > 20')

How about joining two Excel spreadsheets with in the Excel worksheets together like this:

select * FROM OPENROWSET(
  'Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=D:\SQL Data\RHC\Book3.xls;HDR=YES',
  'SELECT * FROM [DatQuestionaire$] a inner join [datQuestionaire2$] b
on a.QuestionaireId = b.QuestionaireId where a.QuestionaireID >20
') Excel

Now lets make this a bit more interesting. Take that Excel Openrowset query and join that to an existing MS SQL table like this:

select * FROM OPENROWSET(
  'Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=D:\SQL Data\RHC\Book3.xls;HDR=YES',
  'SELECT * FROM [DatQuestionaire$] a inner join [datQuestionaire2$] b
on a.QuestionaireId = b.QuestionaireId where a.QuestionaireID >20
') Excel
inner join datQuestion b on Excel.[a.Questionaireid] = b.questionaireid

So as you can see, your imagination can run wild. No longer does your Excel data have to be side-lined. No longer do you have to pull your hair out trying to figure out how to access your Excel data.

But what if you just want to import the data and start over. Well we make use of a very nice statement, select into … Which will create a brand new table for us.

select * into MyOpenRowsetTable FROM OPENROWSET(
  'Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=D:\SQL Data\RHC\Book3.xls;HDR=YES',
  'SELECT * FROM [DatQuestionaire$] a
    inner join [datQuestionaire2$] b
    on a.QuestionaireId = b.QuestionaireId
') a

So there you have it. Working with Excel data in MS SQL. In fact you can use any other driver this way to access other data. Like  plain text files, dBASE data, etc. Enjoy and have fun. No more pulling your hair out.

TrackbackPrint

Tags:

Categories:

Location: BlogsThe Brave Programmer