Hi Jeff,

Thank you for the suggestions.

In output file, I need to add header and footer.

By loading output of procedure in temp table, I'll derive the header.

Later, use the xp_cmdshell to copy file to destination.

Sample code:

IF object_id('tempdb..##SwitchInput') IS NOT NULL DROP TABLE ##SwitchInput;

DECLARE

@tempsql AS varchar(max)

,@execstring AS varchar(max) = 'exec ash_test;';

SET @tempsql = 'SELECT * INTO ##SwitchInput FROM OPENROWSET(''SQLNCLI'', ''Server=vm01;Trusted_Connection=Yes;'','''+@execstring+''')';

EXEC (@tempsql);

DECLARE

@hdrrow AS nvarchar(max) = 'SELECT '''

,@ftrrow AS varchar(50) = ' UNION ALL SELECT ''End of File'' AS rowdata'

,@delmtr AS nvarchar(10) = CHAR(124)

,@effdate AS date = '2016-8-30'

,@destloc AS varchar(max) = 'C:\SSData\'

,@filename AS varchar(250) = 'Strategy_Update_Output_';

SELECT @hdrrow = @hdrrow + name + @delmtr FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..##SwitchInput') ORDER BY column_id ASC;

SET @hdrrow = LEFT(@hdrrow,LEN(@hdrrow)-1) + ''' AS rowdata UNION ALL ';

DECLARE @selectstring AS varchar(max), @detailrow AS varchar(max), @trailerrow AS varchar(max), @dsql AS varchar(max), @bcpsql AS varchar(8000);

SELECT @selectstring = +'[' + name + ']' FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..##SwitchInput') AND column_id = 1;

SELECT @selectstring = @selectstring + '+''' + @delmtr + '''+[' + name + ']' FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..##SwitchInput') AND column_id > 1 ORDER BY column_id ASC;

SET @detailrow = 'SELECT ' + @selectstring + ' AS rowdata FROM ##SwitchInput';

SET @dsql = @hdrrow + @detailrow + @ftrrow

SELECT @bcpsql = 'EXEC master..xp_cmdshell ''BCP "'+REPLACE(@dsql,'''','''''')+'" queryout "'+@destloc+@filename+CONVERT(varchar,@effdate,112)+'_'+FORMAT(GETDATE(),'yyyyMMddHHmmss')+'.txt" -c -T -C 1252''';

EXEC (@bcpsql);

This script is generic and needs work with any procedure that produces output records.

For this procedure, I'm expecting following output:

str1|str2

|I am top most proc

End of File