Linked Server: Cannot initialize the data source object


  • 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


    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' 
    9EXECUTE sp_addlinkedsrvlogin 
    10  @rmtsrvname = 'LSTEST'
    11  @useself = 'FALSE'
    12  @locallogin = 'MYMACHINE\ASPNET'
    13  @rmtuser = NULL
    14  @rmtpassword = NULL 
    17EXECUTE sp_tables_ex LSTEST 

    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?


    Wednesday, February 11, 2009 7:23 PM

All replies