Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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$]')

Print | posted on Wednesday, October 24, 2007 8:01 AM | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# re: Getting errors when working with Excel and SQL Server

which page in the topic? :)
10/24/2007 11:10 AM | Mladen
Gravatar

# re: Getting errors when working with Excel and SQL Server

Page 6.
10/24/2007 2:38 PM | Peter Larsson
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET