T-SQL command for including header column names in data output
-
Hey all, I'm in report generating mode for a project. I BCP output data for the different "reports" I've generated. Is there a way to include the column headers in my flat file extract? (Instead of having just raw data).
(Cannot use DTS, etc..)
-
try isqlw.
You can change default session setting by making changes in sql query analyzer.
-
I believe ISQLW will launch Query Analyzer. I think you meant ISQL and it is fairly well deprecated compared to OSQL. That, not withstanding, if you use OSQL to run a query (as wz700 eluded to), you can direct the output to a file using either the -o switch or the ">" redirection character. You will also get those pesky dashed lines unless you get clever by turning off the headers and using a UNION to select headers followed by data.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I mean isqlw.
Try this.
save following sql to file (test.sql)
select * from pubs.dbo.authors
Change default session setting
open sql query analyzer, Tools/Options,
In Results tag,
tick Print column headers(*) check box.
select Comma Delimited(CSV) in Results output format
*)In Connection Properties tag,
tick set nocount check box.
Ok to close.
At dos prompt type;
isqlw -i test.sql -S(local) -E -o testout.txt
check the output file testout.txt.
You can get syntax by isqlw /?
-
that's pretty stupid that you can't use DTS; it's by far the easiest way to do this.
Look in BOL under:
bcp utility, format files
bcp utility, accessing and changing data:
bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-m max_errors] [-f format_file] [-e err_file]
[-F first_row] [-L last_row] [-b batch_size]
[-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
[-q] [-C code_page] [-t field_term] [-r row_term]
[-i input_file] [-o output_file] [-a packet_size]
[-S server_name[\instance_name]] [-U login_id] [-P password]
[-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]
Signature is NULL
-
I'm running these reports on a timed basis, hence using T-SQL scripts in JOBS. DTS stinks, and is foolishly limited in what it can do. There are no format commands to include column names.
-
"DTS stinks, and is foolishly limited in what it can do. There are no format commands to include column names."
Uh...OK; that's totally wrong. In DTS, the "text file (destination)" object has a property (radio button through the GUI) to include column headers or not. Simple as that.
As for running them on a "timed" basis; you can easily execute DTS from the command line using DTSRun and xp_cmdshell, or even simpler, schedule the package under SQLAgent.
DTS does not stink; it works very well for simple projects, and can be made to work for more complex projects with a little creativity and VB 101 (OK, I'll grant you that VB sucks, but at least it's really easy to program).
Using DTS I can set up a data extract/import (to staging) in under 5 minutes, good luck trying to do that with BCP.
Signature is NULL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply