Writing select results to a file

Question

Post reply

Writing select results to a file

Angelindiego

SSCertifiable

Points: 6048
More actions
December 29, 2008 at 4:07 pm

#126686

I read a forum article about writing to a file, but it was within the context of while I was in Query Analyzer. What I need to do is do a simple test script doing some selects from sysobjects. I want the results to be logged to a file. If my test script looks like this (see below), how do I write the results to a file?? Thank you in advance!! Happy New Year all!!
SELECT
name
, crdate
, refdate
FROM sysobjects
WHERE
type = 'P' AND name = 'GetLocationsAll'
SELECT
name
, crdate
, refdate
FROM sysobjects
WHERE
type = 'P' AND name = 'GetLocationsByCode'
etc.......(13 total)
Thank you!!,
Angelindiego

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

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

You have a couple of options, but they aren't things you can specify in T-SQL.

BCP can run queries and put their output in a file.

http://www.sqlservercentral.com/scripts/Backup+%2F+Restore/30027/

So can SSIS.

http://www.sqlservercentral.com/Forums/Topic525180-148-1.aspx

There are lot more articles and forums on this site if you do a search on results to file.

Happy New Year to you, too. 😀

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

If the file is a "continuous" log instead of a file to be created on the fly, look into the simplicity of making a Text based linked server... a pretty good example is in Books Online under linked servers.

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

Angelindiego SSCertifiable Points: 6048 More actions · Answer 3

thank you to both of you for the good ideas!! I will read the articles in the am.

In this instance, all I want to do is check to see if these sp's exist, and what their create dates were. Drop the results into a file I can go read and then be done with it. I can see myself using this file idea over and over in the course of what I do, so I wanted to document the process. It is not going to be any big deal...I just don't know how to do it!! LOL....

thanks again guys!!

Thank you!!,

Angelindiego

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

You could also save the selects in a scriptfile, make a simple (dos) batchfile and use sqlcmd with the script as input and >> to an outputfile.

It doesn't need to be overly complicated 😉

/Kenneth

Angelindiego SSCertifiable Points: 6048 More actions · Answer 5

Kenneth, that is what I want to do, I just don't know the syntax! Never written to a file before....

Thank you!!,

Angelindiego