bcp export to excel - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

bcp export to excel

Author

Topic

boonboon
Starting Member

5 Posts

Posted - 2013-09-19 : 04:46:52

Encountered this error while exec the stored procedure

EXEC proc_generate_excel 'LIVE', 'Shelflabel_Plano_Master','f:\plano\file.xls'

SQLState = 37000, NativeError = 102
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '-'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

This is the store procedure that I used, can be found in the internet :-

CREATE procedure proc_generate_excel
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select
@columns=coalesce(@columns+',','')+column_name
from
information_schema.columns
where
table_name=@table_name

select @columns = '''''' + replace(@columns,',',''''',''''') + ''''''

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select '+@columns+' as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-09-22 : 12:53:31

Can you print each @sql and see if they have valid statements? Also does any of the column name has - (hyphen) ?

Madhivanan

Failing to plan is Planning to fail

boonboon
Starting Member

5 Posts

Posted - 2013-09-23 : 21:30:54

quote:
Originally posted by madhivanan

Can you print each @sql and see if they have valid statements? Also does any of the column name has - (hyphen) ?

Madhivanan

Failing to plan is Planning to fail

thanks. can I check what if my column name have under score _ will it affect?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-09-24 : 05:07:39

quote:
Originally posted by boonboon

quote:
Originally posted by madhivanan

Can you print each @sql and see if they have valid statements? Also does any of the column name has - (hyphen) ?

Madhivanan

Failing to plan is Planning to fail

thanks. can I check what if my column name have under score _ will it affect?

Underscore will not be a problem but the hyphen is

Madhivanan

Failing to plan is Planning to fail

boonboon
Starting Member

5 Posts

Posted - 2013-09-24 : 21:47:13

thanks.

sorry, I am not very good at SQL. Can I check on every @sql I do a print?

is like PRINT(@sql) ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-09-25 : 05:45:12

yes do that and see if the statement returned has no syntax error

Madhivanan

Failing to plan is Planning to fail

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-09-30 : 11:47:04

As an alternative to this method , you can use Powershell to export SQL Server data to Excel - this will give you more granular control . There is a sample script here: http://www.sqlserver-dba.com/2013/05/sql-server-export-to-excel-with-powershell.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Conari
Starting Member

5 Posts

Posted - 2014-06-10 : 03:19:22

Hello all
I am using this SP, but the resulting null.
table name missing message.
I am USE sql2012

Message,
---------------
NULL
Starting copy...
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 47 Average : (21.28 rows per sec.)
NULL

NULL
Starting copy...
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'dbname.tablename'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Unable to resolve column level collations
NULL
BCP copy out failed
NULL

Conari
Starting Member

5 Posts

Posted - 2014-06-10 : 03:25:38

This SP is only field name create excel,

procedure [dbo].[proc_generate_excel_with_columns]
( @SERVERNAME VARCHAR(100),
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)

as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)

select
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
from
information_schema.columns
where
table_name=@table_name

select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\datam.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c -S'+@SERVERNAME+' -T '''

exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+'['+@db_name+']'+'.'+@table_name+'" queryout "'+@data_file+'" -c -S'+@SERVERNAME+' -T '''
PRINT @sql
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> '+@file_name+''''

exec(@sql)

--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

Conari
Starting Member

5 Posts

Posted - 2014-06-10 : 03:36:53

the problem was solved. :)

I did place the following pairs of points.
--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+ )'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

Conari
Starting Member

5 Posts

Posted - 2014-06-10 : 03:59:07

This process can be done in the character set setting?
Because Ç> €, Ö> ™, S>?, ...

Conari
Starting Member

5 Posts

Posted - 2014-06-11 : 04:58:51

why nothing answer the question?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources