title: Cannot initialize the data source object of OLE DB provider
body: <p>Getting the following error in SQL Server 2008 when trying to select data from an Excel (xls) file:</p>
  
  <pre><code>Msg 7303, Level 16, State 1, Line 43
  Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server
  </code></pre>
  
  <p>The SQL is as follows:</p>
  
  <pre><code>SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;HDR=YES;Database=c:\\Temp\\Book1.xls;', 'SELECT * FROM [Sheet1$]')
  </code></pre>
  
  <p>Steps taken:</p>
  
  <ol>
  <li>Installed the Access Database Engine. 'Microsoft.ACE.OLEDB.12.0' <strong>does</strong> show up in Server Objects > Linked Servers > Providers</li>
  <li>Made sure 'Dynamic parameters' and 'Allow inprocess' is selected</li>
  <li>Made sure the Excel file was closed</li>
  <li>Made sure 'ad hoc distributed queries' was enabled (1)</li>
  <li>Made sure the user running the SQL service has permissions to the c:\temp\ folder</li>
  </ol>
  
  <p>My theory:
  Running on my dev box works fine as my SQL service is running as 'LocalSystem'. The target server I'm trying to get running has a specific network user it's running on. This cannot be changed.</p>
  
  <p>From what I know/have read about OPENROWSET and OPENQUERY (also tried with linked server) it needs to create a temporary file in C:\Windows\ServiceProfiles\\AppData\Local\Temp. I've manually tried to create a folder structure for this user in service profiles but that doesn't work either.</p>
  
  <p>:: UPDATE ::
  In the end I abandoned this idea and instead created an package in integration services. As this process needs to run as part of a job this works well enough for my needs.</p>
  
  <p>If anyone is interested. Creating the package in IS is as easy as following the import data wizard and at the end you choose 'Create Integrated Services Package'. This can then be called from your job.
  Ta</p>