UniAccess Support



Applied Information Sciences

UniAccess

WHITE PAPERS

LAST UPDATED: May 31, 2005

Using Microsoft SQL Server with UniAccess

This paper discusses methods of using UniAccess with Microsoft SQL Server. The paper covers two topics: Calling UniAccess Using Microsoft SQL Server Distributed Queries, and Using UniAccess to Replicate Microsoft SQL Server Data to RDMS 2200.

Calling UniAccess Using Microsoft SQL Server Distributed Queries

This section defines how to call UniAccess using Microsoft SQL Server’s Distributed Query feature. There are four methods that can be used to access RDMS databases: Four-part names, OPENQUERY, OPENROWSET, and, beginning with SQL Server 2000, OPENDATASOURCE. Using the four-part names and OPENQUERY methods, the UniAccess server must first be configured as a Linked Server under SQL Server. Then distributed queries can be made against UniAccess ODBC Server for OS 2200 and/or UniAccess Transaction Server for OS 2200.

[pic]

Configuring UniAccess as a Microsoft SQL Server Linked Server

Microsoft SQL Server linked servers are OLE DB data sources that can be referenced using four-part names in distributed queries.

1. Using the ODBC Data Source Administrator, create a System DSN using UADriver. This is the UniAccess server for which you will create a Microsoft SQL Server Linked Server. Test the connection using the Test tab in the UniAccess configuration dialog.

2. Using the SQL Server Enterprise Manager (depicted in Figure 1), expand a server group, expand a server, and then expand Security.

3. Right-click Linked Servers; then click New Linked Server. The Linked Server Properties — New Linked Server dialog will open.

4. On the General tab, specify the name of the server to add as a linked server. This will be the name that is used from T-SQL. It can be, but need not be, the same as the DSN created in step 1.

5. Under Server Type, select Other data source.

6. Under Provider Name, select Microsoft OLE DB Provider for ODBC Drivers.

7. There are several options for the next step. The approach taken will be dictated by the type of security desired. The following is one of the options as depicted in Figure 2, Figure 3, and Figure 4:

a. Under the Provider String option specify a connection string as follows:

DSN=uasystemdsn;UID=userid;PWD=password

where:

uasystemdsn is the System DSN created in step 1 above.

userid is a valid OS 2200 userid.

password (optional) is a valid OS 2200 password. This is (required if TIP session control is active, otherwise, leave blank).

b. Under the Security Tab, select Be made without using a security context.

c. Use the Linked Server Options tab to view or set the values for the various options.

d. Close the Linked Server Properties dialog. The new linked server should appear under Linked Servers in the SQL Server Enterprise Manager window (as depicted in Figure 5).

8. If you wish to run a SQL Server select query using four-part names against the linked server, you must configure your UniAccess DSN to support the owner field. To accomplish this, go into the UADriver Setup for your particular DSN, select the Advanced tab, and set Workarounds to 32 (see Figure 6).

Figure 1: SQL Server Enterprise Manager

[pic]

Figure 2: Linked Server Properties Dialog — General

[pic]

Figure 3: Linked Server Properties Dialog — Security

[pic]

Figure 4: Linked Server Properties Dialog — Server Options

[pic]

Figure 5: SQL Server Enterprise Manager Showing New UniAccess Linked Server

[pic]

Accessing RDMS 2200 Databases Using Microsoft SQL Server’s Distributed Query Feature

RDMS 2200 databases can be accessed indirectly through Microsoft SQL Server using OPENQUERY, four-part names, OPENROWSET, and OPENDATASOURCE methods as T-SQL statements. The OPENQUERY and four-part names statement require a linked server be configured as described above. The OPENROWSET and OPENDATASOURCE methods, also known as ad hoc queries, do not require a linked server. All these methods provide read-only access (SQL SELECT) to RDMS data. In order to update RDMS data, we recommend using distributed query to call RPCs, discussed at the end of this section.

|Note: The root cause of update statements not working is that SQL Server (OLE DB/ADO) is attempting to retrieve information about each of the |

|columns in the insert list via SQLColAttributes. For example, a call is made to SQLColAttributes to obtain the owner of the column. The owner|

|information is not available as a column attribute. |

| |

|UADriver cannot return the owner information on a column basis via SQLColAttributes, since the only information available regarding a column is|

|that returned by RDMS via DECLARE ... CURSOR ... WITH DESCRIPTION. Furthermore, it is limited by the TDS, which does not send the qualifier |

|(a.k.a. RDMS schema), nor table name. |

| |

|If the update statements could be submitted using four-part names, performance would likely be unacceptable. When updating data on a linked |

|server using four-part names, SQL Server will retrieve all of the data in the table (or set of tables), descriptive information about each |

|column, and then submit the update statement. For additional information, please see the Microsoft Knowledge Base article "PRB: Slow DELETE or|

|UPDATE Against Non-SQL Linked Server" . |

Accessing RDMS 2200 Databases Using Microsoft SQL Server’s OPENQUERY

The OPENQUERY function accepts two parameters: the name of the linked server and the text of the query to pass. The format of a SQL select statement using OPENQUERY is as follows:

FROM OPENQUERY (linkedserver.[database].[owner].table

where:

linkedserver is the name of the linked server specified in step 4 of Configuring UniAccess as

a Microsoft SQL Server Linked Server above. Note: The DSN associated with this linked

server must be configured with Workarounds = 32. (Note: Expanding the table view under the Linked Servers in the SQL Server Enterprise Manager will display the owner under the Schema column.)

database (optional) is the name of the database. This field must be present if using a non-default database.

owner (optional) is the name of the database owner or, if unowned, the values "PUBLIC" or

[PUBLIC]. If this field is present, workarounds must be set to 32.

table is the name of the table.

The following examples show SQL select statements using a OPENQUERY:

SELECT a.* FROM OPENQUERY (LINK2UNIACCESS, 'select * from UASAMPLE.SECOFFUSERID.employee') as a

SELECT a.* FROM OPENQUERY (LINK2UNIACCESS, 'select last_name from..employee') as a

A more complex OPENQUERY statement demonstrates the ability to merge data from separate servers:

SELECT distinct * FROM OPENQUERY(LINK2UNIACCESS, 'select * from UNIACCESS.. EMPLOYEE') a, SQLSERVERDATABASE..SQLSERVERTABLE b where a.column1 = b.column1

Accessing RDMS 2200 Databases Using Microsoft SQL Server’s Four-Part Names

The format of a SQL select statement using four-part names is as follows:

FROM linkedserver.[database].[owner].table

where:

linkedserver is the name of the linked server specified in step 4 of Configuring UniAccess as

a Microsoft SQL Server Linked Server above. Note: The DSN associated with this linked

server must be configured with Workarounds = 32.

database (optional) is the name of the database. This field must be present if using a non-default database.

owner (optional) is the name of the database owner or, if unowned, the values "PUBLIC" or [PUBLIC]. If this field is present, workarounds must be set to 32. (Note: Expanding the table view under the Linked Servers in the SQL Server Enterprise Manager will display the owner under the Schema column.)

table is the name of the table.

The following examples show SQL select statements using four-part names:

SELECT * FROM LINK2UNIACCESS.UASAMPLE.SECOFFUSERID.EMPLOYEE

SELECT * FROM LINK2UNIACCESS.UASAMPLE."PUBLIC".EMPLOYEE

SELECT LAST_NAME FROM LINK2UNIACCESS..EMPLOYEE

Accessing RDMS 2200 Databases Using Microsoft SQL Server’s OPENROWSET

The OPENROWSET statement can be used without a linked server because the entire connection string can be supplied right in the OPENROWSET statement. The format of a SQL select statement using OPENROWSET is as follows:

FROM OPENROWSET (‘MSDASQL’, DRIVER=dsn;HOST=host;PORT=port;UID=userid;PWD=password’, ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download