Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

I know how to use the output feature of interactive SQL to export the results of a query to an excel formated file - how could I do the same thing within a stored procedure?

asked 19 May '23, 23:28

Glenn%20Barber's gravatar image

Glenn Barber
1.1k274456
accept rate: 8%

edited 19 May '23, 23:28

whould this help?

create or replace procedure myprocedure (mytext long varchar, mypath long varchar)
begin
unload select mytext from dummy to mypath;
end
-------------
call myprocedure  ('blabla', 'c:\temp\myfile.txt')
(25 May '23, 11:07) Baron

This is an old discussion on that topic, and there I finally chose a different approach to use Excel VBA to select from SQL Anywhere instead of exporting to Excel files. I'm not sure whether v17 offers better opportunities.

If CSV is acceptable, that is easily doable via UNLOAD statements in stored procedures...

You might also use DBISQL in a cmd line, see here, by using xp_cmdshell within a stored procedure.

permanent link

answered 21 May '23, 15:33

Volker%20Barth's gravatar image

Volker Barth
40.3k363552824
accept rate: 34%

edited 22 May '23, 03:17

Thanks Volker - while csv's are great for us developers, they create difficulties for users - and probably the most valuable utility would be a way to to the excel export similarly to how it could be done in interactive SQL. Apparently such a utility exists for Microsoft SQLServer.

We are quite familiar VBA - but also not all account have a Excel license on the server and as you might know VBA syntax changes from release to release....

That said - I will look into your thread re using DBISQL.

(22 May '23, 16:35) Glenn Barber
Replies hidden

Yes, I surely agree that user prefer "real Excel files" over CSVs.

Apparently such a utility exists for Microsoft SQLServer.

If you are refering to the MS SQL Server Export/Import Wizard (from the SQL Server Data Tools), I'm not sure that tool is anyhow more user-frinedly... and I'm saying that as a developer...

(23 May '23, 04:53) Volker Barth

I use a VBA Add-In.

I use a generic routine to call a standard fixed parameter stored procedure. The actual SP called is driven by a report table.

I use QueryTables to display the data and even embed XL column and header formatting into the column names. You can get pretty funky results.

permanent link

answered 24 May '23, 15:56

TimCH's gravatar image

TimCH
563310
accept rate: 0%

edited 24 May '23, 15:57

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:

×28
×18
×14

question asked: 19 May '23, 23:28

question was seen: 1,009 times

last updated: 25 May '23, 11:07