Post reply

Problems with Excel Linked Server ** Can Someone Help **

  • February 21, 2008 at 3:07 am

    #127983

    Hi All,

    I am having some problems setting up a linked server on a SQL 2005 installation, the funny thing is that I keep getting an unspecified error, hence not very sure of where to attach the problem from.

    The same code works on all other SQL 2005 installations.

    Here is the code

    exec sp_dropserver @server = 'PersonProfile', @droplogins ='droplogins'

    EXEC sp_addlinkedserver 'PersonProfile',

    'Jet 4.0',

    'Microsoft.Jet.OLEDB.4.0',

    'C:\Import\PersonProfile.xls',

    NULL,

    'Excel 5.0'

    EXEC sp_addlinkedsrvlogin @rmtsrvname = 'PersonProfile', @useself = 'false', @rmtuser = 'Admin', @rmtpassword = NULL

    exec sp_tables_ex 'PersonProfile'

    I don’t think it’s a permission problem, as the service account is an administrator has permissions to the folder etc, I have also tried other paths including network shares etc.

    The error I am getting is as follows:

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

    Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41

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

    I am hoping someone will be able to help with this error.

    Thanks in advance

  • February 21, 2008 at 8:12 am

    #780666

    Dean, to rule out any issues with the linked server, try running this...

    select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;DATABASE=C:\Import\PersonProfile.xls', 'Select * from [Sheet1$]')

    Note: If your worksheet tabs are name then replace Sheet1 with the name of the tab.

    Doug

  • February 21, 2008 at 8:17 am

    #780670

    Hi,

    Thanks for the idea, unfortunately this did not work as i have previously tried to use this method, its giving thesame error.

    Thx

  • February 21, 2008 at 8:23 am

    #780675

    Dean, how about some more details? for instance, SQL version, Office version, MDAC version, etc. Also, is the Excel file on the same file system as the server? I ran into this once: executing openrowset from workstation, connected to SQL server1 through SQL Workbench. Excel was local to workstation so it failed. Moved Excel file to same path on SQL Server....

    Doug

  • February 21, 2008 at 10:25 am

    #780763

    Hi

    I have tried moving the files onto various paths, both local and also on the network to no avail.

    Also, SQL version is SQL 2005 SP2

    MDAC version (When i tried using the MDAC checker tool, it came up as unknown, but on this ms site http://support.microsoft.com/kb/301202, it also said that you can check using the registry, I have done this and its coming up as version 2.82.3959.0

    Let me know if you need anything else.

Viewing 5 posts - 1 through 4 (of 4 total)

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