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
>.
>

Batch invocation of Drillthrough action

Does anyone happen to have an example of a batch invocation of a drillthrough action?

Essentially, what I'd like the ability to do is use the results of the drillthrough to feed into another process (i.e., to use as input to an SSIS package or load into a relational table.) However, I've found no examples of what the action settings would need to be in the Application field, for example.

Maybe drillthrough isn't the proper action to define it (although it would be the easiest.) Maybe a regular action returning a dataset or rowset? Any help or idea is a appreciated.

Not sure what you trying to achieve. The drillthrough is invoked in a content of the particular cell.

You can also take a look at the syntax of the DRILLTHOUGH statement http://msdn2.microsoft.com/en-us/library/ms145964.aspx

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I think what he means is doing individual drillthroughs on a bunch of cells, and then combining all the drillthrough results.

I had a similar need for this as well, awhile back

Thanks,

JGP

|||JGP, you're situation is another aspect of what I'm attempting to accomplish.

As an example (using Adventure Works), let's say I have the customer.education on the Y-axis and something else, say Product Categories, on the X-axis. My measure is Internet Order Quantity. My filter criteria is Customers in the US. I've sliced it up until I had found that 461 orders for Mountain Bikes were placed by Customers with a Graduate degree.

Now, that could be one customer purchasing 461 mountain bikes, or 461 customers purchasing one each. Regardless, there are customers associated with each order represented in that cell and I want to create a list of those people from my grid. With an action, I was hoping to do maybe a batch drillthrough that, rather than having the results back to the screen, output to a table. So far, I've not found how to do that, or if it even can be done.

Really, I can't find any examples of what a mechanism a batch action uses. All examples are of interactive reports, URLs, and drillthroughs.