locked
"OPENDATASOURCE" query and linked server fails after changing the SQL Server service startup account

    Question

  • Recently I have changed the SQL Server service startup account for one production SQL Server. While changing the service account, I have already added the account into the local administrator group, and I have restarted the SQL Server service after changing the account.
    But today when I executed below query, I got an error message, and I couldn't think out how to solve the issue:

    --query
    SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=D:\anz_200x.xls;Extended Properties=Excel 8.0')...Sheet1$

    --error message
    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" 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 "(null)".

    Does anyone have an idea how to solve this problem without restart the server? Thanks so much! any ideas would be appreciated!

    BTW, there is another SQL Server which has changed startup account too, but everything is working fine...

    Thursday, July 09, 2009 7:06 AM

All replies

  • This drove me mental for a while.

    If you are trying to open an external file such as a TXT, XLS, CSV using SQL's OPENROWSET or OPENDATASOURCE functionality and keep getting errors that look like the ones below then here is the fix/workaround.

    The Errors:

    OLE Database provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
    returned message "Unspecified error".

    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE Database provider
    "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


    The fix:

    I tried all the suggestions about SP2, restarting services, setting flags in the SQL settings etc but all to no avail.

    What you need to do is visit the link below and download the

    2007 Office System Driver: Data Connectivity Components download

    Once downloaded and installed on the SQL Server you will notice a new driver in the list of drivers on the server at this path:

    SERVER\Server Objects\Linked Servers\Providers

    The new driver is called:

    Microsoft.ACE.OLEDB.12.0

    All you then need to do is change your SQL Query to use this new driver rather than the old Microsoft.Jet.OLEDB.4.0 one.

    Your new connection string should look something like this:

    OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'',
    ''Data Source=C:\data\;
    Extended Properties="Text;HDR=No;FMT=Delimited"'')...['+ @FileN +'#CSV]'
    Vivian_Vivian ! That should sort you out.
    Tuesday, September 01, 2009 8:13 AM