bcp out..

I need to provide a user with the ability to export the
data from 1 view into a .csv file at any time he needs to
see the data.
I have tried using a commmand line bcp out, but the data
does not come out in a format that is highly useable to
the user.
How can I make it so that bcp out exports to the .csv in
format that it exists in the database ie. distinct
columns and rows.
Also, what does the user need to bcp out data? Is select
permissions for his windows account enough?
Thanks,
MariaMaria
Ex
bcp "select * from northwind..orders" queryout c:\cust.xls -c -S<server> -Usa -
above command will create a file with extension XLS but, in fact its a tab delimited flat file since it has extension XLS by default it will get opened in EXCEL and tab character is considered as a column delimiter. I've put TAB as a column delimiter assuming this character is not used in any of the varchar/char field. because if you are including such a character which exists in the varchar/char datatype whole format will get disturbed. comma seperated file/CSV file is a good example for this. Also point to be noted that if any of the char/varchar field contains TAB character even this file's format will get disturbed.So basically you have to make sure that the varchar columns for which you are exporting the data should not contain the character that is being used as a field seperator otherwise it will lead to
you can also run above command line using xp_cmdshell and thus can be used in T-SQL script
E
exec master..xp_cmdshell 'bcp "select * from northwind..orders" queryout c:\cust.xls -c -S<server> -Usa -P
Also refer to following UR
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b30612
>Also, what does the user need to bcp out data? Is select
>permissions for his windows account enough
Yes,
- Visha|||You could also connect to the database via ODBC and an
Excel spreadsheet. Open Excel, go to data/get external
data/new dB query. connect using the ODBC setting and
enter the query needed. The user will then have the option
of refreshing the data at their leisure and can
automatically save it as an CSV file.
>--Original Message--
>I need to provide a user with the ability to export the
>data from 1 view into a .csv file at any time he needs to
>see the data.
>I have tried using a commmand line bcp out, but the data
>does not come out in a format that is highly useable to
>the user.
>How can I make it so that bcp out exports to the .csv in
>format that it exists in the database ie. distinct
>columns and rows.
>Also, what does the user need to bcp out data? Is select
>permissions for his windows account enough?
>Thanks,
>Maria
>.
>