I recently had to change some Excel spreadsheet reading code that uses the MSSQL OPENROWSET function to read and query Excel spredsheets. In MSSQL2000 this worked no issues but because of the updated security on MSSQL2005, the bulk operations are disabled by default.

For example, to query a spreadsheet this is what i would use

<cfquery datasource="dsource" name="xlsData">
SELECT *
FROM OPENROWSET (
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database={pathToSpreadsheet}',
'Select * from [{SheetName}$]'
)
</cfquery>

Using this method you can query any excel spreadsheet as long as your application is running.

Recently i tried to do the same thing on a MSSQL2005 server and it didn't work. After researching and googling i found that for this process to work you need to enable the "Ad Hoc Distributed Queries" option on the database server as this is disabled by default. You need to enable this to be able to perform any BULK operations such as text file importing. There is no GUI to enable this so you need to run it within the Query Analyser using the "sa" user and run this query

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

After you do this you then also you will also then need to check the Windows Registry and look for the registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0

and make sure you have a REG_DWORD entry named "DisallowAdhocAccess".

If you do, it needs to be set to 0.
If don't you need to add it in and set it to 0.

After that you should be able to perform your bulk operations such as querying spreadsheets.