Wednesday, July 7, 2010

Write Excel data using SQL

SQL Configuration to run these damn things

curtecy of

Now, we should connect to the related SQL Server as an administrator and open a new query window.

After the query window is ready for running sql statements run the "sp_configure" sql statement.

If sp_configure command only lists a limited number (~14) of sql configuation settings, where 'Ad Hoc Distributed Queries' does not exist in the returned result set, we should open/enable the 'show advanced options' configuration parameter.

You can see 'show advanced options' in the list with run_value equals to "0" in such a situation.


To set 'show advanced options' run_value equal to 1 or to enable it, run

sp_configure 'show advanced options', 1


The return message from the above sql statements for a successful run is as;

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

After Advanced Options is enabled, you can again run sp_configure t-sql command and in the returned list of configuration settings, go to row where name is 'Ad Hoc Distributed Queries' and control its run_value.

If 'Ad Hoc Distributed Queries' is turned off for considering server security run_value should be "0"

But since we want to enable 'Ad Hoc Distributed Queries' component in order to run 'OpenRowset/OpenDatasource' sql statements, we should set the run_value to "1"

The below sql code is a sample how you can enable a SQL Server configuration parameter.

sp_configure 'Ad Hoc Distributed Queries', 1

The returned message is :

Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

A change with sp_configure comment will require reconfigure command to run in order to the new setting takes effect. So just run the "reconfigure" command:


Now you can see the run_value is set to 1 if you run the "sp_configure" command and control for the "Ad Hoc Distributed Queries" row.

So how do we write data back to Excel using SQL code?

First... read here to ensure all setings are correct.

Code is:

INSERT INTO OPENROWSET('Microsoft.Jet.OleDB.4.0', 'EXCEL 8.0; Database=[Full excel file path and name.xls]',
'SELECT [Columns to insert to] FROM [Sheet1$]')
from SQL_Table