Exporting data from stored procedure to excel spreadsheets

 --'Select Job_T_Job_Desc,Job_T_Job_ID from Pmsclient.dbo.PMS_Job_TManager_h 
 --'Where Job_T_Job_ID=''''JON78''''','c:\bcp\Siraj.xls'
alter Procedure [dbo].[Pms_Usp_GetBackupXlsResult1]    
      
(      
 @server Varchar(100),  
 @DynamicSql Varchar(Max),    
 @fullFileName Varchar(1000)    
    
)      
AS      
Begin      
    
--Declare @DynamicSql  Varchar(Max)  
--Set @DynamicSql='select top 5 * from test.dbo.customer'  
  
--Declare @server varchar(500)  
--Set @server='shrigi104\SHRIUNO0301'  
--Select @server  
  
       
Declare @ExcSql Varchar(Max)    
--Set @ExcSql='SELECT * INTO ##MyTempTable FROM OPENROWSET(''SQLNCLI'', 
    ''Server='+@server+';Trusted_Connection=yes;'','''+@DynamicSql+''')'    
Set @ExcSql='SELECT * INTO tempdb.dbo.##MyTempTable FROM OPENROWSET(''SQLNCLI'',
    ''Server='+@server+';UID=SA;PWD=welcome3#'',''SET NOCOUNT ON;SET FMTONLY OFF; '+@DynamicSql+''')'    
      Select (@ExcSql)  
     Exec (@ExcSql)    
     Select (@ExcSql)    
Declare @dbName Varchar(100)   
set @dbName= 'master'         
Declare  @sql varchar(5000)   
set @sql= 'Select * from tempdb.dbo.##MyTempTable'            
--Declare   @fullFileName varchar(100)   
--set @fullFileName= 'C:\bcp\ss.xls'        
Declare @dbName1 Varchar(100)     
if object_id('##TempExportData') is not null        
    drop table ##TempExportData        
if object_id('##TempExportData2') is not null        
    drop table ##TempExportData2    
          
    Select @dbName1=@dbName      
    Select @dbName = 'use ' + @dbName + ';'        
      
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)        
Select (@dbName + @tempSQL)        
      
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'        
Select @columnNames      
-- 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)        
Select @sql      
set   @sql = 'bcp " select * from ##TempExportData2 " queryout "' + @fullFileName + 
    '" -U uno -P uno   -c -T -S '+ @server   
Select @sql      
Exec master..xp_cmdshell @sql       
Drop table ##TempExportData      
Drop table ##TempExportData2      
Drop Table ##MyTempTable    
End