BookmarkSubscribeRSS Feed
milts
Pyrite | Level 9

 

I have a script which extract data from a MS SQL table. However I'm getting a UCS-2 transcoding error as I have a column with a nvarchar(max) data type. I have some records with more than 32000

 

Is there a syntax for my proc sql script that I can define the dbmax_text? I know for libname I'll be able to add the dbmax_text.

 

proc sql noprint;
	connect to odbc("Driver=ODBC Driver 11 for SQL Server;Server=SQLSVR;Database=MYDB;uid=user;pwd=pwd;");
	create table extract(compress=no) as
	select * from connection to odbc
	(
		select * from mysch.mytbl
	);
	disconnect from odbc;
quit;

 

Thanks!

6 REPLIES 6
error_prone
Barite | Level 11
As far as I know 32k is the maximum number of chars a variable can store. I am sure, that others had the same problem before, so maybe the search function reveals possibilities to solve the problem.
SASKiwi
PROC Star

DBMAX_TEXT is also a dataset option. Have you tried this?

 

proc sql noprint;
	connect to odbc("Driver=ODBC Driver 11 for SQL Server;Server=SQLSVR;Database=MYDB;uid=user;pwd=pwd;");
	create table extract(compress=no dbmax_text = 32767) as
	select * from connection to odbc
	(
		select * from mysch.mytbl
	);
	disconnect from odbc;
quit;

You will still have a problem with text > 32767 characters though.

milts
Pyrite | Level 9

As a workaround I had to split the column to 16k each on my sql query then concatenate them when creating the sas dataset and assigning a length of 32000.

 

My next problem now is to resolve the same transcoding error when writing the processed data back to sql. I'm using a proc append to a sql table. In my libname I have defined dbmax_text=32000 in my libname statement.

 

proc append base=sqllib.myoutput data=extract_processed force; run;

 

Are there other workarounds for this or I really won't be able to writeback a 32k length field into a nvarchar(max) datatype in sql?

SASKiwi
PROC Star

Please post the log with the errors.

milts
Pyrite | Level 9

Sorry I'm unable to extract the whole log as it's residing on a client machine.

 

But error I get is this one:

 

ERROR: Unable to transcode data to/from UCS-2 encoding.
error_prone
Barite | Level 11

See http://support.sas.com/kb/40/566.html and http://support.sas.com/kb/47/787.html.

Your problem looks like those described in the linked pages: mismatching encodings. I recommend contacting tech support.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 5116 views
  • 1 like
  • 3 in conversation