Export Query results to Excel with Column names

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Export Query results to Excel with Column names

Author

Topic

neeraj1401
Starting Member

36 Posts

Posted - 2008-12-03 : 00:47:48

Hi,

I'm tring to export all table to xls file with header , I'm using the following code its working fine for tables who have less columns but i'm geeting some error for large table (having more then 100 columns).please sugesst .............

@columnConvert is taking only 4000 character .........i already defined its length to 8000

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 00:50:08

Duplicate

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115562

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources