December 3, 2008 at 5:06 am
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
May 5, 2009 at 5:36 pm
post error caused dup response
May 5, 2009 at 5:36 pm
I was here looking and working with this. Your problem is that the passed in string was being used within SP, but exceeded its max and ended up with miss formed syntax.
I changed it to (max) and this worked fine. I also had to declare a BCPSQL string as xp_cmdshell does not like (max).
Other than these 2 changes, this works really well to combine Header with Data.
create proc spExportData_n
(
@dbName varchar(100) = 'master',
@sql Nvarchar(max) = '',
@fullFileName varchar(100) = ''
)
as
...declare...
, @tempSQL varchar(8000)
...
Exec master..xp_cmdshell @tempSQL
May 5, 2009 at 5:36 pm
post error caused dup response
May 21, 2010 at 10:30 pm
This looks like it might be really useful. Has anyone managed to get it to work? All it does for me is generate a list of errors when I execute the stored procedure.
The errors include: Error = [Microsoft][SQL Native Client]Unable to resolve column level collations
and: BCP copy out failed
I'm assuming the argument @sql is a query string, and @fullFileName is the output file path and name.
For @sql I tried
' select col1, col2, col3 from source_table' Then I tried leaving off 'select' -- both failed.
I tried just the table name, and tried it with 'dbo.' -- nothing works.
I changed the syntax of the BCP to what works for me in other procedures, but can't get this to do anything, but that may be because the rest of the procedure has already failed.
May 21, 2010 at 11:09 pm
I found the SCRIPT - By Samardeep Khera, 2009/10/01
Thanks Samardeep for noodling that one out. I really appreciate the hints. It worked first time out!:-)
July 11, 2011 at 4:09 am
Hi all.
I am trying to export data from Sql Server table using BCP utillty,
I want to add Column header to my Excel .
Can anyone guide me how i can do this..
Thanks
Deepika Goyal
July 11, 2011 at 7:56 am
if you have to do this via bcp and not SSIS, then there are two ways that i can think of.
one exports a global temp table, and the other uses the command line COPY command to append two files together.
--BCP to get headers
--Version 1: global temp table
--not i'm making sure my headcers are large enough for my data i insert later....
--otherwise CITYNAME wouldbe varchar(8), and I need maybe 100
SELECT
identity(int,1,1) AS BCPORDERID,
CONVERT(varchar(100),'CITYTBLKEY') AS CITYTBLKEY,
CONVERT(varchar(100),'CITYNAME') AS CITYNAME,
CONVERT(varchar(100),'COUNTYTBLKEY') AS COUNTYTBLKEY,
CONVERT(varchar(100),'COUNTYNAME') AS COUNTYNAME,
CONVERT(varchar(100),'COUNTYFIPS') AS COUNTYFIPS,
CONVERT(varchar(100),'STATETBLKEY') AS STATETBLKEY,
CONVERT(varchar(100),'STATECODE') AS STATECODE,
CONVERT(varchar(100),'CITYDESCRIP') AS CITYDESCRIP
INTO ##BCP_Results
INSERT INTO ##BCP_Results(CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP)
SELECT
CITYTBLKEY,
CITYNAME,
COUNTYTBLKEY,
COUNTYNAME,
COUNTYFIPS,
STATETBLKEY,
STATECODE,
CITYDESCRIP
FROM VW_CITYCOUNTYSTATE
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP FROM ##BCP_Results ORDER BY BCPORDERID" queryout MyFileName.txt -c -T'
--drop table ##BCP_Results
--Version TWO:
--export two files, then combine them together.
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT ''CITYTBLKEY'',''CITYNAME'',''COUNTYTBLKEY'',''COUNTYNAME'',''COUNTYFIPS'',''STATETBLKEY'',''STATECODE'',''CITYDESCRIP''" queryout MyFileNameT1.txt -c -T'
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP FROM VW_CITYCOUNTYSTATE" queryout MyFileNameT2.txt -c -T'
set @sql ='copy c:\MyFileNameT.txt + c:\MyFileNameT2.txt c:\MyFileName.txt'
EXEC master..xp_cmdshell @sql
Lowell
July 13, 2011 at 12:15 am
Thanks for this.. i will try this..
1 More thing:
i have one Sql Table and a temp table..
i want to extract whole data from Sql table to temp table without giving any
field name..
like INSERT INTO ##temp
SELECT * FROM [Table name]
But its not working.
any help?????
July 13, 2011 at 12:41 am
"is not working" is just not enough information...
Would you mind posting the error message?
July 13, 2011 at 3:54 am
Hi,
This is an Error
Msg 213, Level 16, State 1, Line 5
Insert Error: Column name or number of supplied values does not match table definition.
I want to copy all the rows from Sql table(table contains 50 fields) to Temp table and then extracting the records to Execl through BCP..
Extractiong data to EXcel i will do..but how to copy data from 1 table to another without giving any fileld name ?? i dnt know.
Thanks a lot ..if i get some solution.
Its urgent
Deepika
July 13, 2011 at 6:24 am
the error's pretty clear. the temp table you are inserting into doesn't match your select *.
either create a table explicitly for your select *, or you have to identify the columns.
are you reusing that ##global table for a second table?
Lowell
July 13, 2011 at 8:49 pm
July 18, 2011 at 12:31 am
Hi..
Its' Working
Thanks..
February 15, 2012 at 3:03 am
This script rocks, thanks to the blogger
<a href="http://dotnetkeeda.blogspot.in/2009/09/bcp-command-to-export-data-to-excel.html"> BCP export to excel</a>
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply