I'm running SQLA 9.0.2.3850

Using ISQL, I want to dynamically create a file name, then write the file based on a query. Here is my code...

BEGIN

DECLARE @output_filename char (100);
  SET @output_filename = 'C:\TEMP\JUNK_' ||  DATEFORMAT(CURRENT TIMESTAMP, 'YYYYMMDD') || '.txt';

SELECT TOP 10 * FROM sys.systable order by table_id;

OUTPUT to @output_filename format ascii;

END

I get "Syntax error near 'OUTPUT'" when I run this. I can't find anything in the docs that says this should not be allowed. And I have to use the BEGIN/END statements in order to use variables, right??

asked 26 Feb '10, 20:35

Ron%20Hiner's gravatar image

Ron Hiner
880202427
accept rate: 9%


The OUTPUT statement is processed by the client applicaton dbisql. However, anything you code inside a BEGIN block has to be passed to the database engine to be processed, and the engine doesn't know what an OUTPUT statement is.

The good news is, the UNLOAD SELECT statement is even more powerful, flexible and efficient than OUTPUT. FWIW it's all described in my book which is written for Version 9 http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20

BEGIN

DECLARE @output_filename char (100);
  DECLARE @sql LONG VARCHAR;
  SET @output_filename = 'C:\TEMP\JUNK_' ||  DATEFORMAT(CURRENT TIMESTAMP, 'YYYYMMDD') || '.txt';

UNLOAD 
  SELECT TOP 10 * FROM sys.systable order by table_id 
  TO @output_filename FORMAT ASCII;

--OUTPUT to @output_filename format ascii;

END

There is a potential problem: The UNLOAD statement runs in the engine, and the filespec is relative to the computer running the engine, whereas the dbisql OUTPUT filespec is relative to the computer running dbisql. If the computers are one and the same, so far so good. But if the engine is on another computer AND you want the file sent back to your workstation, you will have to jump through hoops; e.g., use UNC filespecs:

BEGIN

DECLARE @output_filename char (100);
  DECLARE @sql LONG VARCHAR;
  SET @output_filename = STRING (
     '\\\\192.168.1.105\\C\\TEMP\\JUNK_',
     DATEFORMAT(CURRENT TIMESTAMP, 'YYYYMMDD'),
     '.txt' );

-- The slashes are all doubled-up because \ is an escape character, 
  -- so \\ represents one \, and in order to get two backslashes \\ 
  -- you have to code four \\\\.

UNLOAD 
  SELECT TOP 10 * FROM sys.systable order by table_id 
  TO @output_filename FORMAT ASCII;

--OUTPUT to @output_filename format ascii;

END
permanent link

answered 26 Feb '10, 21:14

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

Thanks Breck! I was afraid that might be the answer, but I had no idea that the server doesn't know about the OUTPUT statement... Thats interesting that Isql has brains of its own - I always thought it was just a UI to the server, and nothing more.

(26 Feb '10, 21:49) Ron Hiner
2

To add to Breck's answer, in version 11 the UNLOAD statement can write its results to a file residing on the client using the TO CLIENT FILE clause - See http://dcx.sybase.com/index.html#1101en/dbreference_en11/unload-statement.html - hence no more hoops are required :-)

(26 Feb '10, 22:23) Mark Culp

@Mark: ...except that teeny little hoop, "upgrade from 9 to 11" :)

(27 Feb '10, 11:06) Breck Carter

If this is a manually invoked or scheduled iSQL Script you can you command-line parameters to name the output file.

Look at PARAMETERS statement

The filename in your example can easily build on windows with the environment variables. As only the date is in question a not synchronized clock on the client should be not problematic.

PARAMETERS file;
SELECT TOP 10 * FROM sys.systable order by table_id;
OUTPUT to {file} format ascii;

HTH

permanent link

answered 28 Feb '10, 12:13

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.7k293965
accept rate: 17%

1

EXCELLENT SUGGESTION! I use PARAMETERS all the time for customizing giant database builds ( {DEBUG_MESSAGES}, {BETA_VERSUS_EVAL_FULL} etcetera ), and to give "template remote databases" their MobiLink personalities ( {GLOBAL_DB_ID} etcetera )... why didn't it occur to me that it would work with the OUTPUT filespec? Doh! :)

(28 Feb '10, 13:28) Breck Carter
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:

×28

question asked: 26 Feb '10, 20:35

question was seen: 10,901 times

last updated: 28 Feb '10, 12:13