ODBC Error HY104 Invalid Precision value

Reply
Super Contributor
Posts: 418

ODBC Error HY104 Invalid Precision value

Hello everyone.  I have a proc sql statement that inserts into a sql-server data table from a sas Dataset.

I have one field that is a text field in the sas dataset (length 4,500) and is a varchar(5,000) in the sql server table.  When I try to do the insert on this field I get the following error.

ERROR: A database error occurred. The database specific error follows:

       DATABASE error: HY104: [Microsoft][ODBC SQL Server Driver]Invalid precision value

BackGround:

I know it's the varchar field and not the other fields I'm inserting, because when I remove that field the insert works.  Here is my libname statement and the sql query below.

DOESN'T WORK

libname Nowork odbc dsn='odbc_fakeodbcname' schema=dbo  dbmax_text=32000;

proc sql;

insert into nowork.SqlTable(loanid,loan,reviewcomments)

select loanid

,loannumber

,reviewcomment

from TempSasTable;

quit;

run;

However... If I make a simple change to the libname statement, and include the "bulkload=Yes" option this error goes away. Aka the libname statement below works (on the same table).

WORKS:

libname whywork odbc dsn='odbc_fakeodbcname'  schema=dbo  bulkload=yes dbmax_text=32000;

proc sql;

insert into nowork.SqlTable(loanid,loan,reviewcomments)

select loanid

,loannumber

,reviewcomment

from TempSasTable;

quit;

run;

The problem is, in my new application I am forced to insert into a table that has an autoincremented identity key (I didn't make table so can't change this).  Therefore I cannot use the Bulkload=yes option in my libname statement because I am not loading every column.

So.... an insert into a table I fully popoulate works, but inserting the exact same 3 columns into a different table (that has additional columns that my code doesn't populate) fails.

Questions:

#1 What is this error?  Why would a varchar field be throwing an Invalid precision Value error?

#2 Why would bulkload stop this error from happening?

#3 How would I get around this error without the bulkload step, as I CANNOT use bulkload to fix the problem.

Thanks, and let me know if anyone has any questions!

Super Contributor
Posts: 418

Re: ODBC Error HY104 Invalid Precision value

Some follow up on this problem.  It turns out if the original table has the field specified as a varchar(4000) or less, then this problem doesn't happen...    The query actually works as an insert with and without the bulkload=yes option.

However the second you go above 4,0001  this error is showing up.  It is my understanding that a sql server table can have a field of varchar(X) where X is between 0-8000 (or max).

In addition, if I take away the option "DBMAX_TEXT"  then the program also works... However it will truncate this column to a value of 4,000 by default (since this is what SAS does apparently).

Ask a Question
Discussion stats
  • 1 reply
  • 2058 views
  • 0 likes
  • 1 in conversation