Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Red face Unanswered: 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.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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

    Code:
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    BTW, I would use the SQL with bcp with queryout...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Dec 2003
    Posts
    2

    Red face

    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.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by Brett Kaiser
    BTW, I would use the SQL with bcp with queryout...
    and a format file along with it
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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