Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002

    Unanswered: formatted file using stored procedure

    how do i create a formatted file using a stored procedure?

    for eg. if i have the following....

    date : 5th April : should occupy 6 spaces (numeric) (05/04/2003)
    Place: London : should occupy 20 spaces (text).
    Country: United Kingdom should occupy 20 spaces (text)
    EOL Terminator: (1 character only)

    the sample final output should be like:

    050403London United Kingdom
    050403Washington DC USA
    050403Delhi India

    any quick help is appreciated,

  2. #2
    Join Date
    Mar 2002
    earlier post discarded the spaces between the sample records... it should reas as

    050403London^^^^^^^^^^^^^^United Kingdom^^^^^^

    where ^ denotes a blank space.


  3. #3
    Join Date
    Dec 2001
    Toronto, Canada
    In your SELECT statement CONVERT the text fields to fix length CHAR. So if City needs to be 40 characters it is CONVERT(CHAR(40), City). You can run the stored procedure from ISQL using -h-1 so no header get printed

    C:\>isql -E -S -Q"select status,CONVERT(CHAR(35),name)+CONVERT
                   (char(10),crdate,103) from sysobjects where type='S'" -h-1
     -2147483645 sysobjects                         13/11/1998
     -2147483643 sysindexes                         13/11/1998
     -2147483645 syscolumns                         13/11/1998
     -2147483645 systypes                           13/11/1998
     -2147483647 syscomments                        13/11/1998
     -2147483648 sysfiles1                          13/11/1998
     -2147483647 syspermissions                     13/11/1998
    Substitue the SELECT with your stored procedure. Or even better use BCP.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts