I often use something along the lines of this to read in Excel data. The IMEX=1 trick will save you a lot of grief from SQL incorrectly "guessing" what type of data to read in from Excel.

/*

-- NOTES

--N.B. "IMEX=1" in connection string for OPENROWSET prevents this problem: If 1st row in XL file is blank, then ALL rows will have NULL.

*/

USE dev;

GO

SELECT *

FROM OpenRowset(

'Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=\\myserver\SQLServerFiles\XL-Import-Test.xls',

'select * from ReportData'-- Where ReportData is a Named Range in XL

) -- will be called F1, F2, F3, ... if there are no headers.

--If it's just a sheet, try

SELECT *

FROM OpenRowset(

'Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=\\myserver\SQLServerFiles\XL-Import-Test.xls',

'select * from [Sheet1$]'

)

Per BOL: "OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access."

You can turn this on with the Surface Area Configuration Manager or with the sp_configure command, per ssagrawal's post above.

This should get you started,

Rich