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