topic Re: Strange SAS->ODBC database note in SAS Programming

Strange SAS->ODBC database note

LaurieF — Tue, 01 Jun 2021 03:25:15 GMT

I've been trying to get around an issue when I upload data through SAS to an ODBC-connected DBMS. I've tried all sorts of variations, but the issue won't go away. Well, I say issue, but I don't know what the issue is!

I'm creating a table via an SQL execute statement, then using a SAS-initiated insert statement to load into it. The message is:

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.

Despite this, the dataset it replicated on the other end apparently perfectly without loss at the observation or variable level. sqlxrc is 0; sqlxmsg is null; sqlobs shows the correct number.

I'm mostly convinced that it is something to do with numeric, non-datetime variables, perhaps related to where those variables contain null values. But I can't pin it down to just one variable on one row.

I've seen references to this note a couple of times elsewhere, but the explanations for it don't appear to be relevant to what I'm doing.

Any ideas?

Re: Strange SAS->ODBC database note

Tom — Tue, 01 Jun 2021 03:51:15 GMT

Show the statement (and the full log for that statement).

Most likely you are trying to use an IMPLICIT passthrough to do the INSERT and SAS is saying it cannot execute the full statement in the remote database. So something like this:

insert into mylib.mytable 
  select ....
;

If you think it should be able to do it completely in the remote database then use an EXPLICIT passthrough (just like you did to make the table).

execute by mylib
( inset into myschema.mytable 
  select ...
);

Re: Strange SAS->ODBC database note

LinusH — Tue, 01 Jun 2021 07:25:40 GMT

Whenever when you encounter issues with implicit pass through, set these options:

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;

It might give you extra hints.