topic Re: pass-through for teradata using execute in SAS Programming

pass-through for teradata using execute

Gayle — Thu, 13 May 2021 14:52:59 GMT

i have a question about passthrough below is the code. I connect successfully but then it is asking for credentials in a dialog box . Why is that? does this code look correct?

proc sql noprint;
connect to teradata as exwp (username=&exwuser. password=&exwpass. tdpid=&ExWP_TDPID. mode=teradata fastload=yes);

execute(DROP TABLE work_05.CMIHistKygjtest)by teradata;
execute (create table work_05.CMIHistKygjtest as
(select MAX(baseid) as maxhistky
FROM
    (
    SELECT COALESCE(MAX(hist_ky), 0) AS baseid FROM work_05.UMD_E0_CHD_HISTORYgjtest
    UNION all
    SELECT COALESCE(MAX(hist_ky), 0) AS baseid FROM work_05.CHD_History_gjtestwae
    ) as foo
)
 WITH DATA NO PRIMARY INDEX) by teradata;

Re: pass-through for teradata using execute

ballardw — Thu, 13 May 2021 16:09:19 GMT

Did you try an example of the code with no macro variables? That would be the first thing. If that works then check how you are creating the macro variables you are providing to make sure they look the same.

Or maybe just the wrong credentials provided.

Re: pass-through for teradata using execute

Sajid01 — Thu, 13 May 2021 16:51:31 GMT

Hello @Gayle
Can we have the log?
Typically the dialog box pops up if there is something wrong with userid or Password OR authorization.

Re: pass-through for teradata using execute

Gayle — Thu, 13 May 2021 18:19:04 GMT

ok trying to redo it however , however i do not think there is an issue with the uid password that has been working

Re: pass-through for teradata using execute

Tom — Thu, 13 May 2021 18:26:07 GMT

Not sure why it is prompting you .

But you can cut down on the number of times you need to sign-in by using a LIBREF to make the connection.

Then use the CONNECT USING statement to re-use the libref connection to submit your Teradata code.

libname exwp teradata username=&exwuser. password=&exwpass. 
  tdpid=&ExWP_TDPID. mode=teradata fastload=yes
;
proc sql noprint;
connect using exwp ;
execute(...) by exwp;
...
quit;
... Other SAS steps ...
proc sql noprint;
connect using exwp ;
select ... from connection to exwp (....);
...
quit;

Re: pass-through for teradata using execute

ballardw — Thu, 13 May 2021 21:23:23 GMT

@Gayle wrote:
ok trying to redo it however , however i do not think there is an issue with the uid password that has been working

This should have been mentioned previously. Code that works for a while and then behaves differently usually points to a change in something. Since the code didn't change then something else did. I would talk to your Teradata admin to see if they changed versions or had an "upgrade" recently. An upgrade or security "patch" may have changed the default behavior for credentials and may need to have some settings modified for running scripts.

Re: pass-through for teradata using execute

Gayle — Thu, 13 May 2021 22:41:25 GMT

oh thank you , i should have used 'by exwp ' not by 'by teradata'

Re: pass-through for teradata using execute

SASKiwi — Thu, 13 May 2021 22:51:41 GMT

Correct, so I suspect that has fixed your problem?

Re: pass-through for teradata using execute

Gayle — Thu, 13 May 2021 23:31:50 GMT

will try it momentarily

Re: pass-through for teradata using execute

Gayle — Fri, 14 May 2021 00:16:19 GMT

right now my session is hanging. Once it gets unhung i will retry

Re: pass-through for teradata using execute

Dreggs — Thu, 20 Oct 2022 23:56:57 GMT

It's been a while since the last posting, but wanted to mention this has happened to me as well.

My solution was to wrap the password in quotes like below

password = "&exwpass."

Hope that helps.