Output File

The Output File result set builds a file 1 field at a time and, when building the file, the results sets for all the fields must be issued all at once as a single record set. This is best accomplished by building the result sets in a temporary table and then returning all the records at once.

Order Field Name Values/Reference
0 Result Set Type 50
1 File Number
2 Field Number
3 File Name
4 Field Name
5 Type 'Alpha'
6 Length
7 Precision
8 Value
9 Carriage Return
  • 0 = No carriage return
  • 1 = Carriage return after the field is written
10 Construction Path
11 Final Path
12 Move Mask
13 Add Timestamp
  • 0 = No
  • 1 = Short
  • 2 = Full

The File Number is relevant only when build multiple files in a single block of records. A default value of 1 will be sufficient when building a single file, otherwise increment it as necessary.

The Field Number must increment sequentially throughout the creation of the entire file regardless of the field’s column/row position. The overall formatting of the file is determined by the sequence of the fields and the placement of the Carriage Returns.

The Field Name can be set to any value.

The Construction Path can’t be the same as the Final Path. If they are the file will not be created. The Move Mask must include the File Name or the file will not end up in the Final Path. The Move

Mask will move all files that match it, regardless of where they came from (for example, if Move Mask = ‘*.log’, then all files that match *.log in the Construction Path will be moved to the Final Path). A simple way to limit this is to make the Move Mask the same as the File Name.

Add Timestamp option will add a timestamp to the end of the file name extension. If the option is set to 1, a File Name of ‘Output.dat’ will become ‘Output.dat120102.

Example


DECLARE @FileOutput TABLE (
ResultSetType	int DEFAULT 50,
FileNumber	   int DEFAULT 1,
FileName	     varchar(255) NULL,
FieldNumber	  int IDENTITY,
FieldName	    varchar(20) DEFAULT '0',
FieldType	    varchar(20) DEFAULT 'Alpha',
FieldLength	  int NULL,
FieldPrecision     int DEFAULT 1,
FieldValue	   varchar(255) NULL,
FieldCR	      int DEFAULT 0,
FieldBuildPath     varchar(50) NULL,
FieldFinalPath     varchar(50) NULL,
FieldMoveMask      varchar(50) NULL,
AddTimestamp	 int DEFAULT 0
)

INSERT INTO @FileOutput (	FileName,
                                FieldLength, 
                                FieldValue, 
                                FieldCR, 
                                FieldBuildPath, 
                                FieldFinalPath, 
                                FieldMoveMask)
VALUES (	‘Output.dat’, 
               255,
              ‘MyDataFieldValue’, 
               1,
              ‘C:\Temp\’, 
              ‘C:\Output Directory\’, 
              ‘Output.dat’)
-- Output results SELECT *
FROM @FileOutput
ORDER BY FieldNumber ASC