Quering excel

Question

Hi there,

 

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$]');

 

 

Run perfectlly, but on server B i get the 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.

Can someone help me?

The link >: http://blog.hoegaerden.be/2010/03/29/retrieving-data-from-excel/ didn´t help me =\...

---

------------------------------------------------------------- Oracle OCA11g

Answer 1

Please check this forum:

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb

USE [master]
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

---

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.

Answer 2

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.

---

~Manoj ([e-mail])
http://sqlwithmanoj.wordpress.com
MCCA 2011