Issue with downloading NVARCHAR MAX Data from SQL Server

[meenakshim]

I have a ETL job in SAS 9.4 which pulls data from SQL server DB and writes to a table in different SQL Server DB. I am seeing truncation issues for fields with nvarchar(max) from the source. The destination column datatype is Varchar(max) as well. I tried options like DBMAX_TEXT and casting again the field as nvarchar(max) while writing to the target column but none of these works. This truncation issue is seen in all free form text columns with lengthy data. 

I tried the same download through SSIS and it downloads varchar max data without any truncation issue. Is this issue has any fix in SAS or this is SAS limitation? Then we may need to think of rewriting this ETL in SSIS. How do i download nvarchar max data through SAS and load it back into target SQL table without any truncation issue.

Any help will be appreciated. 

Proc Sql;
Connect To odbc 
       (NOPROMPT="dsn=servername;database=dbname;&_log_host dbmax_text = 14000" );  
insert into dbo.table1
  SELECT * FROM CONNECTION TO ODBC
(SELECT
GNO AS GRV_NBR,
contract_number,
cast(Issue_description as nvarchar(max))
from claim.table2
);
disconnect from odbc;
QUIT;

[Tom]

Server to Server transfers should be done with PROC FEDSQL as it has that capability.  If you use PROC SQL it will have to create a SAS dataset which will limit the character variables to 32K bytes (if that even works with an ODBC driver connection).

Not sure if it will help, but you should try it.

Also if you have VIYA look at this discussion:

https://communities.sas.com/t5/SAS-Programming/Read-varchar-max-from-SQL-Server-into-SAS/td-p/755583

[SASKiwi]

Why do you need to read the data into SAS at all if you are not applying any transformations? Just read the data directly from the source SQL Server database and insert it directly into the target one similar to this:

Proc Sql;
Connect To odbc 
       (NOPROMPT="dsn=servername;database=dbname;&_log_host" );  
execute(
 insert into dbo.table1
 SELECT * FROM [MySQLServer].[MySQLDatabase].[MySQLSchema].[MySQLTable]

) by ODBC;
disconnect from odbc;
QUIT;

[meenakshim]

My source and target tables are in different servers. Will this execute statement with SAS/ACCESS works here , don’t we need to use explicit/implicit pass through ? 

[SASKiwi]

As long as the SQL Servers are accessible from each other, then it should work fine. Talk to your SQL Server DBA to confirm. I've used this type of query myself. You need to use explicit pass through so all processing is done in SQL Server.