Data Connection Usage Notes

SAS Data Management Server Connections

Connecting SAS Data Management Studio to a Secure Socket Layer (SSL)-enabled SAS Data Management Server

When you connect from SAS Data Management Studio to a Secure Socket Layer (SSL)-enabled instance of SAS Data Management Server, the connection will fail unless SAS Data Management Studio is also SSL-enabled.

ODBC Connections General

ERROR: loading type library/DLL

If you get "error loading type library/DLL" when doing any level of authentication in Data Management Studio, take the following steps. Either remove HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib\{87AD4890-333C-451F-8986-C46885C0C8E 7}\1.0 from the registry or change the HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib\{87AD4890-333C-451F-8986-C46885C0C8E 7}\1.0\0\win32 value to reflect the value in HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib\{87AD4890-333C-451F-8986-C46885C0C8E7}\1.1\0\win32.

How do I change ODBC credentials that have been saved to the Windows Registry?

Data Direct ODBC drivers do not update any settings for System DSNs when changes are made to the connection options in the logon dialogs. Instead, they will write any values that a user changes into the Windows registry for the current user. This prevents multiple users who are sharing a data source from changing each other's settings. However, if you need to change the settings that were written the registry, open the Registry Editor (regedit). Expand HKEY_CURRENT_USER -> Software -> ODBC -> ODBC.INI. Select the DSN with the incorrect values. Update the values as needed and click OK. Close the registry.

Error message when testing a connection for a localized DSN

When you receive an error message when you test a connection for a localized DSN and you cannot determine where it fails, check the log. The check for credentials for localized DSNs is additive, so you need to check the log to determine which specific check failed.

ODBC Connections Wire Protocol

Are there any special considerations for ODBC drivers using the wire protocol?

DataDirect provides a number of wire protocol ODBC drivers that communicate directly with a database server, without having to communicate through a client library. If these drivers are available at your site, they are available from the Drivers tab of the ODBC Data Source Administrator dialog.

If you use a wire protocol driver to create an ODBC connection, the following special considerations apply:

[00007350] ERROR DF.DAC - Operation failed: [22001]

[SAS][ODBC Oracle Wire Protocol driver]String data, right truncated. Error in parameter 1. (0)

Since disabling Enable N-CHAR Support is probably not an option, the recommended workaround is to add a new string, ColumnSizeAsChar, to the data source in the Windows registry, or in ODBC.INI for Unix systems.

WARNING: Using a registry editor incorrectly can cause problems with your system. If you are not sure about this procedure, contact your system administrator for assistance.

First, determine what type of DSN you are editing (system or user) because the registry path to a system DSN differs from the path for a user-defined DSN as shown below:

User DSN: HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\<DSN NAME>

System DSN: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<DSN NAME>

To create the ColumnSizeAsChar string in the registry:

  1. Open the registry editor and navigate to the data source (DSN NAME) using one of the paths shown above.
  2. With your cursor positioned on the <DSN Name> entry, right-click and select New > String Value.
  3. A 'new value' object appears in the registry - key in the name ColumnSizeAsChar and enter.
  4. Right-click the new ColumnSizeAsChar object and select Modify.
  5. In the Value Data field, enter 1 and click OK. The new string now appears in the registry:



Example: New ODBC String Value

ODBC Domain-Enabled Connections

Domain-enabled connections are not displayed in lineages

Domain-enabled connections are not displayed in lineages. Instead, the regular connections that the domain-enabled connections refer to are used in lineage displays.

How can I change a domain-enabled ODBC DSN data source to a non-domain-enabled ODBC DSN source in the Data Source node?

After saving a job with a Domain Enabled ODBC Connection, you need to change the data source from a domain enabled ODBC DSN to a regular, non-domain ODBC DSN. The change will not be saved and the domain enabled ODBC DSN will remain selected in the data source node. This behavior occurs because the application is pulling cached information from the domain enabled ODBC DSN.

Correct this behavior as follows: After saving the job, exit and restart SAS Data Management Studio to clear the cached information. Revisit the job and change the data source to a non-domain ODBC DSN. When the job is saved, the non-domain ODBC DSN is associated with the job.

ODBC Hadoop Connections

Avoid Using an Impala Table as the Output of Any Job Node

Cloudera Impala is optimized for queries rather than inserts into the Hadoop file system (HDFS). Accordingly, avoid using an Impala table as the output of any job node.

Setting the Maximum Character String Size for Apache Hive

Apache Hive has a single data type for storing text, STRING, which is a variable-length character string with a maximum size of 2Gb. As a result, this can create very large character fields when processing data. Since Hive’s string type is comparable to VARCHAR in other data sources, you can set the ODBC attribute, Max Varchar Size to specify the maximum character string size. Set the Max Varchar Size value using Advanced Options in Windows ODBC Administrator, or in Unix by editing odbc.ini in the specified path or $HOME directory.

Can I use a business rule task to insert a row into an Apache Hive table?

Data can only be inserted into an Apache Hive table by reading it from another table, so you cannot use a business rule task to insert a row. Apache Hive does not support the method used by business rule tasks.

If you create an index on your Hive table (INDEX_TABLE), you cannot use Impala.

Hive indexes are not supported on Impala, so you cannot use the DSN ODBC Impala Wire Protocol driver to view Impala table fields and data.

Does Apache Hive Table Support Row-Level Insert, Update, or Delete?

Apache Hive does not support row-level insert, update, or delete. Accordingly, avoid using an Apache Hive table as the output of these data job nodes:

Why did I received an error message for "Error retrieving data" and the log stated "Failed to load metadata....due to unsupported column type"?

That error indicates that you used an unsupported data type . According to the ODBC User's Guide "Part 4: The Connect XE Drivers", the only data types supported by the ODBC 7.1.3 Impala Wire Protocol driver are:

ODBC Netezza Connections

What limitations apply to Netezza Connections?

Netezza connections are supported in only data flow jobs. They are available for only the following nodes:

Also, note that the Boolean data type is not currently supported on Netezza.

ODBC Oracle Connections

What is the proper Wire Protocol Mode setting for Data Direct DSNs for the Oracle Wire Protocol?

Data Direct DSNs for the Oracle Wire Protocol have a Performance tab. This tab has a Wire Protocol Mode field. When the DSN is used to connect to Oracle 11 or later, the Wire Protocol Mode field should be set to a value of 2.

SAP Connections

Error: "Unable to load extension: (tkesapf)" when connecting to an SAP System

This error probably means that the SAP libraries (DLLs) are not installed on the computer where they are needed. See the SAP documentation for details about installing these libraries on various platforms. SAP libraries must be installed on all computers where a connection to the SAP system is attempted. This could include the computer where SAS Data Management Studio is installed; the SAS Data Management Server where jobs that use SAP connections are executed; and the Federation Server if that server is used to manage connections to an SAP system.

Does the SAP Remote Function Call node support shared logins?

The custom connection that is defined for the SAP Remote Function Call node cannot reference a shared login. The SAP Remote Function Call node does not support shared logins.

SAS Data Set Connections

What Versions of SAS Can Be Accessed with a SAS Data Set Connection?

A SAS Data Set Connection in SAS Data Management Studio 2.5 or later uses a SAS 9.4 TKTS Base driver. Accordingly, it can access any SAS version 8 or version 9 data set.

A SAS Data Set Connection in SAS Data Management Studio 2.4 or earlier uses a SAS 9.2 TKTS Base driver. Accordingly, it can access any SAS version 8 data set. It can also access any version 9 data set that was not created with the Extend Observation Counter turned on.

ODBC SQL Server Connections

Why should I use the SAS SQL Server Wire Protocol driver?

You cannot use a DSN that specifies the SAS SQL Server Legacy Wire Protocol driver or the SAS SQL Classic Wire Protocol driver. If you have an ODBC DSN that uses one of these drivers, replace that DSN with one that uses the appropriate SAS SQL Server Wire Protocol driver.

Why does my table appear twice in common Select Table dialog when I use SQL Server ODBC DSN?

When using a SQL Server ODBC DSN, if you select a table using the Select Table dialog, close that dialog, and then re-open it, you will see your table name appear twice in the table list. This does not affect the functionality of the application. You can select either table listed in the tree.

When I'm running a profile report, if I try to access anything in my repository it appears to lock up the client. What can I do to keep it from locking up?

If you use an SQL Server DSN connection, you can avoid locking issues by setting the DAC option Readuncommitted as described in the "Data Access Component Directives" topic in the SAS Data Management Studio Installation and Configuration Guide.

ODBC Teradata Connections

The DSN for my data source uses a DataDirect 7.0 Driver for Teradata. It takes too long to expand the DSN in the Data Connections tree.

In the properties window for the DSN, click the Options tab and deselect the Show Selectable Tables check box.

ODBC XML Connections

How can I read an XML file in a data job?

Due to the limitations of the ODBC 32-bit XML driver, we recommend that you use the XML Input node to read an XML file in a data job.

How can I read an XML File in a profile?

We recommend that you extract the data from the XML file to a text file or to a database table, and then profile the text file or table. To extract the data, create a data job in which the XML Input node is used to read the XML file, and then use other nodes to output a text file or a database table.