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 spools 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?

My script:
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 -i "c:\test.sql" -
o "c:\test.log" -h-1 -n -w 8000 -s "|"

test.sql
--------
exec sp_test

(sp_test is having query - "select * from test")


Output file test.log
---------------------
1 |2 |3
100 |20 |3

The length of each column is different from the datatype
declared.

Thanks.


LC

In article <2eb401c3c4a2$81e4afe0$7d02280a (AT) phx (DOT) gbl>, LC
<anonymous (AT) discussions (DOT) microsoft.com> writes

Why not use BCP?

Any additional formatting options should be possible through a format
file.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Hi,

I can't use bCP as it doesn't support stored procedure
calling. Any other solution?

Hey LC,

You can use dynamic sql to construct command strings for
the BCP command and use xp_cmdshell to run this in a proc..
I am passing two tablenames here..if u want u can hardcode
them in the logic as well...or, conversely add more
parameters for the paths...

The file names are appended with datetime stamp, if u want
u can remove that...

CREATE PROCEDURE dbo.bcp_proc
@tablename1 varchar(60)
,@tablename2 varchar(60)

AS

declare @sql_part_one varchar(200)
declare @filename1 varchar (85)
declare @filename2 varchar (85)

BEGIN
set @filename1=replace
('C:\temp_acs_files\test\'+@tablename1+'_'+convert (varchar
(8),getdate(), 1)+'.DAT', '/' , '')
set @sql_part_one= 'bcp <Databasename>..'+@tablename1+ '
OUT '+@filename1+' -f C:\temp_acs_files\format\temp.fmt -
T '
EXEC master..xp_cmdshell @sql_part_one
END


GO

PS: Make sure u test the workings of this before u run it
in production.