Creating sql table with varchar(Max) using ODBC

[js5]

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!

[Kurt_Bremser]

 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.

[js5]

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.

[SuryaKiran]

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

[js5]

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

[js5]

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.

[js5]

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.