cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

isql with 'output to'

Go to solution
Former Member

on ‎2012 Feb 22 7:44 AM

11,701

Hi.

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?

Thanks.

Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

chris_keating
Product and Topic Expert
Product and Topic Expert
‎2012 Feb 22 8:15 AM

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
Show replies
Show replies
Former Member
‎2012 Feb 22 9:39 AM
0 Kudos

Thanks Chris. I was just not familiar with dbisql, that's all. I've tried it the way you suggested and for some reason it only outputs the headers into the output file but none of the data. As before, works fine from GUI. I've tried both html & ascii format.

If I change the 'exec stored-procedure' to a dummy 'select' statement, then I get data in the output file. However, my report is a bit more complicated than that and is hence in a stored procedure.

VolkerBarth
Contributor
‎2012 Feb 22 9:41 AM
0 Kudos

Try using the CALL statement (that's the SQL Anywhere way to execute a stored-procedure):

call myprocedure();
output to 'd:\\reports\\myreport.xls' format html;
chris_keating
Product and Topic Expert
Product and Topic Expert
‎2012 Feb 22 9:56 AM

What version and build of SA11 are you running?

Here is the test that I ran to verify this:

Using the following example proc;

CREATE PROCEDURE "DBA"."foo"( ) AS BEGIN select * from groupo.departments END

and the runreport.sql as

execute foo; output to 'output.txt' format 'html';

and the command line:

%sqlany11%bin32dbisql -nogui -c "eng=demo11;uid=dba;pwd=sql" read runreport.sql

the file generated is:

<html> <head> <meta content="text/html;charset=Cp1252"> </head> <body>

DepartmentIDDepartmentNameDepartmentHeadID
100R & D501
200Sales902
300Finance1293
400Marketing1576
500Shipping703
</body> </html>

Answers (1)

Answers (1)

Former Member
‎2012 Feb 22 10:54 AM
0 Kudos

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

Show replies
Show replies
chris_keating
Product and Topic Expert
Product and Topic Expert
‎2012 Feb 22 11:03 AM
0 Kudos

I get both headers and data. Can you confirm version and build that you are running of both dbisql and the engine (select @@version will give you the engine verson).

Former Member
‎2012 Feb 22 11:14 AM
0 Kudos

Adaptive Server Enterprise/15.0.3/EBF 16738 ESD#2/P/NT (IX86)/Windows 2003/ase1503/2708/32-bit/OPT/Mon Jul 27 20:19:56 2009

Interactive SQL version 11.0.0 build 1649

chris_keating
Product and Topic Expert
Product and Topic Expert
‎2012 Feb 22 11:29 AM
0 Kudos

Your initial post was a little confusing in that you indicated you were using ASA11. Since your backend is ASE and you were using an ASE based isql tool, this may not be the forum you want to raise this question. This forum is for SQL Anywhere product components.

I have tested this with 11.0.1 latest build for both the server and dbisql tool and it is working as expected. Since you reported this with a complete ASE (isql and server) environment initially, it is likely that there is some issue at the ASE level. I would recommend to have a look in the FAQ page - see the last question to find hints for further support on ASE.

Former Member
‎2012 Feb 22 12:00 PM
0 Kudos

Thanks Chris. I'll follow it up in the other forum. My apologies for the mix up with ASE/ ASA. I've only ever used Sybase for data retrieval, haven't needed to look at the administration side yet. I just checked again and I have Sybase ASA ISQL version 8.0.1 build 2600. And running this query using this isql tool has the same problem. Hopefully the ASE people will have an answer to what's going on! Thanks again for all your help.

Ask a Question