Just a follow-up from this question:

What's the reason that UNLOAD from a proxy table is not supported?

12.0.1.3324 on Windows 32-bit returns SQLCODE -134 (SQLE_NOT_IMPLEMENTED - "Feature not supported") when trying to unload from a MS SQL Server proxy table.


I'm aware that unloading from a remote database will be far less efficient as unloading from a local database. However, OUTPUT from a proxy table does work, and in some cases UNLOAD is not preferable over OUTPUT because of its performance but simply because of its nature as a real SQL Statement (in contrast to an ISQL statement) that can be used within code blocks and procedures.

In the current case, I've found a workaround - but I would like to know whether there are particular reasons for the UNLOAD limitation.

asked 20 May '11, 08:44

Volker%20Barth's gravatar image

Volker Barth
39.8k358546815
accept rate: 34%

edited 20 May '11, 08:45

Or am I to select the proxy table's contents to a local temporary table (say by SELECT INTO LOCAL TEMPORARY TABLE) - and then UNLOAD from that?

I have not yet tested that, but it would be an acceptable solution...

(21 May '11, 17:35) Volker Barth

Volker,

The initial implementation of unload table (many, many, years ago) performed a low level table scan of that table and did not use any intermediate cursor. As a result, unloading a proxy table was impossible since the "low level table scan" would somehow need to be performed on the remote server. However, more recent versions of SQL Anywhere do use a cursor when performing UNLOAD...SELECT, and so it may be possible to at the very least to perform an UNLOAD...SELECT for proxy tables. I will therefore open an enhancement request to revisit the "no unload for proxy table" restriction and see if we can provide at least UNLOAD...SELECT support for proxy tables. In the meantime your workaround of selecting the contents of proxy tables into a temporary table and then unloading the temp table seems to be reasonable.

Karim

permanent link

answered 24 May '11, 09:40

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

Thanks for getting into this!

For the workaround, another nice-to-have would be the ability to do an SELECT INTO LOCAL TEMPORARY TABLE and define the PK automatically, particularly when the unload should be ordered by PK.

Currently, it's not possible to ALTER an automatically created temporary table, cf. this question. Creating an index is possible (though somewhat cumbersome).

I'd suggest a clause like "SELECT * INTO LOCAL TEMPORARY TABLE MyTempTable (WITH PRIMARY KEY) FROM" that would auto-create a PK for MyTempTable if the result set has a unique row (and fail if none or more than one are available). AFAIK sa_describe_query allows for such tests...

(24 May '11, 12:23) Volker Barth
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:

×438
×68
×48
×28

question asked: 20 May '11, 08:44

question was seen: 3,337 times

last updated: 24 May '11, 12:23