I guess the issue is solved:
There was a difference between the proxy table's column definition (particularly the width of NVARCHAR columns) and the remote table's definition, say the proxy has NVARCHAR(30) whereas the remote table has NVARCHAR(50) and contains values with char_length() > 30. I can't tell but strongly assume that the length in the remote table has been enhanced after it was created as proxy table. (It's a third party database, so difficult to find out.)
The too short column description in the proxy table has made sa_describe_query() report NVARCHAR(30), so both local temporary table and permanent table would reasonably be created with NVARCHAR(30), and the INSERT of values with char_length() > 30 chars has apparently triggered that assertion. (I still do not understand why inserting into the local temporary tables has not triggered that assertion, and I generally would have expected an insert error as option string_rtruncation is set to ON here - which would be way better than an assertion...)
After dropping and recreating the proxy table, the problem is solved.
FWIW: As stated in the question, with v16 this assertion has never been raised, and values > 30 chars have been happily accepted by the freshly created permanent table and have successfully been merged into other permanent tables, so I do assume the changes in V17's ODBC/NCHAR treatment do make the difference here.
I have not provided a sample but do assume that it is quite easy to reproduce this with a remote table with an "inappropriate" proxy table definition with too short colum width definitions.
UPDATE: Here's a reproducible.
- Step 1: Use a local SQL Anywhere 16 demo database to create a small table with a NCHAR column:
create table DBA.T_Remote (
ID int default autoincrement primary key,
Value nvarchar(30) not null
);
-- add 2 rows with maximum length
insert DBA.T_Remote (Value) values (N'This is a string with 30 chars');
insert DBA.T_Remote (Value) values (N'This is a string with ä umlaut');
select *, char_length(value) from DBA.T_Remote order by ID;
- Step 2: Use a SQL 17 database (17.0.11.7173) to establish a proxy table to the SA 16 demo database:
create server SVR_SA16 class 'SAODBC'
using 'SERVER=demo16;DSN=SQL Anywhere 16 Demo;UID=DBA;PWD=sql';
create existing table DBA.Proxy_Remote at 'SVR_SA16..DBA.T_Remote';
select * from DBA.Proxy_Remote order by ID;
- Step 3: Now enhance the NCHAR width of the original table and add according longer contents:
alter table DBA.T_Remote alter value nvarchar(50);
insert DBA.T_Remote (Value) values (N'This is a string with 50 chars, after ALTER TABLE!');
- Step 4: Now leave the proxy definition unchanged and use SELECT INTO on the SQL 17 database.
select * from DBA.Proxy_Remote order by ID; -- list all rows with full contents
select * into table DBA.CopyTable from DBA.Proxy_Remote order by ID;
-- raises assertion and crashes the database
- Step 5: In contrast, a local temporary table can be used with SELECT INTO but surprisingly it is described with NVARCHAR(30) although it happily contains values with 50 chars:
select * into local temporary table LtCopyTable from DBA.Proxy_Remote order by ID;
select *, char_length(value) from LtCopyTable order by ID;
call sa_describe_query('Select * from LtCopyTable'); -- lists nvarchar(30) for 2nd column.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.