I am new to Sybase, hopefully someone can help me with this problem.

SELECT * FROM temp_data order by r_date, r_time;
OUTPUT TO 'C:\files\headerfile.txt' 
    FORMAT TEXT
    DELIMITED BY '\x09'
    QUOTE ''
    ENCODING 'UTF-8'
    WITH COLUMN NAMES;

This code works for me but unfortunately WITH COLUMN NAMES; can not be used in a stored procedure. Then I tried:

unload select location, r_date, r_time, ct_codel_code_title from temp_data 
    TO 'C:\files\headerfile.txt'
    FORMAT TEXT
    DELIMITED BY '\x09'
    QUOTE ''
    ENCODING 'UTF-8'

still the column name don't appear. Can anyone help?

asked 28 Dec '12, 03:10

kach's gravatar image

kach
95246
accept rate: 0%

edited 01 Jan '13, 10:34

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109


The OUTPUT TO statement is interpreted by dbisql -- it is not understood by the server and therefore cannot be used in a stored procedure.

The WITH COLUMN NAMES option is not supported by UNLOAD. An enhancement request has been made for this feature. In the meantime, you might be able to manually writing the column names out with one UNLOAD statement and then writing the data with UNLOAD ... APPEND ON.

permanent link

answered 28 Dec '12, 07:50

John%20Smirnios's gravatar image

John Smirnios
8.9k377110
accept rate: 40%

If you were wanting to "future proof" against additional columns being added in future, you could select the column names from the SYS.SYSCOLUMNS view - something like:

select list(string('"',cname,'"'),char(09) order by colno asc)
  from sys.syscolumns where creator = 'dba' and tname = 'temp_data' ;

Ordering by colno should ensure that your columns are listed in the same order as the result set produced by select *

permanent link

answered 28 Dec '12, 08:42

Justin%20Willey's gravatar image

Justin Willey
6.7k108138208
accept rate: 20%

Comment Text Removed

A possibly even more "future proof" way would be to use sa_describe_query() with the "real query" as argument to get the column list of the result set - that would even work with ad-hoc queries and would guarantee that the column order is correct.

Here's a sample for the original query:

select list(string('"',name,'"'),char(09) order by column_number)
from sa_describe_query('SELECT * FROM temp_data order by r_date, r_time');

Yes, in case the "real query" contains quotes, they will have to be masked, as usually...


Note: sa_describe_query was introduced in v10, so that won't work for older versions - in contrast to Justin's suggestion.

permanent link

answered 28 Dec '12, 09:18

Volker%20Barth's gravatar image

Volker Barth
30.3k301454660
accept rate: 32%

FWIW, this FAQ deals with a similar (though more general) problem, and Mark has created a bunch of samples how to create a function that does export both column headings and data, where details of the the format (delimiters and the like) can be set as parameters...

Post Processing Result Set with Dynamic Query Input

(02 Jan '13, 03:27) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×36

question asked: 28 Dec '12, 03:10

question was seen: 3,016 times

last updated: 02 Jan '13, 03:28