Output Query Results to Text File

Output Query Results to Text File

Post by Audre » Thu, 11 Sep 2003 17:27:47



Hi,

I have just written a stored procedures to query from a
couple of tables and pipe the results to a temp table.  

After which, I want to perform a query from this table and
save the information to a text file.  How can I go about
doing it within the stored procedure?  Please advise.

Regards,
Audrey

 
 
 

Output Query Results to Text File

Post by Jens Sü?meye » Thu, 11 Sep 2003 17:42:11


Hello Audrey !

Go to BOL, check BCP copying data from query to datafile:

bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname"
queryout Authors.txt -c -Sservername -Usa -Ppassword

Execute with SP_CMDshell

HTH, Jens S?meyer



Quote:> Hi,

> I have just written a stored procedures to query from a
> couple of tables and pipe the results to a temp table.

> After which, I want to perform a query from this table and
> save the information to a text file.  How can I go about
> doing it within the stored procedure?  Please advise.

> Regards,
> Audrey


 
 
 

Output Query Results to Text File

Post by Vishal Parka » Thu, 11 Sep 2003 17:36:17


Return the resultset from the stored procedure and use bcp command to send
it to the text file. you can make use of xp_cmdshell to run bcp command
through t-sql

Ex:
Exporting from stored procedure to text file

bcp "exec northwind..<sp>" queryout "c:\test.txt" /c /Usa /P /S<servername>

--
-Vishal


Quote:> Hi,

> I have just written a stored procedures to query from a
> couple of tables and pipe the results to a temp table.

> After which, I want to perform a query from this table and
> save the information to a text file.  How can I go about
> doing it within the stored procedure?  Please advise.

> Regards,
> Audrey

 
 
 

Output Query Results to Text File

Post by Audre » Thu, 11 Sep 2003 17:47:16


Hi,

Is that the only way to achieve what I want?  The reason
is because xp_cmdshell is a controlled stored procedure in
my environment and I can't open up this access to my users.

Is there an alternative?

Cheers, Audrey

>-----Original Message-----
>Hello Audrey !

>Go to BOL, check BCP copying data from query to datafile:

>bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER
BY au_lname"
>queryout Authors.txt -c -Sservername -Usa -Ppassword

>Execute with SP_CMDshell

>HTH, Jens S?meyer



>> Hi,

>> I have just written a stored procedures to query from a
>> couple of tables and pipe the results to a temp table.

>> After which, I want to perform a query from this table
and
>> save the information to a text file.  How can I go about
>> doing it within the stored procedure?  Please advise.

>> Regards,
>> Audrey

>.

 
 
 

1. Output Query Result to Text File with BCP

Hi,

When I need to output my result produced from a stored
procedure to a text file, is BCP the only way I can use?

Also, if I use BCP to do it, I need to use exec statement
like this one followed:

exec Procedure_Name 'MyDB', 'MyTable' ,

For security concern, I don't want to pass the id and
password.  Is there any way that I can avoid passing these
two parameters?

Thanks.  

2. stumped on sql query

3. Need to output query results to a delimited text file

4. exact book title

5. output query results to a text file

6. joining a table to two others

7. Output query result to a text file?

8. Unify 2000 and Outer Joins

9. Outputting results to a text file

10. How to output results onto a TEXT file?

11. Outputting results to a text file

12. Output result into text file

13. Oracle8i stored procedures - output result to text file