0

I am trying to export to excel from the following query. The following query doesn't work. Please let me know. I appreciate any help.

   INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\test.xls;','
    select distinct a.a_name, b.b_name, 
    CASE WHEN a.rights  = 1 THEN 'Yes' ELSE 'No' END AS rights 
    FROM TABLE_A a 
    JOIN TABLE_B b ON a.a_id = b.a_id')
nav100
  • 2,923
  • 19
  • 51
  • 89
  • http://stackoverflow.com/questions/9086880/t-sql-export-to-new-excel-file/9086889#9086889 – JonH Aug 29 '12 at 20:08
  • 1
    I am getting the following error message. OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries – nav100 Aug 29 '12 at 20:10
  • run this `sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO` – JonH Aug 29 '12 at 20:11
  • I was able copy and paste into Excel using sql server management studio.Thanks JonH. – nav100 Aug 29 '12 at 20:18

1 Answers1

0

You can use Export Import Wizard in SSMS.

  1. Right-click on the database which your query is using
  2. Choose TASKS from the context menu which opens after right-clicking
  3. Choose EXPORT DATA from menu
  4. SQL Server Import and Export window will open.
  5. In the wizard select your data source and where to export the data.
  6. Enjoy!

Additional info/ tutorial can be found here Please check http://www.kodyaz.com/articles/sql-server-export-to-excel-using-import-export-wizard.aspx

Princess
  • 443
  • 2
  • 5
  • 20