CASLIB Connection to SQL Server Fails to Load Rows with VARCHAR(MAX) Column

[cristianstoe]

Hey!
Viya 3.5
I'm facing an issue when trying to load a table from Microsoft SQL Server into CAS using a CASLIB data connector. My SQL Server table contains a column with the VARCHAR(MAX) data type, which stores very large text fields (up to 3 million characters).

When I try to load the table into CAS, the process completes without errors, but the resulting CAS table has zero rows. If I use the connection wizard in SAS Studio, it also fails to show any sample data.

Here is the code I am using:

caslib sqlDB desc='Microsoft SQL Server Caslib' 
    dataSource=(srctype='sqlserver',
                username='user1',
                password='myPwd',
                sqlserver_dsn="configured-DSN-name",
                catalog='*');

proc casutil;
  load casdata="myTable" incaslib="sqlDB" 
       casout="myCASTable" outcaslib="casuser";
run;

As suggested inRead varchar(max) from SQL Server into SAS.
As a test, I created a new table in the SQL Server database by selecting all data from the original table, but casting the VARCHAR(MAX) column to VARCHAR(8000).

Why does the load fail with a VARCHAR(MAX) column but succeed with VARCHAR(8000)? Is there a specific configuration or option required to make the SAS/ACCESS to SQL Server connector handle VARCHAR(MAX) data types correctly when loading to CAS?
The encoding has anything to do with it?

Any help or insight would be greatly appreciated.

[yabwon]

I suspect that it's because this data type conversion (from MSsql to CAS) is not supported. If you take a look into this doc. page:

https://documentation.sas.com/doc/en/pgmsascdc/v_063/casref/n09w43elndibwun1q5iy7wae5yy5.htm

the "Supported Microsoft SQL Server Data Types" table doesn't list varchar(max).

But, as I wrote, that's just my best guess from what I can see in the doc.

Bart

[thesasuser]

The  MS SQL varchar(max) supports upto 2 GB of data whereas the Viya varchar(*) can support upto 536,870,911 characters. That could be one reason for the issue.