Saving Query Results to .txt using Scripts

Question

Saving Query Results to .txt using Scripts

Nishernd

Mr or Mrs. 500

Points: 561
More actions
September 15, 2009 at 7:07 am

#135017

Hi All,
I need to save the results of any Query to a delimited .txt (Any Delimiter)
however i need it to be saved from within a SQL Proc, which will be called every night
in a job.
Any Help Please...

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

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

Chand00 SSCertifiable Points: 6466 More actions · Answer 1

Hi,

You can use bcp with xp_cmdshell or openrowset. Please refer to the following links. I recommend you to use open rowset. Please refer the following links.

bcp with xp_cmdshell

http://www.mssqltips.com/tip.asp?tip=1633

openrowset

http://www.mssqltips.com/tip.asp?tip=1202

[font="Verdana"]Thanks
Chandra Mohan[/font]

Lowell SSC Guru Points: 323504 More actions · Answer 2

both bcp and the more robust sqlcmd have the ability to be fed a query and output the results to a file;

that is your best choices, unless you write/find a CLR to do it for you instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Nishernd Mr or Mrs. 500 Points: 561 More actions · Answer 3

Nishernd

Mr or Mrs. 500

Points: 561

September 15, 2009 at 8:08 am

#1053793

Thanks Guys,

Im looking into it now...

steveb. SSC-Forever Points: 46733 More actions · Answer 4

If you are using a job to send the results to a .txt file, the one alternative way would be to create a SSIS package to extract the data and create the file and then schedule this package as run as your nightly job.,

amns SSCrazy Points: 2628 More actions · Answer 5

Hi,

i faced a similar problem and i created a stored procedure that uses bcp, fmt format file and xp_cmdshell..

The code follow this:

select @cmd = 'bcp "SELECT column from table" queryout ' D:\exported.txt -f D:\format.fmt - T

EXEC @rc = master..xp_cmdshell @cmd

IF @rc 0

BEGIN

PRINT 'cmd faillure!'

return 1

END

ELSE

BEGIN

Print 'cmd execution ok!'

return 0

END