Microsoft KB Archive/261225

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

Article ID: 261225

Article Last Modified on 8/8/2007

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q261225



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.

  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(&quot;Scripting.FileSystemObject&quot;) Set objArgs = wscript.Arguments InFileNameStr = objArgs(0) OutFileNameStr = objArgs(1)

'===================================================================== '=== Read Script in    wscript.echo &quot;Reading SQL Script from: &quot; & InfileNameStr set infile = fs.OpenTextFile(InFileNameStr) sqlstr = infile.ReadAll infile.close '===================================================================== '=== open connection to server and execute sql wscript.echo &quot;Executing SQL Script... &quot; set conn = wscript.createobject(&quot;ADODB.Connection&quot;) conn.open &quot;LocalServer&quot;, &quot;sa&quot; conn.execute sqlstr '===================================================================== '=== retrieve values to write out set rs = conn.execute(&quot;SELECT * FROM #t_FlatFile&quot;) '===================================================================== '=== Write data to flat file 'wscript.echo &quot;Writing data to: &quot; & OutFileNameStr 'set outfile = fs.CreateTextFile(OutFileNameStr, True) 'outfile.Write rs(0).value 'outfile.close wscript.echo &quot;Writing data to: &quot; & 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 &quot;Done&quot; 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://msdn2.microsoft.com/en-us/library/ms950396.aspx



<div class="moreinformation_section">

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.

Keywords: kbcodesnippet kbprb KB261225

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.