DBMAX_Text setting and Warning Message about data truncation - Please help

Reply
Super Contributor
Posts: 418

DBMAX_Text setting and Warning Message about data truncation - Please help

Hello all. I had a question relating to a sas Libname statements for a sql server database, using an odbc connection (dsn).

In our database, we have specific columns that have to be defined as varchar(max) since they are often over 12,000 text length long. (This cannot be broken out, as it is one "answer"). If I want to use a libname statement to connect to this database for a Proc Sql insert into, I have to set the dbmax_text setting to a large number (using 32,000). This way Sas will not truncate any values inserted into this column to a length of 1 (since sas does not recognize the length of varchar(MAX) by default.

I have run this libname statement code on numerous occasions and the step works, however sas always creates the following warning message.

"WARNING: Truncating character column XXX to 32000 characters long, based on dbmax_text setting."

This is a problem because our code is quite large, and loops through numerous tables and does numerous inserts. These warnings are cluttering up the log and making it almost impossible to debug real occuring issues.

Does anyone know an option to turn this specific warning message off? (Or all warning messages with another setting to turn them immediately back on).
If no such setting exists, can anyone suggest a different way to move data between Sas and sql server without a libname statement that does not produce this warning?

Thank you!


PROC Star
Posts: 7,360

Re: DBMAX_Text setting and Warning Message about data truncation - Please help

What version of SAS are you on?  It may have been corrected in 9.2 http://support.sas.com/kb/30/741.html

Super Contributor
Posts: 418

Re: DBMAX_Text setting and Warning Message about data truncation - Please help

Sadly this "fix" in 9.2 did not solve the WARNING issue (at least not on my computer). If anyone has 9.2 and runs code similar to this, does this same warning message occur for them? If it does not, I will probably have to un-install and re-install (really hoping to avoid that).

For a bit more clarification, I am using the Microsoft ODBC Data Source Administrator  "Data Sources (ODBC)" to define a system DSN, which I am then refrencing in my libname statement as following.

Libname Myodbc odbc dsn='MY_DEFINED_SYSTEM_DSN_NAME' schema=dbo bulkload=yes DMBAX_text=32000;

Contributor
Posts: 23

Re: DBMAX_Text setting and Warning Message about data truncation - Please help

Yep, fun and games with long fields. I found it better to use pass-through sql than the libname statement.

I download lots of tables using pass through SQL with the database dbmax_text=32767 option.

I use global macro variables for the database log on information:

&GLB_USER

&GLB_PASSWORD

&GLB_DBname

&GLB_DSN_NAME

&GLB_ServerIPaddr


** macro to pull all the data from the table ;
%macro SQLPullDataBig (outputdataset,SQLtableName,DatasetLabel);

PROC SQL;
CONNECT TO ODBC AS ODBCref
  (NOPROMPT="UID=&GLB_USER;PSW=&GLB_PASSWORD;DSN=&GLB_DSN_NAME;

SERVER=&GLB_ServerIPaddr;DATABASE=&GLB_DBname"  dbmax_text=32767);
Create table &outputdataset(compress=binary label="&DatasetLabel") as
Select * from connection to ODBCref (
Select  *
From &SQLtableName
);
quit;

%mend;

%SQLPullDataBig(libName.outputDatasetName,SQLtableName,Label for you dataset);

Now when the fields are longer than 32,767 characters life get more interesting as the only way seems to be to chop the field up and download from SQL in sections. I have SAS code which does this by working out the maximum length of the field and generating the download code as required. If anyone is interested.

Ask a Question
Discussion stats
  • 3 replies
  • 844 views
  • 0 likes
  • 3 in conversation