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!
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.
My guess is ODBC is not recognizing the varchar of this size. Try using SQL server native driver or OLEDB instead of ODBC.
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.
With ODBC driver 17.2 the things are better in some aspects but worse in others:
I will keep investigating.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: