OSQL- How to know the length of each column in the output file?

Hi,

I would like to use OSQL command to spool the output of a query to a text file. However, the length of the column in the text file is not same as the table column length declared. As I need to process the output file again , I would like to know exactly the start and the end position in the output file for each column. Delimiter doesn't help as the the logic will break if the value of one of the columns is having the delimiter.

My questions are:
1) How does the sql server calculate the length of each column when it spooled to a text file using OSQL?
2) How can I enclose the value with ""? For e.g., "record1"|"record2"|"record3"
3) If I use trial an error to get the length of each column in the output file, will the length of each column changes according to the value each time it runs?

drop table test
create table test
(col1 char(3),
col2 char(5),
col3 char(2)
)

insert into test
values (1, 2, 3)

insert into test
values (100, 20, 3)

osql -S servername -d dbname -E -Q "select * from test" -o "c:\test.log" -h-1 -n -w 8000 -s "|"

Thanks.FIRST!

EXCELLENT POST!

Having sample code makes it sooooooooooooo much easier

Second, I didn't test the bcp, but I did test the execution of the SQL...

Try this

USE Northwind
GO

create table test
(col1 char(3),
col2 char(5),
col3 char(2)
)

insert into test
values (1, 2, 3)

insert into test
values (100, 20, 3)

DECLARE @.cmd varchar(4000) DECLARE @.SQL varchar(4000)

SELECT @.SQL = 'SELECT ''"''+RTRIM(col1)+''"|"''+RTRIM(col2)+''"|"''+RTRIM(col3)+''"'' FROM test'
SELECT @.SQL
EXEC(@.SQL)

SELECT @.cmd = 'osql -S servername -d dbname -E -Q "'+ @.SQL + '" -o "c:\test.log" -h-1 -n -w 8000 -s "|"'
EXEC(@.cmd)
GO
DROP TABLE test
GO

Let us know how it works out...|||BTW, I would use the SQL with bcp with queryout...|||Hi,

First of all, thanks for your reply.

I can't use bcp as i need to call stored procedure (which reside externally in another database and I can't make any code change).

The logic behind is after calling the stored procedure and get the records, I need to spool all records into a text file and process the data -> load it to Oracle database. I can't use middle tier to write to a text file per records due to performance issue. As a result, I need to use OSQL to get the output.

Right now, I am having problem to identify the columns in the text file. Can you help with me quesitons above?

Any idea on that?

Thanks.|||Originally posted by Brett Kaiser
BTW, I would use the SQL with bcp with queryout...

and a format file along with it

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!