Thanks for the quick reply... here is my SP.
USE [MES - Reporting]
GO
/****** Object: StoredProcedure [dbo].[MBT_RPT_Export_Excel_TEST] Script Date: 10/26/2009 17:08:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 10/24/09
-- Description:Used to export data to Excel
-- =============================================
ALTER PROCEDURE [dbo].[MBT_RPT_Export_Excel_TEST]
--@filename varchar(100),
--@sql varchar(8000),
--@reportid int,
--@msgsubject varchar(1000),
--@bodytext varchar(1000),
--@reccount int OUT
AS
BEGIN
Declare @filename varchar(100)
Declare @sql varchar(8000)
--**Added**
Declare @sorttext varchar(500)
--**
Declare @reportid int
Declare @msgsubject varchar(1000)
Declare @bodytext varchar(1000)
Declare @reccount int
DECLARE @tempSQL varchar(8000)
DECLARE @columnNames varchar(8000)
DECLARE @columnConvert varchar(8000)
DECLARE @bcpCommand VARCHAR(8000)
DECLARE @distlist NVARCHAR(MAX)
DECLARE @distlist_cc NVARCHAR(MAX)
DECLARE @distlist_bcc NVARCHAR(MAX)
Set @filename = 'G:\SQLData\restor\RPT-DispenseDaily.xls'
Set @sql = 'Select * from [Rpt - Daily Dispense Detail]'
Set @reportid = 1
Set @msgsubject = 'Dispense Detail: ' + convert(varchar,GetDate(),101)
Set @bodytext = 'Attached please find the Dispense Detail Report'
Set @reccount = 0
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
select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' +
substring(@sql, charindex('from', @sql)-1, len(@sql))
EXEC(@tempSQL)
-- 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 ',100'
--+ 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 ',100'
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
SET @bcpCommand = 'bcp "SELECT * from ##TempExportData2" queryout "'
SET @bcpCommand = @bcpCommand + @filename + '" -c -w -T -S"' + @@Servername + '"'
--Print @bcpCommand
EXEC master..xp_cmdshell @bcpCommand
Drop Table ##TempExportData
Drop Table ##TempExportData2
Exec MBT_RPT_GetDistList @reportid, @distlist OUT, @distlist_cc OUT, @distlist_bcc OUT
EXEC msdb.dbo.sp_send_dbmail
@recipients=@distlist,
@copy_recipients=@distlist_cc,
@blind_copy_recipients=@distlist_bcc,
@subject = @msgsubject,
@file_attachments = @filename,
@body = @bodytext ;
END