topic CASLIB Connection to SQL Server Fails to Load Rows with VARCHAR(MAX) Column in SAS Viya

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

cristianstoe — Thu, 29 May 2025 11:55:48 GMT

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 in
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.

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

yabwon — Thu, 29 May 2025 13:25:27 GMT

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

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

thesasuser — Fri, 13 Jun 2025 17:46:40 GMT

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.