2

Was trying to datestamp the output filename - but keep getting errors -- along the lines of:

Select * from Orders
output to 'c:'+ select (CONVERT(varchar(10), GETDATE(), 120)) + 'orders.csv'

Any help appreciated...

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
Gator
  • 21
  • 2
  • Syntax Error: '+' was not expected – Gator Jun 22 '11 at 12:58
  • IAnywhere - 11 --- Wanted output filename like 2011-06-22orders.csv – Gator Jun 22 '11 at 13:00
  • Thanks for the pointer - sorry for the delay responding UNLOAD SELECT * FROM Orders INTO CLIENT FILE 'c:mydir\todaysdate.csv' this works fine - but you need WRITECLIENTFILE authority - to transfer the data to the client...need to alter some permissions –  Jul 12 '11 at 12:26

2 Answers2

3

I had similar problem on Sybase 9 - I had to write each procedure / function body inside spearate file named as that procedure. So I had to dynamically create file name using name of each procedure (or function). Below is my solution (works in isql):

begin
    declare folder varchar (40);
    declare fileName varchar(60);
    -- folder parameter is our destination path - the 4 backslashes are important here
    set folder = 'c:\\\\test\\\\sql\\\\';
    -- here we are iterating over all procedures / functions
    for p as curs dynamic scroll cursor for 
        Select distinct sysobjects.name
            from sysobjects inner join syscomments
            on sysobjects.id = syscomments.id where sysobjects.type = 'P'
    do
        -- each procedure must be inside separate file named like that procedure
        set fileName =  folder + name + '.sql';
        -- finally, there are answer to original question:
        -- we are exporting data into file, whose name is defined dynamically
        unload select proc_defn from SYS.SYSPROCEDURE where proc_name = name 
            to fileName escapes off;
    end for;
end
Kuba Samczuk
  • 111
  • 6
  • 1
    It's awesome that you included code in your answer, but would you mind sharing more details on *what this code is doing* and *why* this solved the problem? – brokethebuildagain Nov 14 '17 at 18:41
  • Thanks for your reply. I just updated my answer by giving some brief description of problem and placing some comments describing each step – Kuba Samczuk Dec 11 '17 at 21:04
2

output to is a dbisql command, so it's interpreted on the client. This means that you can't use expressions for the filename, since they are executed on the server. However you can use the unload select statement (which does run on the server) with the into client file clause to do what you want.

See docs on the unload select statement here.

Disclaimer: I work for Sybase in SQL Anywhere engineering.

Graeme Perrow
  • 56,086
  • 21
  • 82
  • 121