pass-through for teradata using execute

[Gayle]

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;

[ballardw]

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.

[Sajid01]

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

[Gayle]

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

[ballardw]

---

@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.

[Tom]

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;

[Gayle]

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

[SASKiwi]

Correct, so I suspect that has fixed your problem?

[Gayle]

will try it momentarily

[Gayle]

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

[Dreggs]

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.