> 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