SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 bcp export to excel
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

boonboon
Starting Member

5 Posts

Posted - 09/19/2013 :  04:46:52  Show Profile  Reply with Quote
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

India
22755 Posts

Posted - 09/22/2013 :  12:53:31  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

boonboon
Starting Member

5 Posts

Posted - 09/23/2013 :  21:30:54  Show Profile  Reply with Quote
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?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 09/24/2013 :  05:07:39  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

boonboon
Starting Member

5 Posts

Posted - 09/24/2013 :  21:47:13  Show Profile  Reply with Quote
thanks.

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

is like PRINT(@sql) ?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 09/25/2013 :  05:45:12  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
yes do that and see if the statement returned has no syntax error

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2060 Posts

Posted - 09/30/2013 :  11:47:04  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

Conari
Starting Member

5 Posts

Posted - 06/10/2014 :  03:19:22  Show Profile  Reply with Quote
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
Go to Top of Page

Conari
Starting Member

5 Posts

Posted - 06/10/2014 :  03:25:38  Show Profile  Reply with Quote
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)
Go to Top of Page

Conari
Starting Member

5 Posts

Posted - 06/10/2014 :  03:36:53  Show Profile  Reply with Quote
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)
Go to Top of Page

Conari
Starting Member

5 Posts

Posted - 06/10/2014 :  03:59:07  Show Profile  Reply with Quote
This process can be done in the character set setting?
Because Ç> €, Ö> ™, S>?, ...
Go to Top of Page

Conari
Starting Member

5 Posts

Posted - 06/11/2014 :  04:58:51  Show Profile  Reply with Quote
why nothing answer the question?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000