topic Re: Extract SQL Server data with NVARCHAR(max) data type in SAS Programming

Extract SQL Server data with NVARCHAR(max) data type

milts — Thu, 28 Sep 2017 02:57:43 GMT

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!

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

error_prone — Thu, 28 Sep 2017 03:37:12 GMT

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.

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

SASKiwi — Thu, 28 Sep 2017 06:14:22 GMT

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.

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

milts — Fri, 29 Sep 2017 01:13:06 GMT

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?

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

SASKiwi — Fri, 29 Sep 2017 03:09:47 GMT

Please post the log with the errors.

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

milts — Fri, 06 Oct 2017 01:09:11 GMT

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.

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

error_prone — Sat, 07 Oct 2017 20:25:42 GMT

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.