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

This article was previously published under Q261225
This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
When you use the TSQL PRINT statement to print a line that is longer than 255 characters, it breaks one line into several lines and each segment line is 255 characters long. Therefore, if you include the PRINT statement in the Input.txt file for the Osql command, (for example, osql -E -ic:\input.txt -n -oc:\output.txt -w2000 -s -h-1) when you create a flat file for the MVS system, uploading the output file fails because each line longer than 255 characters is broken down to several lines and extra line feeds and carriage returns are added in the flat file you created for the MVS system.
CAUSE
The PRINT statement outputs the line with a maximum length of 255 characters. Any line longer than 255 characters is broken into several lines.
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.

  1. Save the following code as Input.txt:
    SET NOCOUNT ONCREATE TABLE #t_FlatFile (x varchar(814))'add the variable declaration partINSERT 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					
  2. 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.

  1. 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: " & 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: " & OutFileNameStr	'set outfile = fs.CreateTextFile(OutFileNameStr, True)	'outfile.Write rs(0).value	'outfile.close        wscript.echo "Writing data to: " & 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) & 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'=====================================================================					
  2. 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:
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 varcharDECLARE @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.
Properties

Article ID: 261225 - Last Review: 01/16/2015 20:23:10 - Revision: 3.3

  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • kbnosurvey kbarchive kbcodesnippet kbprb KB261225
Feedback