1

I have created an account in SQL Server that I use in a connection string for a c# application.

I am trying to export data from my c# database programmatically,

INSERT INTO OpenRowSet( 
'Microsoft.ACE.OLEDB.12.0' 
, 'Excel 12.0;Database=C:\Temp\TestExcel.xlsx;' 
, 'SELECT * FROM [categoryData$]' 
) 
SELECT * FROM categoryData;

When i do this I receive an error

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

So I did some research and came across the following guide:

https://visakhm.blogspot.co.uk/2013/12/how-to-solve-microsoftaceoledb120-error.html

I'm on step 2 where I have to add permissions to the Temp folder to the account accessing the database.

This is needed because the provider uses the temp folder while retrieving the data. The folder can be one of the below based on whether you use a local system account or network domain account.

For network accounts, folder is

:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp

Here is my connection string (wrapped for readability):

SqlConnection con = new SqlConnection("Data Source=x.x.x.x,1433; Network Library=DBMSSOCN;
Initial Catalog=footfall; User ID=access; Password=pw;connection timeout=0; ");

Am I correct in assuming that the user account I need to add permissions for is "access" - Which is a login I setup myself. If so, how to I add this account as I can't find it in the security dialog.

Edit: Using answer below from Max Vernon I completed STEP 2 of the guide but STEP 3 I cannot as I am using a 64 bit version of SQL Server. Does anyone else know how to get round this error as I would really like to be able to export my data to excel.

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

EDIT: I managed to get this working yesterday by adding a domain admin account as the log in account for the sql server service, then outputting the file to a network drive. I still cant get it to work locally. I can get it to write rows into the spreadsheet by manually editing the column headings to match those in the table. I've written some new code to create the spreadsheet using System.Runtime.InteropServices; & Microsoft.Office.Interop.Excel; Then I can export the data.

3

The rights need to be provided to the SQL Server Service Account, which is typically an Active Directory account setup specifically for SQL Server to use. Microsoft has a great Docs page that describes Service Accounts for SQL Server.

Check with SQL Server Configuration Manager to determine the name of that account.

Assign rights to the temp folder for that account.

In the image above, you can see SQL Server Express is running under the NT AUTHORITY\NetworkService account - any account that begins with NT AUTHORITY is an account local to the machine running SQL Server.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.