DATA Step, Macro, Functions and more

Extract SQL Server data with NVARCHAR(max) data type

Reply
Super Contributor
Posts: 326

Extract SQL Server data with NVARCHAR(max) data type

 

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!

Frequent Contributor
Posts: 149

Re: Extract SQL Server data with NVARCHAR(max) data type

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.
Super User
Posts: 3,260

Re: Extract SQL Server data with NVARCHAR(max) data type

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.

Super Contributor
Posts: 326

Re: Extract SQL Server data with NVARCHAR(max) data type

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?

Super User
Posts: 3,260

Re: Extract SQL Server data with NVARCHAR(max) data type

Please post the log with the errors.

Super Contributor
Posts: 326

Re: Extract SQL Server data with NVARCHAR(max) data type

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.
Frequent Contributor
Posts: 149

Re: Extract SQL Server data with NVARCHAR(max) data type

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.

Ask a Question
Discussion stats
  • 6 replies
  • 245 views
  • 1 like
  • 3 in conversation