none
Linked Server: Cannot initialize the data source object

    Question

  • I'm getting this error when I try to connect to a linked server (Excel file):

    An error occurred while attempting to execute the SQL database command,

    EXECUTE sp_tables_ex LSTEST

    Details:

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

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

    I'm running the following script using ExecuteCommand() on a SqlCommand object (ASP.NET):

    1EXECUTE sp_addlinkedserver 
    2  @server = 'LSTEST'
    3  @srvproduct = 'Microsoft OLE DB Provider for JET'
    4  @provider = 'Microsoft.Jet.OLEDB.4.0'
    5  @datasrc = 'C:\Inetpub\wwwroot\MyWebSite\bin\Spreadsheets\DatabaseInput.xls'
    6  @provstr = 'Excel 5.0;IMEX=1' 
    7GO 
    8 
    9EXECUTE sp_addlinkedsrvlogin 
    10  @rmtsrvname = 'LSTEST'
    11  @useself = 'FALSE'
    12  @locallogin = 'MYMACHINE\ASPNET'
    13  @rmtuser = NULL
    14  @rmtpassword = NULL 
    15GO 
    16 
    17EXECUTE sp_tables_ex LSTEST 
    18GO 

    If I go to the folder containing the .xls file and get Properties/Security, all users (Administrators, ASP.NET Machine Account, and Everyone) have "full control". Also, I double-checked that the path to the .xls file is correct.

    The problem only occurs if I run the website out of IIS. If I run this exact same code out of the ASP.NET Development Server (i.e., open the page from Visual Studio), it works fine. In fact, I don't even need the EXECUTE sp_addlinkedsrvlogin command. (Note: Versions are Windows XP SP3, IIS v5.1, Visual Studio 2005, SQL Server Express 2005 SP2, .NET Framework 3.0.)

    Does anyone know what I could be doing wrong?

    Thanks,

    -Dan
    Wednesday, February 11, 2009 7:23 PM

All replies