Problem using: Quick export - Excel Instance - Excel Instance

Toad for IBM DB2
1

Hello,
I am using Toad for DB2 v4.7.2.642 and experiencing a sporadic error when trying to export a result set.
brief descr:
I run an SQL query and receive a result set that I wish to export; I highlight all the cells in the result window and “right click”. I then follow the navigation: Quick Export >> Excel Instance >> Excel Instance…

This used to work as expected for all SQL queries I currently run: a MicroSoft Excel sheet would open and the result set appeared on the Excel sheet.

Now this functionality does not seem to work for particular queries: a Microsoft Excel sheet opens, the column headings are populated; and then … nothing else populates. When I navigate back to TOAD window; I see the following error: Specified cast is not valid.

I cannot seem to find any connection between the exports that used to work as expected and the exports that now do not work; other than I have recently upgraded from the TOAD “freeware” version 4.6.xxx to the commercial version 4.7.2.642. i.e. I purchased the full licensed version.

Has anyone else experienced similar issues when using the Quick Export functionality ?

I can get around the above problem by simply following the same steps and instead of choosing: Quick Export >> Excel Instance >> Excel Instance… ; I simply follow the Quick Export >> File>> Excel File…

So it seems that TOAD can export the results into an excel file but cannot export them to an excel instance …again…this only fails on certain SQLs.

Thanks for any suggestions that are out there.
I have attached examples of an SQL that has no issues with either method and one that has now started failing when exporting to an Excel instance but is successful when exporting to an Excel file.

exports no issues.txt (431 Bytes)

fails export excel instance TOAD log.txt (969 Bytes)

fails export excel instance.txt (3.54 KB)

2

I know some changes were made in that code area for our more recent releases.
Can you please send me the DDL of the objects used in your scenarios that fail?
You can send those directly to me at:
jpodlasek@quest.com if you wish.
thanks
Jeff

3

Hi,

I’ve been having a similar problem. The difference is that I was trying to export the data into an XML file. Same version of Toad.

What helped me was one thing: explicit type cast on timestamp columns.
I’m not aware if the problem also occurs with other data types (e.g. numbers, long), but the same solution wold probably work in those cases as well.

Instead of simply selecting the timestamp columns I used to_char(timestamp_col, ‘YYYY.MM.DD’) and it was solved.

Also, you could probably experiment with different format masks in the wizard itself. I know it offers the option to change the mask for XML output, but I’m not sure if it does for excel instances as well. However, the explicit to_char() cast should work in any case.

(Sorry, but I couldn’t check your attachments because our network won’t allow me, so I don’t know if I’m suggesting something you’ve already tried.)

4

This issue of not being able to export timestamp values via Quick Export has been fixed and will be available in our next 5.0.2 patch.
That patch is tentatively scheduled to be released by mid January 2012.