PRB: TSQL PRINT Statement Breaks Lines Longer Than 255 Characters When You Use OSQL to Create a Flat File (261225)

WORKAROUND

NOTE: Both of the following code examples assume that each line is 814 characters long. Here are two methods you can use to work around this problem:

Method 1

Create a temporary table to hold the results of the PRINT result, and then select the result from the temporary table.

Save the following code as Input.txt:

SET NOCOUNT ON
CREATE TABLE #t_FlatFile (x varchar(814))
'add the variable declaration part
INSERT INTO #t_FlatFile values (RTRIM(LTRIM(@Pay_Ent + @InputDate + @GrpNbr + SPACE(20) + @RecordType + SPACE(10) + SPACE(2) + @AreaCode + @GLEffDate + @CGamt + SPACE(15 - LEN(@CGamt)) + SPACE(259) + @OperID + SPACE(473) + @End)))
SELECT * FROM #t_FlatFile

In a command prompt window, enter the following command:osql -E -ic:\input.txt -n -oc:\output.txt -w814 -s -h-1

Method 2

Use a script language to parse the input file and write the output file.

Saving the following code as Osql.vbs:

'=====================================================================
'=== Declare variables
	Dim conn
	Dim rs
	Dim fs
	Dim infile
	Dim outfile
	Dim objArgs
	Dim sqlstr

'=====================================================================
'=== Create FileSystemObject, and read in and out file names.

	set fs = wscript.createobject("Scripting.FileSystemObject")
	Set objArgs = wscript.Arguments
	InFileNameStr = objArgs(0)
	OutFileNameStr = objArgs(1)

'=====================================================================
'=== Read Script in 
	wscript.echo "Reading SQL Script from: " &amp; InfileNameStr
	set infile = fs.OpenTextFile(InFileNameStr)
	sqlstr = infile.ReadAll
	infile.close
'=====================================================================
'=== open connection to server and execute sql
	wscript.echo "Executing SQL Script... "
	set conn = wscript.createobject("ADODB.Connection")
	conn.open "LocalServer", "sa"
	conn.execute sqlstr
'=====================================================================
'=== retrieve values to write out
	set rs = conn.execute("SELECT * FROM #t_FlatFile")
'=====================================================================
'=== Write data to flat file
	'wscript.echo "Writing data to: " &amp; OutFileNameStr
	'set outfile = fs.CreateTextFile(OutFileNameStr, True)
	'outfile.Write rs(0).value
	'outfile.close
        wscript.echo "Writing data to: " &amp; OutFileNameStr
	set outfile = fs.CreateTextFile(OutFileNameStr, True)
	If NOT rs.eof Then
		outfile.Write rs(0).value
	End If
	rs.MoveNext
	while NOT rs.eof
		outfile.write CHR(13) &amp; CHR(10)
		outfile.Write rs(0).value
		rs.MoveNext
	wend
	outfile.close
'=====================================================================
'=== clean up and exit
	wscript.echo chr(13)
	wscript.echo "Done"
	rs.close
	conn.close
	set rs = nothing
	set conn = nothing
'=====================================================================

In a command prompt window, enter the following command:cscript osql.vbs c:\input.txt c:\outfile.txt

You can use Cscript.exe only if you have Microsoft Windows Script Host installed. You can download Windows Script Host from the following Web site:
http://msdn.microsoft.com/scripting/

MORE INFORMATION

The following example shows how the PRINT statement breaks one line (814 characters long) into several lines. Save this code as Input.txt:

DECLARE @Pay_Ent varchar(4)
DECLARE @InputDate varchar(8)
DECLARE @GrpNbr varchar(4)
DECLARE @RecordType varchar(2)
DECLARE @AreaCode varchar(2)
DECLARE @GLEffDate varchar(8)
DECLARE @CGamt varchar(15)
DECLARE @OperID varchar
DECLARE @End varchar(1)
SET @Pay_Ent = '19TV'
SET @InputDate = '20000317'
SET @GrpNbr = '1806'
SET @RecordType = 'CG'
SET @AreaCode = 'AP'
SET @GLEffDate = '20000331'
SET @CGamt = '00000000094112H'
SET @OperID = '016497'
SET @End = 'X'
PRINT @Pay_Ent + @InputDate + @GrpNbr + SPACE(20) + @RecordType + SPACE(10) + SPACE(2) + @AreaCode + @GLEffDate + @CGamt + SPACE(15 - LEN(@CGamt)) + SPACE(259) + @OperID + SPACE(473) + @End

Use Osql.exe to create a flat file, and then issue the following command:

osql -E -ic:\input.txt -n -oc:\output.txt -w814 -s -h-1

The ouput files shows that this line is broken into three lines.

PRB: TSQL PRINT Statement Breaks Lines Longer Than 255 Characters When You Use OSQL to Create a Flat File (261225)

SYMPTOMS

CAUSE

WORKAROUND

MORE INFORMATION