export all table to excel with header using bcp

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

SQL Server Development (2000)

export all table to excel with header using bcp

Author

Topic

neeraj1401
Starting Member

36 Posts

Posted - 2008-12-03 : 00:39:26

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_n') is not null
drop proc spExportData_n
go
create proc spExportData_n
(
@dbName varchar(100) = 'master',
@sql varchar(8000) = '',
@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 -t, -T -Sserve -Usa -P'
-- execute BCP
Exec master..xp_cmdshell @sql
--print @sql
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
drop table ##TempExportData
drop table ##TempExportData2
set @columnNames =' '
set @columnConvert =' '
set @tempSQL =' '
select 1 as ReturnValue -- success

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 00:43:27

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/10/10/export-to-excel-with-column-names.aspx

neeraj1401
Starting Member

36 Posts

Posted - 2008-12-03 : 01:37:22

hi i'm geeting the following error

Query hints exceed maximum command buffer size of 1023 bytes (2909 bytes input).

please suggest

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 01:45:11

Are you using procedure in link i posted?

neeraj1401
Starting Member

36 Posts

Posted - 2008-12-03 : 01:46:00

yes i'm ...........

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 01:48:55

quote:
Originally posted by neeraj1401

yes i'm ...........

Can you show us how you executed the procedure?

Madhivanan

Failing to plan is Planning to fail

neeraj1401
Starting Member

36 Posts

Posted - 2008-12-03 : 01:53:17

EXEC proc_generate_excel_with_columns 'Reports_27Nov', 'CP_AM_ALTAG_NV8','\\wsn4gfgdee\neeraj'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 02:03:11

quote:
Originally posted by neeraj1401

EXEC proc_generate_excel_with_columns 'Reports_27Nov', 'CP_AM_ALTAG_NV8','\\wsn4gfgdee\neeraj'

Is neeraj a folder or file?

It should be

EXEC proc_generate_excel_with_columns 'Reports_27Nov', 'CP_AM_ALTAG_NV8','\\wsn4gfgdee\neeraj.xls'

Madhivanan

Failing to plan is Planning to fail

neeraj1401
Starting Member

36 Posts

Posted - 2008-12-03 : 02:18:09

Thanks ...........get the xls file but no coloumn header ..................

neeraj1401
Starting Member

36 Posts

Posted - 2008-12-03 : 02:35:41

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

use master
go
if object_id('spExportData_n') is not null
drop proc spExportData_n
go
create proc spExportData_n
(
@dbName varchar(100) = 'master',
@sql varchar(8000) = '',
@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 -t, -T -Sserve -Usa -P'
-- execute BCP
Exec master..xp_cmdshell @sql
--print @sql
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
drop table ##TempExportData
drop table ##TempExportData2
set @columnNames =' '
set @columnConvert =' '
set @tempSQL =' '
select 1 as ReturnValue -- success

neeraj1401
Starting Member

36 Posts

Posted - 2008-12-03 : 04:16:08

hi I'm facing problem with this query .........please suggest ....i'm new to sql server ......
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'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 04:16:16

quote:
Originally posted by neeraj1401

Thanks ...........get the xls file but no coloumn header ..................

Chenck again. You should get column names

Madhivanan

Failing to plan is Planning to fail

neeraj1401
Starting Member

36 Posts

Posted - 2008-12-03 : 05:18:46

hi Madhivanan,

I'm geeting the column name but some of my tables have more then 100 columns, for those table i'm facing problems......i just wanted to know is there any way to split the query so that it can take more column ........@columnConvert varible is geeting values in nvarchar is there any way to change it into varchar ...............

thanks in advance...........

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 05:37:49

quote:
Originally posted by neeraj1401

hi Madhivanan,

I'm geeting the column name but some of my tables have more then 100 columns, for those table i'm facing problems......i just wanted to know is there any way to split the query so that it can take more column ........@columnConvert varible is geeting values in nvarchar is there any way to change it into varchar ...............

thanks in advance...........

Which code are you talking about? Your code or proc_generate_excel_with_columns?

Madhivanan

Failing to plan is Planning to fail

neeraj1401
Starting Member

36 Posts

Posted - 2008-12-03 : 06:53:26

I'm talikng about following code ................

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'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 07:29:15

What happens when you use varchar(8000) instead of nvarchar(4000)?

Madhivanan

Failing to plan is Planning to fail

neeraj1401
Starting Member

36 Posts

Posted - 2008-12-03 : 07:34:37

Hi Madhivanan ,

Thanks for quick reply . I tried that to but still getting the same error ........

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 07:39:29

Do you get the same error?
Query hints exceed maximum command buffer size of 1023 bytes (2909 bytes input).

Madhivanan

Failing to plan is Planning to fail

neeraj1401
Starting Member

36 Posts

Posted - 2008-12-03 : 08:09:37

Madhivanan
No , in the above mention code @columnConvert is geting string more then 8000 byte or 4000 character.............so its giving the syntax error at run time .........

SP is working fine for some of my table but geting problems with the tables which have more 80 or 90 columns ..........please see the values of @columnconvert......

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 09:08:27

Try this logic

declare @s1 varchar(8000),@s2 varchar(8000)
select coalesce(@s1+',','')+column_name from information_schema.columns
where table_name='table' and ordinal_position between 1 and 3
print @s1
select coalesce(@s2+',','')+column_name from information_schema.columns
where table_name='table' and ordinal_position between 4 and 10
print @s2

Later concatenate @s1 and @s2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources