The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

I'm running SQLA

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


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;


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


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 
  TO @output_filename FORMAT ASCII;

--OUTPUT to @output_filename format ascii;


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:


DECLARE @output_filename char (100);
  SET @output_filename = STRING (
     '.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 \\\\.

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

--OUTPUT to @output_filename format ascii;

permanent link

answered 26 Feb '10, 21:14

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%

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

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

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


permanent link

answered 28 Feb '10, 12:13

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
accept rate: 16%


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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 26 Feb '10, 20:35

question was seen: 4,871 times

last updated: 28 Feb '10, 12:13