Export Table data in to Excel using T-SQL

• kiran08.bi

  SSC-Addicted

  Points: 417

  More actions

  October 13, 2016 at 12:15 am

  #313797

  I am trying to export my table data in to excel through T-SQL query

  Below is the query which I am using for the same

  INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;Database=G:\Test.xls;',

  'SELECT * FROM [Sheet1$]')

  SELECT * FROM dbo.products

  When I execute the above query I am getting the below error

  Msg 7302, Level 16, State 1, Line 2

  Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

  Any solution to fix this problem is highly appreciated

  I am running this query in SQLServer 2012 .

  so i tried with below query

  Insert Into OPENROWSET('Microsoft.Ace.OLEDB.12.0','Excel 12.0;

  Database=D:\Test.xlsx', 'SELECT * FROM [Sheet1$]')

  Select * From dbo.BARS_Purge_Work

  for Above code i am getting thee below error

  Msg 7399, Level 16, State 1, Line 3

  The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

  Msg 7303, Level 16, State 1, Line 3

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

• sunita2912

  SSC Veteran

  Points: 295

  More actions

  October 13, 2016 at 11:39 am

  #1906425

  Try this . This helped me --

  http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx

• kiran08.bi

  SSC-Addicted

  Points: 417

  More actions

  October 14, 2016 at 3:59 am

  #1906519

  Thanks For your reply . I tried the settings in the above link but it did not workout

  Below is the another solution to achieve this and it is working fine

  DECLARE @FileName varchar(50),

  @bcpCommand varchar(2000),

  @Query nvarchar(4000)

  SET @FileName = REPLACE('D:\01_KT\Others\export_'+CONVERT(char(8),GETDATE(),1)+'.csv','/','-')

  --SET @FileName='D:\01_KT\Others\Copy_Test.xls'

  SET @Query ='SELECT ''Account_Name'' AS Account_Name, ''Ammount'' AS Ammount, ''YearMonth'' AS YearMonth UNION ALL SELECT cast(Account_Name as varchar), cast(Ammount as varchar), cast(YearMonth as varchar) FROM DEMO.dbo.Account'

  SET @bcpCommand = 'bcp ' + '"' + @Query + ' " queryout "'

  SET @bcpCommand = @bcpCommand + @FileName + '" -c -T'

  EXEC master.dbo.xp_cmdshell @bcpCommand

  Table Query:

  USE [DEMO]

  GO

  /****** Object: Table [dbo].[Account] Script Date: 10/14/2016 10:46:02 AM ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[Account](

  [Account_name] [nchar](10) NULL,

  [Ammount] [int] NULL,

  [YearMonth] [int] NULL

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[Account] ([Account_name], [Ammount], [YearMonth]) VALUES (N'A ', 2342, 201604)

  GO

  INSERT [dbo].[Account] ([Account_name], [Ammount], [YearMonth]) VALUES (N'A ', 2465, 201606)

  GO

  INSERT [dbo].[Account] ([Account_name], [Ammount], [YearMonth]) VALUES (N'A ', 4345, 201602)

  GO

  INSERT [dbo].[Account] ([Account_name], [Ammount], [YearMonth]) VALUES (N'A ', 4679, 201607)

  GO

  INSERT [dbo].[Account] ([Account_name], [Ammount], [YearMonth]) VALUES (N'A ', 5476, 201603)

  GO

  INSERT [dbo].[Account] ([Account_name], [Ammount], [YearMonth]) VALUES (N'A ', 5799, 201608)

  GO

  INSERT [dbo].[Account] ([Account_name], [Ammount], [YearMonth]) VALUES (N'A ', 7689, 201605)

  GO

  INSERT [dbo].[Account] ([Account_name], [Ammount], [YearMonth]) VALUES (N'A ', 23293, 201601)

  GO