How do I use OUTPUT with dynamic filename?

[Former Member]

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??

[Breck_Carter]

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

[Former Member]

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.

[MarkCulp]

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 🙂

[Breck_Carter]

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

[thomas_duemesnil]

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

[Breck_Carter]

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! 🙂