Name not in HOSTS file error - SAS

Connectivity
Fan

Name not in HOSTS file error - SAS

I've contacted many people within my company who use SAS, but I can't figure out how to resolve my issue and am running out of places to turn.

I primarily use SAS 9.1.3 to create automated audits that connect to (and pull data from) Teradata and Oracle databases.

I recently upgraded to a new work laptop when my old machine died.

SAS 9.1.3 was installed on my new machine.

Both SAS and Teradata versions remained the same betwen my old and new laptops.

Teradata queries from SQL Assistant connect and run successfully.

I was able to run Teradata queries from within SAS BEFORE I received a new laptop and but not AFTER.

SAS programs that I create are stored on our team's shared computer. 

Teradata sql queries from SAS won't work from my new machine, but they work from my co-worker's machine that accesses the exact same code.

EXAMPLE CODE THAT I AM TRYING TO RUN:

%include     '\k1234567\SAS_AUDITS\PROCESS_FILES\MACROS\passwords.sas';

libname perm '\k1234567\SAS_AUDITS\PROCESS_FILES\Tables';

**************************************************************************************;

proc sql; connect to teradata(user=&acf2 password=&terapword tdpid=tdpe mode=teradata); execute (


          UPDATE   A1_TEST_TBLS.TMP_JOB_CNTL

          SET         START_DATE            = CURRENT_DATE

                        ,START_TIME            = SUBSTR(CAST(CURRENT_TIME(0) AS CHAR(14)),1,5)

                        ,COMPLETE_DATE     = NULL

                        ,COMPLETE_TIME     = NULL

          WHERE       PROGRAM_NAME  = 'GLFDM.sas'

) by teradata;  execute (COMMIT WORK) by teradata; quit;

Everytime I try executing SAS statements that query against Teradata, an error is generated (pasted below) and a DB login window pops up.

ERROR: Teradata connection: MTDP: EM_NOHOST(224): name not in HOSTS file or names database.

I am able to successfully run Teradata queries from SQL Assistant.


SET-UP AFTER INSTALL

    - I updated my hosts file, tsotcp_ibm.scr, tnsnames.ora entries  (by using copies of these files that I saved from my old machine) .

    - I also tried using several co-workers hosts file.

    - I tried hard-coding the Teradata login credentials, but none of these helped resolve the issue.

ORACLE CONNNECTIONS - GOOD

    - My Oracle queries run in SAS with no problems.

OTHER STEPS I'VE TRIED USING TO RESOLVE ISSUE:

    - I tried removing SAS 9.1.3 and installing SAS 9.2.  That did not work so I reinstalled SAS 9.1.3

    - I tried uninstalling and reinstalling Teradata 12.0.

    - I have verified that Teradata CLIv2 was installed on my machine.

    - I have verified my Teradata password is correct.

    - I have tried other people's hosts files.

    - I've tried googling for help.

    - I have verified that ODBC Driver for Teradata was installed (Queries against Teradata within SQL Assistant work fine).

HOSTS FILE


# Copyright (c) 1993-1999 Microsoft Corp.

#

# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.

#

# This file contains the mappings of IP addresses to host names. Each

# entry should be kept on an individual line. The IP address should

# be placed in the first column followed by the corresponding host name.

# The IP address and the host name should be separated by at least one

# space.

#

# Additionally, comments (such as these) may be inserted on individual

# lines or following the machine name denoted by a '#' symbol.

#

# For example:

#

#      102.54.94.97     rhino.acme.com          # source server

#       38.25.63.10     x.acme.com              # x client host

127.0.0.1       localhost

144.222.111.130 tdpecop1     tdpe     #pdal1111.corp.company.com

144.222.111.131 tdpecop2     tdpe     #pdal1112.corp.company.com

144.222.111.132 tdpecop3     tdpe     #pdal1113.corp.company.com

144.222.111.133 tdpecop4     tdpe     #pdal1114.corp.company.com

TERADATA CONNECTION BOX:


When I run the code above, this box displays.

SAS LOG:


78   proc sql;

78 !           connect to teradata(user=&acf2 password=&terapword tdpid=tdpe mode=teradata);

ERROR: Teradata connection: MTDP: EM_NOHOST(224): name not in HOSTS file or names database.  .

78 !                                                                                         execute (

79

80             UPDATE      P6_ADHO_TBLS.TMP_JOB_CNTL

81             SET            START_DATE         = CURRENT_DATE

82                              ,START_TIME         = SUBSTR(CAST(CURRENT_TIME(0) AS CHAR(14)),1,5)

83                              ,COMPLETE_DATE  = NULL

84                              ,COMPLETE_TIME  = NULL

85             WHERE       PROGRAM_NAME  = 'GLFDM.sas'

86

87   ) by teradata;

ERROR: Teradata connection: CLI2: CANCELLOGON(370): Logon is canceled.  .

87 !                 execute (COMMIT WORK) by teradata;

ERROR: Teradata connection: MTDP: EM_NOHOST(224): name not in HOSTS file or names database.  .

87 !                                                    quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

      real time           3:27.09

      cpu time            0.65 seconds

CLIv2


SAS SCREEN SHOT


I MUST BE FORGETTING SOME CRUCIAL STEP.

Do you happen to have any suggestions on what I could be forgetting to update that is preventing me from connecting to Teradata through SAS?

3 REPLIES
Teradata Employee

Re: Name not in HOSTS file error - SAS

Search for CLISPB.DAT file on your old PC and copy it to your new PC.

CLI reads default configuration information from the CLISPB.data file when application does not specify them. I am guessing SAS is not passing the DBCName to CLI because the "Connect to Teradata DBS" dialog box shows the default DBC Name instead of "tdpe".
Fan

Re: Name not in HOSTS file error - SAS

Thank you so much, NetFx!

Unfortunately, the files on my old machine were not saved.  However, I did get a copy of my co-worker's clispb.dat.  All of my SAS jobs are automated to run from her machine each morning.  We both are running the exact same versions of SAS and Teradata. 

I compared my clispb.dat file to my coworker's clispb.dat file, and they are identical to each other.

Teradata Employee

Re: Name not in HOSTS file error - SAS

The connection box only shows up if username and password were not supplied to CLI (and GUILOGON is not set to NO).

Considering the DBC Name in the connection box is dbc, your TDPID also did not make it to CLI, so it used the default.

Cancelling the connection dialog prompt results in CLI 370 (as it should), OK'ing without changing any values results in CLI 224, because "dbc" is not a valid hostname (and you're not trying to connect to it anyway).

I recommend double checking your code, starting with that suspicious looking space right before "passwords.sas".