topic Re: Creating sql table with varchar(Max) using ODBC in SAS Programming

Creating sql table with varchar(Max) using ODBC

js5 — Mon, 22 Oct 2018 13:30:55 GMT

Dear community,

I am having issues creating character variables longer than 8000 characters in an SQL odbc library. We need to transfer several sas datasets into an SQL database and I have written the following macro to accomplish the task.

%macro transfer_to_sql;

	libname aaa odbc prompt="Driver={ODBC Driver 13 for SQL Server};
		Server=xxxx;
		Database=xxxx;
		Trusted_Connection=yes;"
		bulkload=yes;

		proc sql noprint;
			%do i = 1 %to &last_dataset;
				drop table aaa.&&dataset&i;
				select cats(name, %str("='varchar(max)'")) 
					into :varcharmax separated by " "
						from dictionary.columns where libname="bbb"
							and memname=upcase("&&dataset&i")
							and type="char"
							and length GT 8000;

				%if &sqlobs GT 0 %then
					%do;
						create table aaa.&&dataset&i(dbtype=(&varcharmax)) as 
							select * from bbb.&&dataset&i;
					%end;
				%else
					%do;
						create table aaa.&&dataset&i as 
							select * from bbb.&&dataset&i;
					%end;
			%end;
		quit;

	libname aaa clear;

%mend transfer_to_sql;

It works up to a point where a variable longer than 8000 characters is encountered, then the following error happens:

ERROR: Error binding parameters: [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype

Disabling the bulk loader does not help much, the error message is different though:

ERROR: Error binding parameters: [Microsoft][ODBC Driver 13 for SQL Server]Invalid precision value

Has anyone seen this before? Is there a solution available? Thank you for your support in advance!

Re: Creating sql table with varchar(Max) using ODBC

Kurt_Bremser — Mon, 22 Oct 2018 13:53:12 GMT

A google search for "odbc varchar max length" lets me think you may have hit on a problem with the ODBC driver for MS SQL.

Re: Creating sql table with varchar(Max) using ODBC

js5 — Mon, 22 Oct 2018 14:01:40 GMT

It certainly is looking this way. In the meantime I have tried the old "SQL Server" driver (one shipped with Windows 7) and it appears to have succeeded with bulk loader disabled - albeit it took a long time. With bulk loader enabled it fails in the same way the version 13 driver did.
I requested to have version 17.2 installed, let's see if that helps.

Re: Creating sql table with varchar(Max) using ODBC

SuryaKiran — Mon, 22 Oct 2018 14:18:21 GMT

My guess is ODBC is not recognizing the varchar of this size. Try using SQL server native driver or OLEDB instead of ODBC.

Re: Creating sql table with varchar(Max) using ODBC

js5 — Mon, 22 Oct 2018 14:20:55 GMT

We unfortunately only have ODBC licensed, no OLE DB or Microsoft SQL...

Re: Creating sql table with varchar(Max) using ODBC

js5 — Mon, 22 Oct 2018 14:25:32 GMT

I was able to update the macro to use the venerable driver from 2010 and enable bulkloading only if there are no values longer than 8000 characters:

%macro transfer_to_sql;
	
	libname aaa odbc prompt="Driver={SQL Server};
		Server=xxxx;
		Database=xxxx;
		Trusted_Connection=yes;";

		proc sql noprint;
			%do i = 1 %to &last_dataset;
				drop table aaa.&&dataset&i;
				select cats("'", name, "'n='varchar(max)'") 
					into :varcharmax separated by " "
						from dictionary.columns where libname="bbb"
							and memname=upcase("&&dataset&i")
							and type="char"
							and length GT 8000;

				%if &sqlobs GT 0 %then
					%do;
						create table aaa.&&dataset&i(dbtype=(&varcharmax)) as 
							select * from bbb.&&dataset&i;
					%end;
				%else
					%do;
						create table aaa.&&dataset&i(bulkload=yes) as 
							select * from bbb.&&dataset&i;
					%end;
			%end;
		quit;

	libname aaaclear;

%mend transfer_to_sql;

I will test more once I get the latest 17.2 ODBC driver installed and report back.

Re: Creating sql table with varchar(Max) using ODBC

js5 — Wed, 24 Oct 2018 08:45:46 GMT

With ODBC driver 17.2 the things are better in some aspects but worse in others:

with bulkload set to no and no varchar(max) defined I can load everything into the database, character variables longer than 8000 bytes end up loaded as text (this was the same with driver version 13)
with bulkload set to no and varchar(max) set I am still getting the invalid precision value error
with bulkload set to yes sas crashes, no matter whether variables longer than 8000 bytes are involved or not - this is a change from driver version 13 which was throwing the "Invalid field size for datatype" error with long variables and working correctly with short ones.

I will keep investigating.