Export Data from SQL Server 2014 to Excel

1
votes

I tried to export data from SQL Server 2014 to Excel.

I tried like this 

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0
Xml;HDR=YES;Database=D:\testing.xlsx;', 
'SELECT OI_NAME,OI_ADDRESSS FROM [Sheet1$]') select OI_NAME,
OI_ADDRESSS from OI_TEMP

But I get this error

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I searched on the internet, and found this:

The SQL Server Error Message if a user have no rights for SQL Server TEMP directory OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

But I assign permission is still does not work.

Why?

2
sql-serverexcelexcel-2007sql-server-2014

2 Answers

2
votes

To export data from SQL to Excel, you need to follow 2 steps:

  • Step 1: Connect Excel to your SQL database‍ (Microsoft SQL Server, MySQL, PostgreSQL...)
  • Step 2: Import your SQL data into Excel

The result will be the list of tables you want to query data from your SQL database into Excel:

enter image description here

Step1: Connect Excel to an external data source: your SQL database

  1. Install An ODBC
  2. Install A Driver
  3. Avoid A Common Error
  4. Create a DSN

Step 2: Import your SQL data into Excel

  1. Click Where You Want Your Pivot Table
  2. Click Insert
  3. Click Pivot Table
  4. Click Use an external data source, then Choose Connection
  5. Click on the System DSN tab
  6. Select the DSN created in ODBC Manager
  7. Fill the requested username and password
  8. Avoid a Common Error
  9. Access The Microsoft Query Dialog Box
  10. Click on the arrow to see the list of tables in your database
  11. Select the table you want to query data from your SQL database into Excel
  12. Click on Return Data when you’re done with your selection

To update the export automatically, there are 2 additional steps:

  1. Create a Pivot Table with an external SQL data source
  2. Automate Your SQL Data Update In Excel With The GETPIVOTDATA Function

I've created a step-by-step tutorial about this whole process, from connecting Excel to SQL, up to having the whole thing automatically updated. You might find the detailed explanations and screenshots useful.

0
votes

I try on a few years ago, do it that way and it was not possible, I suggest you use this form to export data to Excel from SQL -server.

Font: Microsoft Developer Network

Create an excel file, then option Data -> From other source -> From SQL Server

Select input your server, user and password, then select database and choose your table.

See this video:

Video Tutorial - SQL server to Excel

Then, you can code a macro on VBA to update automatically excel file

sub Auto_Open ()

ActiveWorkbook.RefreshAll

end sub

I hope this help to you!