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.

asked 22 Feb '12, 07:44

cspsp's gravatar image

cspsp
21115
accept rate: 0%

edited 22 Feb '12, 11:31

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819


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

answered 22 Feb '12, 08:15

Chris%20Keating's gravatar image

Chris Keating
7.8k49128
accept rate: 32%

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.

(22 Feb '12, 09:39) cspsp
Replies hidden

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;
(22 Feb '12, 09:41) Volker Barth
1

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>

(22 Feb '12, 09:56) Chris Keating

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

permanent link

answered 22 Feb '12, 10:54

cspsp's gravatar image

cspsp
21115
accept rate: 0%

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

(22 Feb '12, 11:03) Chris Keating

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

(22 Feb '12, 11:14) cspsp
Replies hidden

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.

(22 Feb '12, 11:29) Chris Keating

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.

(22 Feb '12, 12:00) cspsp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

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

×53
×46
×28
×14

question asked: 22 Feb '12, 07:44

question was seen: 11,129 times

last updated: 22 Feb '12, 12:30