LinkedServer using Microsoft.ACE.OLEDB.12.0 for Excel worksheet

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  July 6, 2014 at 10:29 am

  #296277

  Hello comunity

  I use many times Microsoft.ACE.OLEDB.12.0 provider to link Excel worksheet, without problems.

  but on my last experience, I encountered 2 problems:

  My coding is the following:

  USE [master]

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

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

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

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

  EXEC sp_addlinkedserver @server = N'ExcelDataSource',

  @srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0',

  @datasrc=N'D:\Trabalho_Clientes\Sgate_BD\Wendler.xlsx',

  @datasrc=N'C:\IMPORT\Wendler.xlsx',

  @provstr=N'EXCEL 12.0' ;

  EXEC sp_addlinkedsrvlogin ExcelDataSource, FALSE;

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ExcelDataSource',@useself=N'False',@locallogin=NULL,@rmtuser=N'Admin',@rmtpassword=N''

  The first problema is:

  @datasrc=N'\\Server\c$\IMPORT\MyexcelFile.xlsx'

  or

  @datasrc=N'\\192.168.1.10\c$\IMPORT\MyexcelFile.xlsx'

  Error occur , i cannot use UNC path on @datasrc ??

  Secund error:

  I have to write this lline of code:

  @rmtsrvname=N'ExcelDataSource',@useself=N'False',@locallogin=NULL,@rmtuser=N'Admin',@rmtpassword=N''

  I dont understand why, this is the first that i used this line, i always choose the option on my linkedserver:

  "Be Made without using a security context".

  Someone could give a reason for that !?

  Also, i like to know if there is another provider independent regarding if the operating system or version of Office is X86 or X64, which allows to connect to any sheet Excel, because sometimes i cannot used this provider because OS is X64, but Office is X86, or simply OS is X86.

  Many thanks,

  Best regards

  Luis Santos

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  July 8, 2014 at 4:31 am

  #1727700

  Hello comunity

  Nobody could give me a ideia or solution ?

  I see several tread , and someone of them talk about permission on TEMP folder !?

  What i need to do, on any user computer, is to create the linkedserver without giving a direct access to the server or mapping a drive letter to do this.

  Many thanks

  Luis Santos

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  February 8, 2015 at 11:25 pm

  #1776296

  In the category of "Better Late than Never", the following link seems to have an answer to your Temp folder woes.

  http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm

  This problem reportedly only occurs on 32 bit machines so if this is happening on a 64 bit instance of SQL Server, there may be a larger problem at hand.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)