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

Article translations Article translations
Article ID: 261225 - View products that this article applies to.
This article was previously published under Q261225
Expand all | Collapse all

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 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
    					
  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:
    http://msdn2.microsoft.com/en-us/library/ms950396.aspx

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.

Properties

Article ID: 261225 - Last Review: August 8, 2007 - Revision: 3.3
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbcodesnippet kbprb KB261225

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com