October 13, 2016 at 12:15 am
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)".
October 13, 2016 at 11:39 am
Try this . This helped me --
October 14, 2016 at 3:59 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply