Results 1 to 10 of 10

Thread: osql formatting

  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Unhappy Unanswered: 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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use the -s to set a column separator. The columns come out fixed width unless you give a separator.

    -PatP

  3. #3
    Join Date
    Nov 2003
    Posts
    7

    Question

    Quote Originally Posted by Pat Phelan
    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...
    Last edited by misiek; 05-07-04 at 11:43.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    Nov 2003
    Posts
    7
    Quote Originally Posted by Pat Phelan
    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...

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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 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

  7. #7
    Join Date
    Nov 2003
    Posts
    7
    Quote Originally Posted by Pat Phelan
    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 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...

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Nov 2003
    Posts
    7

    Lightbulb

    Quote Originally Posted by rdjabarov
    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.

  10. #10
    Join Date
    Nov 2003
    Posts
    7
    Quote Originally Posted by rdjabarov
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •