sending query output to a text file

Question

sending query output to a text file

Vijay-251247

Say Hey Kid

Points: 684
More actions
September 5, 2006 at 5:46 am

#114314

Hi there!
Is there an easy way to direct SQL query output to a text file? ... For instance, to run SELECT * FROM authors and send the results to a text file, ...
I need this in T-sql only. Not in VB script
Thanks

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

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

Yep... use OSQL to run the query from a CMD prompt (or using xp_CmdShell w/ trusted connection) with a redirect (the -o option) to a text file.

See "Books Online" for the full syntax... if you run into problems with the syntax, post back and we'll help...

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

Vijay-251247 Say Hey Kid Points: 684 More actions · Answer 2

Hi jeff

i tried below but can't get round it. Please help.

DECLARE @cmd VARCHAR(5000)

SET @cmd = 'OSQL -S support-ts2 -dNCL_REMOTE_SP6D '

+ ' -Usa -Pseaward'

+ ' -Q"select notes from person where len(notes) > 1000"'

+ ' -oc:\authors.txt'

EXEC master..xp_cmdshell @cmd, NO_OUTPUT

c:\authors.txt is the file i wanna write to.

Thanks a lot mate

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

One thing you can do is this :

DECLARE @cmd VARCHAR(5000)

SET @cmd = 'OSQL -S support-ts2 -dNCL_REMOTE_SP6D '

+ ' -Usa -Pseaward'

+ ' -Q"select notes from person where len(notes) > 1000"'

+ ' -oc:\authors.txt'

PRINT @cmd

--my results

OSQL -S support-ts2 -dNCL_REMOTE_SP6D -Usa -Pseaward -Q"select notes from person where len(notes) > 1000" -oc:\authors.txt

then manually run that in OSQL and make corrections untill you get it working. Then come back and fix the string.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

Vijay,

You can debug the query by checking the authors.txt file.. It contains the vital error information

--Ramesh

Vijay-251247 Say Hey Kid Points: 684 More actions · Answer 5

thanks guys..

i managed to do it...

DECLARE @cmd VARCHAR(5000)

SET @cmd = 'OSQL -S support_svr1 -d NCLREMOTE_SP6D '

+ ' -U sa -P seaward'

+ ' -Q "select notes from person where len(notes) > 1000"'

+ ' -o c:\Notes.txt'

EXEC master..xp_cmdshell @cmd, NO_OUTPUT

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

BTW you might want to change your login credentials now .