Peter Larsson Blog

Patron Saint of Lost Yaks

Getting errors when working with Excel and SQL Server

In this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 I gave a suggestion how to get a more detailed error message when working with OPENROWSET.

But the same thinking is applicable for LINKED SERVER and other "outer referenced" data retreival methods.

The OPENROWSET syntax with Jet provider does not give you full error description when problem occurs, such as permission errors.

– Using this code for a file with no appropriate permissions throws a general error
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'Excel 5.0;HDR=No;IMEX=0;Database=\datastorage\excel\book2.xls', 'select * from [Sheet1$a1:q50]')

If you have some error and you don't understand why, try using MSDASQL provider temporarily.

SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\datastorage\excel\book2.xls',
    'SELECT * FROM [Sheet1$]')

Now most error desriptions are output. When you have resolved the error, switch back to Jet again.

For Excel 2007 and later, the OPENROWSET contructor would look something like

SELECT

*
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=C:\book1.xlsx;IMEX=1', 'SELECT * FROM [Sheet1$]')

Legacy Comments


Mladen
2007-10-24
re: Getting errors when working with Excel and SQL Server
which page in the topic? :)

Peter Larsson
2007-10-24
re: Getting errors when working with Excel and SQL Server
Page 6.