import data into one text file

I have sp that I have written that gets info from a table. but in the text
file
I need header and results set. For ex. this sp looks like this
create proc info
as
declare @.count int
set @.count = (select count(*) from info)
select getdate()
select * from info
select 'total count '+convert(varchar(20),@.count)
So, how is it possible to get this results set into one text file?
Thanks in advance.
sonalHi,
Execute the procedure using the command line utility OSQL.
OSQL -Usa -Ppassword -Q"dbname..sp_name" -Oc:\result.txt -n
Thanks
Hari
MCDBA
"sonal" wrote:
> I have sp that I have written that gets info from a table. but in the text
> file
> I need header and results set. For ex. this sp looks like this
> create proc info
> as
> declare @.count int
> set @.count = (select count(*) from info)
> select getdate()
> select * from info
> select 'total count '+convert(varchar(20),@.count)
> So, how is it possible to get this results set into one text file?
> Thanks in advance.
> sonal
>|||Several ways.
I prefer to use bcp for exporting to text files.
You can format the data into a single resultset.
select s = convert(varchar(1000),getdate())
union all
select convert(varchar(20), col1)
+ ',' + convert(varchar(20), col2)
+ ....
from info
union all
select 'total count '+convert(varchar(20),@.count)
In that way you can test the structure without going to the text file (and
also save it to a table if need be).