How to test the existence of a database Teradata before logging on

[psgard]

Hello,

libname DATAB TERADATA user=&userp password=&passwdp server=&serverp database=&dbp_dtm_sellsec;
ERROR: Cannot connect to the specified database.  Try changing or removing your DATABASE= option.
ERROR: Error in the LIBNAME statement.

This ERROR is normal because the database was unavailable.

I would know how to test if a database Teradata is available or if the user can access to this base before to create the libname or before to do a "connect to Teradata" instruction,  and so to prevent the ERROR Message.

I succeed in testing this with "dopen" for a simple SAS Library , but how to do this for  a Teradata, Oracle or other external database ?

Perhaps  with catching ERROR ? But I want not to have the ERROR message in the log of the treatment. At the end of the treatment i want to have a RCode =0.

I use SAS ACCESS TO TERADATA.

Thank for your help.

[tomrvincent]

Maybe this will help you:

https://communities.sas.com/t5/General-SAS-Programming/check-quot-active-quot-connection-to-oracle-l...

[Tom]

I am not sure I follow the logic of the request. It sounds like you want to know how to tell if you can connect to Teradata without actually trying to connect to Teradata.  If such a function existed you would need to check with Teradata for what it is.

Back in the old days we had an engineer put a detector on the error light on the computer console and controlled a red light in the hallway. That way programmers could know when the machine crashed because the red light was turned on.  Sounds like you want something similar for your Teradata database.

[psgard]

Hello,

Sorry for the late answer.

I want to create a generic autoexec.sas for all users in my organization.

In the autoexec.sas i want to define main libnames  (Oracle, Teradata, or SAS databases).

But i my organization, there are a lot of user profiles and not all of them have the same rigths to data libraries.

If i set a libname on a data library on which the user does'nt have rights to access, the autoexec.sas will send an error.

So i want the autoexec to test if the libname can be set without error.

Thanks

Best regards

[SASKiwi]

@psgard - Please don't post a new question in the middle of an old one, particularly those already answered, as it just confuses people. Creating a new post will give you much better visibility and you can add a link back to this post if you think it would be useful.

[Patrick · Accepted solution]

Instead of using the global libname statement you could use the libname function to assign the library because this lets you test for success or failure. Some code along the line of example 1 from the docu found here could do the job.

And for the connect statement: Use syntax "connect using <libref>" and assign the libref with the libname function.

[psgard]

Hello,  

thank you. I used the function and it's ok . I can test and manage libname without ERROR with this function .

/* format convertion code retour en libellé statut de connexion */
 proc format;
 value LIB_ERR
  -70004  = "Connectée"
  0  = "Connectée"
  70029 = "Non Autorisé"
  630081 = "Déjà Connectée"
  other = "ERROR"
 ;
 run;

 
 /* On parcours le référentiel des librairies et on ouvre les libnames */
 /* les erreurs de connexion sont tracees dans la table journal */
 data PRIVE.LIBRAIRIES;
  set SAS_LIB.AUTOEXEC_LIB;

  format
   RC 10.
   DATETIME datetime.
  ;
  length
   RC 8.;
  label
   RC='Code retour connexion librairie'
   STATUT='Statut de la connexion'
   MSG = 'Message Erreur'
   DATETIME= 'Date et heure de connexion';

  RC=libname(LIBNAME, CHEMIN, , );
  STATUT=put(RC,LIB_ERR.);
  msg=sysmsg();
  datetime=datetime();
 run;

[Patrick]

Thanks for sharing