Import from SQL without trucating

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Import from SQL without trucating

I am importing a table from SQL  using an oledb connection, but when I try to make a temporary sas data set, it truncates one of the variables that can be very large (can have over 5000 characters).  It is really important that I have all the characters in that data field. Is there a way to import this data set without truncating?

Here is the code I'm using:

libname testsql oledb

init_string= "Provider=SQLOLEDB.1;

     Persist Security Info= True;

     Persist Security Info= True;

     Initial Catalog = sql_database_name;

     Data Source = xxxx"

Schema=Src;

data name;

     set testsql.name;

runb

And this is the warning I am getting:

WARNING: During read: Data truncated for column Variable1

Any help or advice about this would be very much appreciated!

Thanks!


Accepted Solutions
Solution
‎12-20-2012 11:39 PM
Respected Advisor
Posts: 3,124

Re: Import from SQL without trucating

You may want to add DBMAX_TEXT option either in libname statement or as a data set option. The default is 1024, the max is 32k, so choose some number that can accommodate your largest length:

libname testsql oledb

init_string= "Provider=SQLOLEDB.1;

     Persist Security Info= True;

     Persist Security Info= True;

     Initial Catalog = sql_database_name;

     Data Source = xxxx"

Schema=Src

DBMAX_TEXT=10000;

Haikuo

View solution in original post


All Replies
Solution
‎12-20-2012 11:39 PM
Respected Advisor
Posts: 3,124

Re: Import from SQL without trucating

You may want to add DBMAX_TEXT option either in libname statement or as a data set option. The default is 1024, the max is 32k, so choose some number that can accommodate your largest length:

libname testsql oledb

init_string= "Provider=SQLOLEDB.1;

     Persist Security Info= True;

     Persist Security Info= True;

     Initial Catalog = sql_database_name;

     Data Source = xxxx"

Schema=Src

DBMAX_TEXT=10000;

Haikuo

Occasional Contributor
Posts: 6

Re: Import from SQL without trucating

That did the trick! Thanks so much for your advice!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 408 views
  • 1 like
  • 2 in conversation