Output an SQL Query to a text file

[Alex]

Hi,

How would I go about writing the results of an SQL query to a text
file?

I cannot find any info in the Online help files.

For example, I would like the results of:

SELECT * FROM TableA

To be written to the file result.txt
Alex

[Query Builder]

Open query analyzer. Go to Query and select Result to file.... and
during execution, it will prompt a file name....

[Alex]

Thanks but I cannot do that.

This must query must be invisible to the end user.

For example, if a user performs some function on my system, I wish to
execute a query on what they have selected and output the results to a
file. I do not want my user to be selecting Save As etc..

[Query Builder]

First your question is not clear. I am still not sure what you are
trying to accomplish.

First if you have a query that you dont want your user to know, wrap it
up in a stored proc and schedule it in a job and let the user execute
the job.

Another easy way is to create a dts and let the user run the dts that
should run the query and output the file.

Hope this helps..!

[Alex]

Ok, I have no idea what a dts is!!

Is it possible to output the results from a stored procedure to a
specified file.

I do not want to use a tool or anything, just simply write the results
from a query to a file.

[JD]

Alex,
You might try adding this to your connection string: -o '<path
name>\results.txt.

such as: isql -SMyserver -Uusera -Puserapassword -i mysqlquery.sql -o
results.tx

hth

"Alex" <al**********@lineone.net> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Hi,

How would I go about writing the results of an SQL query to a text
file?

I cannot find any info in the Online help files.

For example, I would like the results of:

SELECT * FROM TableA

To be written to the file result.txt
Alex

[Gregory Dean]

On 2/14/05 4:24 PM, in article
11**********************@z14g2000cwz.googlegroups. com, "Alex"
<al**********@lineone.net> wrote:

Ok, I have no idea what a dts is!!

Is it possible to output the results from a stored procedure to a
specified file.

I do not want to use a tool or anything, just simply write the results
from a query to a file.

There is no OUTPUT TO FILE argument in a SELECT clause. You can SELECT INTO
a temporary table but this doesn't help you. The simplest thing to do (path
of least resistance) is to use a program in the middle to handle the query
and writing the recordset into a file. For example, if you wanted to create
an .ASP web form you could simply ask for the customer's email address, run
a query and send them the results. You could write a VB app or VB script to
run locally and generate a file from your query.

-Greg

[louis]

There isn't a direct method. Two possible workarounds are OSQL and
BCP. I tend to use BCP. BCP only lets you output from a permanent
table, though.

I would create a table called "CACHE" where one of the columns is "IP"
and the other cols match the SELECT query. Then store the output into
CACHE along with the user's IP. Then run BCP to output from CACHE.

[Erland Sommarskog]

Alex (al**********@lineone.net) writes:

Ok, I have no idea what a dts is!!

Is it possible to output the results from a stored procedure to a
specified file.

I do not want to use a tool or anything, just simply write the results
from a query to a file.

No, you don't want to do that. Yes, you can invoke xp_cmdshell and from
there do all sorts of things. But then your users would have to have
permission to use xp_cmdshell, and that could be a security issue.

It's not clear why you want to write to a file, but if you want to
log some stuff for debug reasons - write to a table instead. You are
in a database, not in a file system.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Alex]

Ok this is great, the tables I need data outputting from are permanent.

Basically it is for creating a log file. The database is huge, but the
log only needs to contain the contents of a few tables. It has to go to
a file because I want to be able to save the results of the query to
disk or print it out.

Can I BCP out from several tables into the same file?

i.e. I need SELECT * from TableA, TableB, TableC to result.txt

Security is not an issue.

[louis]

Alex wrote:

Ok this is great, the tables I need data outputting from are permanent.
Basically it is for creating a log file. The database is huge, but the log only needs to contain the contents of a few tables. It has to go to a file because I want to be able to save the results of the query to
disk or print it out.

Can I BCP out from several tables into the same file?

i.e. I need SELECT * from TableA, TableB, TableC to result.txt

Security is not an issue.

It's a little involved but you can write a select statement joining
several tables. Each time BCP executes, I believe it overwrites the
text file.

Check out this article:
http://www.sqlteam.com/item.asp?ItemID=4722