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