DBAppender Connection Options for ODBC

The following is an example of the basic syntax for specifying options in the ConnectionString parameter of a DBAppender configuration for an ODBC-compliant database. If you need additional details, contact SAS Technical Support.

Valid in:	ConnectionString parameter of DBAppender configuration
See:	DBAppender

Table of Contents

Syntax

CATALOG=catalog-identifier;
ODBC_DSN=ODBC-DSN-name
DRIVER=ODBC;
PWD=password;
UID=user-id;
CONOPTS=(valid-ODBC-compliant-database-connection-string);

Syntax Description

The data source connection options for an ODBC-compliant database include the following:

CATALOG=catalog-identifier

specifies an arbitrary identifier for an SQL catalog, which groups logically related schemas.

For the Microsoft SQL Server, you can specify a logical name for the catalog, and map it to the native catalog name that is defined in the SQL Server. For example, to specify the logical catalog logcat and map it to a native catalog called sqlcat, you would specify the following:

catalog=(logcat=sqlcat);

For databases that do not support native catalogs, any identifier is valid (for example, catalog=myodbc).

Default	If this parameter is omitted for the Microsoft SQL Server, the default setting CATALOG=* is used.
Requirements	For the Microsoft SQL Server, which is a multiple-catalog database, CATALOG= is optional.
Requirements	For databases that do not support native catalogs, you must specify a catalog.

ODBC_DSN=ODBC-DSN-name

specifies a valid ODBC-compliant database DSN that contains information for connecting to the ODBC-compliant database.

Interaction

To specify database-connection options that cannot be specified with the other ConnectionString parameters, you can use the CONOPTS= option along with the ODBC_DSN option. However, do not specify the ODBC DSN in both CONOPTS= and ODBC_DSN=.

DRIVER=ODBC;

identifies the type of data source to which you want to connect, which is ODBC.

Requirement

You must specify the driver.

PASSWORD=password;

specifies the password that is associated with the user ID.

Alias

PWD=

USER=user-id;

specifies the user ID for logging on to the ODBC-compliant database.

Alias	UID=
Default	If no user ID is specified, the default user for the database is used to log on.

CONOPTS=(valid-ODBC-compliant-database-connection-string);

specifies, within parentheses, an ODBC-compliant database connection string. This optional parameter enables you to specify connection options that cannot be specified with the other ConnectionString parameters. Here is an example:

If the database uses a DSN, you can use this parameter to specify a value for DSN= or FILESDSN=. Here is an example:
```
CONOPTS=(DSN=LogSql);
```
For databases that do not use a DSN, use this parameter to specify the DRIVER= keyword. Here is an example:
```
CONOPTS=(DRIVER=SQL Server);
```

Interaction

Do not specify the ODBC DSN in both CONOPTS= and ODBC_DSN=.

Example: Connection String for ODBC

The following example uses ODBC to connect to a Microsoft SQL Server:

DRIVER=ODBC;ODBC_DSN=LogSql;UID=User1;PWD=Password1;