Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

BCP command to export data to excel with column headers Expand / Collapse
Author
Message
Posted Tuesday, October 27, 2009 8:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 9:05 AM
Points: 6, Visits: 59
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
Post #809352
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse