osql formatting

At first I'd like to justify myself - I'm rather a greenhorn in MS SQL ;-)
What I'd like to do is to create a very simple spool file from my database.
I'd like to do this using OSQL tool and it's very important for me to have the spool file in the same format as it was earlier when I used Oracle and simple spool command.
And it's almost the same, but white spaces...
There's always one white space at the begining of each line and at least one at the and of each line.
I've been trying many OSQL switches and ltrim(..) / rtrim(..) functions but the problem still exists...
The spool file is rather big - around 300MB so it's not good idea to use SED (for example) to remove white spaces.
So my question:
IS IT POSSIBLE TO REMOVE WHITE SPACES COMPLETELY USING OSQL SPOOL?

Thanks in advance for any help.Use the -s to set a column separator. The columns come out fixed width unless you give a separator.

-PatP|||Use the -s to set a column separator. The columns come out fixed width unless you give a separator.

-PatP

Thanks!
It helps a little. Once I use -s option leading spaces disappeared.
But I still have no idea how to remove white spaces at the end of each line.
It happens when the fields I spool contain numbers with different lenght.
(in expample: 9, 100, 21, etc.)
When I use ltrim/rtrim(..) functions (and str(..) function too) the white spaces are moved from the fields to the end of the line.

I think it may be a problem with the length of each line.
Does each line of a spool file (created by OSQL) has to have the same length? For me it makes no sense...|||I would trim the lines on the client if that was the case. The whitespace trimming is really a presentation issue, and should be done on the client anyway.

-PatP|||I would trim the lines on the client if that was the case. The whitespace trimming is really a presentation issue, and should be done on the client anyway.

-PatP

You are 100% right.
I would do it on the client if only I could do it...
Unfortunately the data format on the client is strictly fixed and I cannot change it.
The second problem is that the file is big and SED (stream editor) is working so so with such a big file.
As a new MS SQL 2k user I have to say that this kind of problem was unknown for me when I started working with Oracle DB. Simple spool could do anything...|||First of all, I'd like some more information on what you are doing that is causing such problems.

Can you post the DDL (the SQL statements that create the tables), and the DML (the SQL statements that return the data) that are causing you these problems? When I build test cases trying to cause the kind of problems you are describing, I can't do it unless I deliberately set things up to cause the problem.

Next order of business, you could use any of several client tools that don't even get "warmed up" very well for 300 Mb of data. Some of these tools, like Perl (http://www.perl.org/) are both free and multi-platform. Others range widely in price, functionality, and platform.

I'm pretty sure that OSQL can do what you want. I just need to see what you are doing in order to understand what problems you are having. Even if OSQL can't do it, there are plenty of other tools that can, but until I understand what the problem is, I can't help you much.

-PatP|||First of all, I'd like some more information on what you are doing that is causing such problems.

Can you post the DDL (the SQL statements that create the tables), and the DML (the SQL statements that return the data) that are causing you these problems? When I build test cases trying to cause the kind of problems you are describing, I can't do it unless I deliberately set things up to cause the problem.

Next order of business, you could use any of several client tools that don't even get "warmed up" very well for 300 Mb of data. Some of these tools, like Perl (http://www.perl.org/) are both free and multi-platform. Others range widely in price, functionality, and platform.

I'm pretty sure that OSQL can do what you want. I just need to see what you are doing in order to understand what problems you are having. Even if OSQL can't do it, there are plenty of other tools that can, but until I understand what the problem is, I can't help you much.

-PatP

I've been doing some tests and I've noticed that OSQL seems to be a "fixed row length" spooler.
But of course I can be wrong.

Now I'm not able to login to the MS SQL 2k db
but I far as I remember the columns look like these:

* msisdn - number(11) -> NULL values are allowed and many NULL values exist
* bserv - varchar2(10)
* id - number(13) -> NULL values are not allowed

And this is the query (looking rather simple...):

query1.sql:

use [SQL_REPLICA]
GO
SET NOCOUNT ON
select
rtrim(case when msisdn is NULL then '' else str(msisdn, 11) end + ',' +
bserv + ',' +
str(id + 1200000000000, 13))
from msisdn_codes
GO

and OSQL command line:

osql -E -h-1 -s "" -n -i query1.sql -o msisdn.dat

Switch -s "" seems to be not obligatory but once I added this swich, leading spaces (at the begining of each line) from the spool file disappered.
To remove white spaces I've been trying to use SED:

sed "s/ //g" msisdn.dat msidn_sr.dat
move msisdn_sr.dat msisdn.dat

or PERL (thanks for your hint!):

use Tie::File;
tie @.array, 'Tie::File', 'msisdn.dat';
for (@.array)
{
s/ //g;
}
untie @.array;

But it takes to much time.
It took 40 minutes to remove white spaces from the file of 100 MB (PERL).
In fact I did the tests on my laptop (only 1,7GHz and 512 MB RAM), but the server is not much faster (Intel Xeon 3GHz, 2 GB RAM).

Thanks for your help and patience...|||If all you need is a text file of a specific most elaborate format, then OSQL is not the utility you need. All you need to do is create your sophistication using a view, and then BCP that view OUT using "-c" switch.|||If all you need is a text file of a specific most elaborate format, then OSQL is not the utility you need. All you need to do is create your sophistication using a view, and then BCP that view OUT using "-c" switch.

You might be right.
The simpliest solution - the best solution.
I'll try to do it the way you suggest.
Thanks for help.|||If all you need is a text file of a specific most elaborate format, then OSQL is not the utility you need. All you need to do is create your sophistication using a view, and then BCP that view OUT using "-c" switch.

It works perfectly!
That is what I need.
Thanks a lot!