I'm using Sybase 15 with ASA 11. I'm trying to output results of my stored procedure to an Excel file and calling this from a batch file to run regularly. Here's my code:
runreport.sql : exec myprocedure ; output to 'd:\reports\myreport.xls' format html ;
runreport.bat : isql -Ureportuser -Preportuser -Smyserver -id:\reports\runreport.sql
If I run the sql file in the Interactive SQL GUI, it outputs the excel file perfectly. But when I run it using the command line isql, it throws me a "Syntax error near keyword 'output' " error message.
I tried using the -o option in isql to output the results, but then I have to set up Excel macros to convert this to xls. I would prefer to avoid this route since I can't change the macros security settings on the server I'll be using for this.
The 'output to' works in the GUI version of ISQL, just not in the command line version. Anyone got any ideas?
Is there a reason you are using the ASE based isql tool versus the SQL Anywhere 11 interactive sql (dbisql or dbisqlc)? I am not familar with isql so I am not sure that OUTPUT TO is a valid statement.
You can add -nogui to the dbisql command line to run in a non-gui mode.
dbisql -nogui -c <conn_str> read runreport.sql
answered 22 Feb '12, 08:15
Chris, could you please try with a temporary table in the stored procedure? If it's just a simple select statement in the stored procedure, output's fine. But with a temporary table, it just outputs the headers, no data.
runreport.sql: create procedure dbo.foo() as begin create table #temptable ( myid varchar(10), lname varchar(10), fname varchar(10) ) insert into #temptable values('1234567890','Smith','John') select * from #temptable end
answered 22 Feb '12, 10:54