Amadeus cookies policy - you'll see this message only once.

Amadeus use cookies on this website. They help us to know a little bit about you and how you use our website, which improves the browsing experience and marketing - both for you and for others. They are stored locally on your computer or mobile device. To accept cookies, continue browsing as normal. Or, go to the privacy policy for more information.

Returning the Libname String for OLE DB and ODBC

Often it is the case that it is unknown what attributes to specify when creating a libname statement when connecting to a database.

When connecting via OLE DB it is possible to be prompted by a Data Link Wizard for certain attributes by entering the statement libname oledb;

When connecting via ODBC it is also possible to be prompted by a Select Data Source Wizard for certain attributes to connect to a File Data Source or a Machine Data Source which has been configured in Data Sources(ODBC) by entering the statement libname odbc prompt;

These methods will automatically set up the Library connection, unfortunately this will not notify the user with the parameters SAS entered into the libname statement.

The parameters created by the Data Link and Data Source Wizards for each connection can be output to the log window by the use of %put %superq(sysdbmsg) statement after the libname statement is called.

The %SUPERQ function is required to mask special characters and any unbalanced parentheses or quotation marks might be created using the connection wizards which are stored in the SYSDBMSG macro.

Below is the code needed for this example:

libname mylib oledb; 
   %put %superq(sysdbmsg);
libname mylib2 odbc prompt; %put %superq(sysdbmsg);

Log window:

1      libname mylib oledb;
NOTE:  Libref MYLIB was successfully assigned as follows:
       Engine:        OLEDB
       Physical Name:
2      %put %superq(sysdbmsg);
OLEDB: Provider=SQLOLEDB.1;Integrated Security=SSPI;
       Persist Security Info=True;Initial
       Catalog=;Data Source=
3      libname mylib2 odbc prompt;
NOTE:  Libref MYLIB2 was successfully assigned as follows:
       Engine:        ODBC
       Physical Name: 
4      %put %superq(sysdbmsg);
ODBC:  DSN=;Description=;

The OLEDB and ODBC statement from the log window can then be copied and placed into a libname statement which enables the user to use in their code.

For example:

libname Amadeus OLEDB init_string="Provider=SQLOLEDB.1;
	Integrated Security=SSPI;Persist Security Info=True;
	Initial	Catalog=;Data Source=";
libname Amadeus2 ODBC noprompt="DSN=; Description=;Trusted_Connection=Yes; WSID=";

Log window:

5     libname Amadeus OLEDB 
NOTE: Libref AMADEUS was successfully assigned as follows:
      Engine:        OLEDB
      Physical Name:
8     libname Amadeus2 ODBC noprompt=XXXXXXXXXXXXXXXXXXX
9 ! ;
NOTE: Libref AMADEUS2 was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: 

This tip is based on an example in "SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition", SAS Institute.