T-SQL command for including header column names in data output

Question

Post reply

T-SQL command for including header column names in data output

A-214252

Mr or Mrs. 500

Points: 503
More actions
April 5, 2005 at 2:51 pm

#67381

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

wz700 SSCertifiable Points: 7133 More actions · Answer 1

try isqlw.

You can change default session setting by making changes in sql query analyzer.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

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)

wz700 SSCertifiable Points: 7133 More actions · Answer 3

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 /?

Calvin Lawson SSChampion Points: 11030 More actions · Answer 4

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

A-214252 Mr or Mrs. 500 Points: 503 More actions · Answer 5

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.

Calvin Lawson SSChampion Points: 11030 More actions · Answer 6

"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