Linked Server Problem

Question

Post reply

Linked Server Problem

darren_w99

Old Hand

Points: 356
More actions
January 16, 2008 at 11:49 am

#185738

I'm having a problem selecting from an Excel linked server...
1. Create a new simple Excel file at C:\DeleteMe.xls and add a couple column headers and a row of data. e.g.
--------------
ColA | ColB |
--------------
123 | 456 |
-------------
2. Execute the following command in SQL Server 2005 Management Studio...
EXEC sp_addlinkedserver
@server = 'ExcelSource',
@srvproduct = 'Excel',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'Database=C:\DeleteMe.xls',
@provstr = 'Excel 5.0'
select * from ExcelSource...[Sheet1$]
If you're like me, you'll get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource".
Does anyone have a solution?
THANKS in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

EXEC master.dbo.sp_addlinkedserver

@server = N'ExcelSource',

@srvproduct=N'Excel',

@provider=N'Microsoft.Jet.OLEDB.4.0',

@datasrc=N'C:\DeleteMe.xls',

@provstr=N'Excel 8.0'

You have to use Excel 8.0 for Excel 97 +

My blog: http://jahaines.blogspot.com

darren_w99 Old Hand Points: 356 More actions · Answer 2

Great. It works.

Hey Adam, thanks again for helping me today. That's two problems solved.

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 3

Check the last parameter.

EXEC sp_addlinkedserver

@server = 'ExcelSource',

@provider = 'Microsoft.Jet.OLEDB.4.0',

@srvproduct = 'OLE DB Provider for Jet',

@datasrc = N'C:\DeleteMe.xls',

@provstr = 'Excel 8.0;HDR=NO;IMEX=1'

Some values in an Excel column may be returned as Null when the underlying value is not a Null. This typically occurs when numeric and text datatypes are intermixed within the same Excel column.

Setting IMEX=1 tells the driver to use Import mode.

Check the link below for more info:

http://support.microsoft.com/kb/194124

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 4

You could also check this:

http://www.sql-server-helper.com/tips/read-import-excel-file-p02.aspx

darren_w99 Old Hand Points: 356 More actions · Answer 5

Hi Nisha. Thanks for your help.

I gather that the way to go is to set

IMEX=1 when you're going to select from excel

IMEX=0 when you're going to insert/update to excel

Thanks again.

rakeshsqltips Grasshopper Points: 11 More actions · Answer 6

Hi,

I tried same code but still having same problem.

EXEC master.dbo.sp_addlinkedserver

@server = N'ExcelSource',

@srvproduct=N'Excel',

@provider=N'Microsoft.Jet.OLEDB.4.0',

@datasrc=N'E:\XlsDataSource.xls',

@provstr=N'Excel 8.0'

GO

Error Details:

---------------

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource".