column name inserted in text file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

column name inserted in text file

Author

Topic

petit-pere
Starting Member

12 Posts

Posted - 2006-11-07 : 10:46:35

Hi ...
I have a stored procedure with a cursor on a table that inserts for each record returned a row in a single column table. At the end of the query the results are returned with following statement: select * from @results. The result is correct but when I save the result in a text file, the first row is the name of the column of the table. example:
r_buffer
1,test01,123456.1
2,test01,123456.1
3,test01,123456.1

How can I avoid to have the query returning this column name?

Thanks a lot for your precious help.
Hervé

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 10:53:55

select * from @results
where columnname like '[0-9]%'

Peter Larsson
Helsingborg, Sweden

petit-pere
Starting Member

12 Posts

Posted - 2006-11-07 : 11:02:24

thanks for your answer Peter but it doesn't help as r_buffer is not a row in the table but the name of its column. too bad ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 11:12:35

Oh, you mean that the CURSOR logic in your stored procedure is writing the column name to the file?
Just comment out that part in the SP.

Peter Larsson
Helsingborg, Sweden

petit-pere
Starting Member

12 Posts

Posted - 2006-11-07 : 11:31:40

ok, this is the SP I'm running.
Unneccessary code has been removed, so I agree it doesn't really make sense to write a SP for achieving this result anymore ... ;)

For reminder my problem is when you export the result to a text stream on screen or to a file.

------------------------------------------------
set nocount on

declare @buffer varchar(500),
@policy char(18),
@prd char(20),
@price float,
@units float,
@amount float

declare @result table (
r_buffer varchar(500)
)

declare po_cursor cursor for
select p.pol_id,
p.date_of_commencement,
p.product,
p.units,
p.price
from policies as p
where (p.pol_id like 'CP%' or p.pol_id like 'CL%')

open po_cursor

fetch next from po_cursor into @policy, @issue_date, @prd, @units, @price

while @@fetch_status = 0
begin
select @amount = 0
select @amount = @units * @price * 0.008 / 52

if ( round(@amount, 2) <> 0 )
begin
select @buffer = ''
select @buffer = rtrim(@policy) + ',' rtrim(@prd) + ',' + cast(round(@amount, 2) as varchar)

insert into @result ( r_buffer ) values ( @buffer )

fetch next from po_cursor into @policy, @issue_date, @prd
end

close po_cursor
deallocate po_cursor

select r_buffer from @result

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 11:38:19

Ok, so you manually copy and paste the result to a text file?
DO NOT COPY THE COLUMN HEADER...

Peter Larsson
Helsingborg, Sweden

petit-pere
Starting Member

12 Posts

Posted - 2006-11-07 : 11:43:17

no, no, I save the result to a text file using the SQL-server option "save to file". BTW I'm using SQL-Server 2K ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 11:48:33

Look under Tools -> Options to see wether or not there is an option to set to avoid saving the column names too.
It not, it is not a problem. Just edit the text file manually and drop the first line.

Peter Larsson
Helsingborg, Sweden

petit-pere
Starting Member

12 Posts

Posted - 2006-11-08 : 03:08:16

Dropping the line manually is only a work-around and it doesn't solve my problem if the query runs automatically.

You're right Peter, there is an option that avoids printing the column name, but I need to set it in the SP not for the QA. Do you know the SET statement to use?

Anyway, thanks for your answers ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 03:11:15

It is not the SP that saves the result to a file. You do that manually...
You can use BCP to export data from @Results table to file without headers automatically.

Peter Larsson
Helsingborg, Sweden

petit-pere
Starting Member

12 Posts

Posted - 2006-11-08 : 03:25:49

Stupid me ... I am using BCP in other queries but didn't think using it here.

Thanks for your help Peter and sorry for the inconvenience ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources