Post reply

Problem with Linked Server to Excel

  • August 2, 2007 at 7:38 pm

    #174672

     

    I am getting this error when trying to Select From a Table/Worksheet from a Linked Server connected to Excel

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLTEST_SP" reported an error. The provider did not give any information about the error.

    Msg 7330, Level 16, State 2, Line 1

    Cannot fetch a row from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLTEST_SP".

    I am running this in a SQL Server 2005 Express Edition (X32) instance on a 64 bit machine running Vista X64. I also have SQL Server X64 on the machine but there is no 64 bit version of the 'Microsoft.Jet.OLEDB.4.0' driver, so, I need to use SQL Server 2005 Express Edition for this.

    Here is the code I am using to establish the Linked Server and the Select statement:

    USE OLEDB32

    GO

    If Exists (select * from sys.servers where name = 'XLTEST_SP')

    BEGIN

    EXEC sp_dropserver 'XLTEST_SP', 'droplogins';

    END

    GO

    DECLARE @rc int

    DECLARE @server nvarchar(128)

    DECLARE @srvproduct nvarchar(128)

    DECLARE @provider nvarchar(128)

    DECLARE @datasrc nvarchar(4000)

    DECLARE @location nvarchar(4000)

    DECLARE @provstr nvarchar(4000)

    DECLARE @catalog nvarchar(128)

    -- Set parameter values

    SET @server = 'XLTEST_SP'

    SET @srvproduct = 'Excel'

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

    SET @datasrc = 'c:\Temp1\SQLExcelTest.xls'

    SET @provstr = 'Excel 8.0'

    EXEC @rc = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,

    @datasrc, @location, @provstr, @catalog

    GO

    SELECT * FROM XLTEST_SP...Sheet1$

    GO

    Any help resolving the problem would be appreciated.

    Terry Clancy

  • August 2, 2007 at 10:03 pm

    #724374

    You're not going to like this...

    The problem is that there's a lack of support for the Jet engine in 64-bit environments. This is a headache we've run into a bunch. Look into using a 32-bit instance of SSIS to import the data into SQL Server.

  • August 3, 2007 at 2:21 pm

    #724638

    Thanks - actually I was aware of this (and yes I don't like it 🙂 )

    However this should not be the problem hin this case because I am using SQL Server Express X32 and although I am running on a 64 bit machine running Vista X64 the 32 bit 'Microsoft.Jet.OLEDB.4.0' driver should work with 32 bit SQL Express. 

    Actually the reason I am using SQL Express at all is because of the absence of a 64 bit 'Microsoft.Jet.OLEDB.4.0'  driver. I am trying to implement the workaround discussed at C:\Clancy\Terry\IT\Learning\SQL\ADO.Net\centerGorm Braarvig-center Access database from SQL 2005-64.mht  and had the problem.  I have eliminated much of the complexity of that solution when posting this question to show just the problem.

    Thanks for your comment - but I still believe it should work.

    Terry Clancy

Viewing 3 posts - 1 through 2 (of 2 total)

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