Output Query Result to Text File with BCP

Output Query Result to Text File with BCP

Post by CC » Fri, 16 Aug 2002 16:38:05



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.  

 
 
 

Output Query Result to Text File with BCP

Post by Erland Sommarsko » Sat, 17 Aug 2002 07:06:40



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

It's a little unclear to me what you are actually doing. If you pass
username and password to the stored procedure, I presume that the
stored procedure calls xp_cmdshell which in its turn starts BCP
to bulk out the data?

Since is then running as the same user under which SQL Server is running,
you should be able to specify -T to BCP to get a trusted connection
rather than -U and -P.

But maybe you could clarify a little more what you are doing and what
you are trying to achieve. You could called the stored procedure from
the Query Analyzer, and under Tools options you cold chose to direct
output to a file.

You could also call the stored procedure from the BCP, using the queryout
option (although this one is not entirely without problems).

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

1. output query results to a text file

Hello all,
I am trying to write a sproc that I can schedule as a Scheduled Task in SQL
Server 6.5.   How can I save the output to a text file?

I know how to email the results of a query but can't for the life of me
figure out how to write out to a file.  I'm sure I am missing something very
basic and am hoping someone can point me in the right direction.

Many thanks,
Deb Joyce

2. SS6.5: last date/time of inserts?

3. Output query result to a text file?

4. why no fast refresh on materialized view

5. Output Query Results to Text File

6. How to go back to a field from a session trigger

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

8. Curly brackets

9. Extracting a file from a query text file through BCP

10. How to output results onto a TEXT file?

11. Output result into text file

12. Outputting results to a text file

13. bcp command runs but results do not show up in text file