problem using rtrim in select when outputting to file with isql

[NickW2]

When outputting to a file with isql under SQL Server 2000, even if the select statement uses RTRIM on a column, it is still output with trailing spaces.
Is there any way to force the removal of the trailing spaces?

 

[donutman]

What does it mean to have trailing spaces in a text file? Are they actually within a quoted string? If not will adding an ASCII quote character to beginning and end of field within the Select clause give you what you want?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

I believe that nick is talking about the fact that isql will padd the columns to get them to the correct width because isql wants the output to look nice.

Nick, use BCP to export the data. It doesn't have this annoying little problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[donutman]

Ahh. The old is it a bug or feature question.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

yeah pretty much.

The thing to remember about isql is that it's not a data export tool. It's a user interface tool much like query analyzer (Query Alanyzer is isqlw). BCP is the data export tool. isql will format the data to try to make it look easy to read on the screen. But in this format it isn't always easy to process. Hence the use of BCP.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[NickW2]

Thanks. I'll use bcp instead.
I was already aware of bcp but was checking if there was another way of using isql to achieve what I wanted.