Dear All,

    How to store Column name and Row name in text File using SQL Server?  I am using following query for create Text file in the local driver for getting data from table.I need to display Column name in the text File . How to do this?

DECLARE @FileName varchar(50),
        @bcpCommand varchar(2000)

SET @FileName = REPLACE('D:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "SELECT sono as [SO No] FROM DB Name..tbl_ss" queryout "' 
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P pw -c'
EXEC master..xp_cmdshell @bcpCommand
PRINT @bcpCommand

One way is this workaround

https://connect.microsoft.com/SQLServer/feedback/details/288800/bcp-out-to-include-field-columns-names

ANother way is to do like this in your script itself

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/10/10/export-to-excel-with-column-names.aspx

just replace the excel part with what you have now

That's the age old problem: By default you cannot get column name from BCP command, however you can do this trick in your BCP command:

SET @bcpCommand = 'bcp "SELECT sono as [SO No] FROM DB Name..tbl_ss" queryout "'

CHANGE THIS TO:

SET @bcpCommand = 'bcp "SELECT '[SO No]'  as [SO No] UNION SELECT sono as [SO No] FROM DB Name..tbl_ss" queryout "'

This will get you "[SO No]" as first record, which in turn is actually the column header

HTH