Exporting columns in a text file with a precise layout.

[connectic]

I have a SQL Server 7.0 database that communicates with an application running on an AIX 4.3 platform through an automated batch gateway. The data must be sent to the Unix program in text format with a specific header size.

I need to know how can I generate a text file containing a field of 1096 characters?

Columns extracted from different tables of the Sql-server database must form the text file.

The position of columns in the text file is a very important parameter.

Anyway back to my problem, I would like to write a stored procedure (or a DTS package) allowing me to generate a text file with specific layout according the columns.

By example :

TableA
  colA1 : datatype1, length (4)
  colA2 : datatype1, length (2)
  colA3 : datatype2, length (2) 
  ...  : ...
  colAn : datatypeN, length (n) 

TableB
  colB1 : datatypeW, length (1)
  colB2 : datatypeX, length (2)
  colB3 : datatypeY, length (10)
  colB4 : datatypeZ, length (1) 
  ...   : ...
  colBn : datatypeN, length (n)

generate Myfile.txt

XXXXXXXXXXXXXXXXX.......X.....................Z(Z=1096 char)
A1A1A1A1B2A3A3B1Bn......Bn....................B4
A1A1A1A1B2A3A3B1Bn......Bn....................B4
A1A1A1A1B2A3A3B1Bn......Bn....................B4
..................
A1A1A1A1B2A3A3B1Bn......Bn....................B4

Etc.

 

[tlbroadbent]

You can write a select statement that will concatenate the columns selected and output the result as one column. Use CAST or CONVERT to fix the size of each column in the result set.

Select
Cast(a.colA1 As Char(7)) +
Cast(a.colA2 As Char(3)) +
Cast(a.colA3 As Char(11)) +
.
.
.
Cast(b.colB1 As Char(9)) +
Cast(b.colB2 As Char(6)) +
Cast(b.colB3 As Char(6)) +
.
.
.
FROM TableA Inner Join TableB
ON a.keycol=b.keycol Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[KRISF]

Use the CHAR data type to enforce the specific sizes and use BCP to export it. Very quick and no concatenation required.