In this thread  it's shown how to export query results to excel File using OpenRowset. The problem with this is that you have to create an excel  file with columns. For me that is simply unacceptable because i had to export any query result to excel and having to make an excel file for each query is simply ridicolous. So I went looking into BCP. Now BCP can't export column names adn i saw some solutions that use a view but nothing really dynamic. So i had to do a little workaround.

It resulted in this stored procedure. Data is exported in BCP RAW format. It can also be exported to any format that BCP is familiar with.

use master
go
if object_id('spExportData') is not null
    drop proc spExportData
go
create proc spExportData 
(
    @dbName varchar(100) = 'master', 
    @sql varchar(5000) = '',     
    @fullFileName varchar(100) = ''
)
as
if @sql = '' or @fullFileName = ''
begin
    select 0 as ReturnValue -- failure
    return
end 
-- if DB isn't passed in set it to master
select    @dbName = 'use ' + @dbName + ';'
if object_id('##TempExportData') is not null
    drop table ##TempExportData
if object_id('##TempExportData2') is not null
    drop table ##TempExportData2
-- insert data into a global temp table
declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)
select    @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' + 

     substring(@sql, charindex('from', @sql)-1, len(@sql))
exec(@dbName + @tempSQL)
if @@error > 0
begin
    select 0 as ReturnValue -- failure
    return
end 
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT    @columnNames = COALESCE( @columnNames  + ',', '') + column_name,
        @columnConvert = COALESCE( @columnConvert  + ',', '') + 'convert(nvarchar(4000),' 
        + column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'
                             when data_type in ('numeric', 'decimal') then ',128'
                             when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
                             when data_type in ('datetime', 'smalldatetime') then ',120'
                             else ''
                        end + ') as ' + column_name
FROM    tempdb.INFORMATION_SCHEMA.Columns
WHERE    table_name = '##TempExportData'
-- execute select query to insert data and column names into new temp table
SELECT    @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] 
       from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
exec (@sql)
-- build full BCP query
select    @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW'
-- execute BCP
Exec master..xp_cmdshell @sql
if @@error > 0
begin 
    select 0 as ReturnValue -- failure
    return
end
drop table ##TempExportData
drop table ##TempExportData2
select 1 as ReturnValue -- success
go
declare @sql varchar(6800),    @dbName varchar(100), @fullFileName varchar(100)
select    @dbName = 'northwind', @sql = 'select * from orders order by orderdate', @fullFileName = 'e:\test.xls'
exec    master..spExportData @dbName, @sql, @fullFileName