I'm running SQLA 22.214.171.12450
Using ISQL, I want to dynamically create a file name, then write the file based on a query. Here is my code...
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
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
answered 26 Feb '10, 21:14
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.
answered 28 Feb '10, 12:13