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