SQL with Manoj

–> Question:

I have 2 servers, on server A the query:

SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 8.0;HDR=YES;Database=C:\Book1.xls',
    'SELECT * FROM [Sheet1$]');

Runs perfectly, but on server B I get following error:

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “The Microsoft Access database engine could not find the object ‘Sheet1$’. Make sure the object exists and that you spell its name and the path name correctly. If ‘Sheet1$’ is not a local object, check your network connection or contact the server administrator.”.

Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

If I go to Proviver -> Microsoft.ACE.OLEDB.12.0 -> Properties:
On the server that works: on the white square bellow i got: Linked servers using this provider: XML
On the server with error i got nothing.
 

–> Answer:

If you are using same query/connection then make sure the excel files are on both the servers at C: drive.

Are both the servers same, i.e. 32 bit or 64? check: select @@version

And same for excel, it seems you have Excel 2007.

Check this blog post for more info.
 

Ref link.