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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- working with sas date and time functions
- oracle to bigquer y sql translat ion reference
- to char function with dates
- t sql data types
- oregon institute of technology
- mis 120 study guide for exam 2
- suprtool quick reference guide
- purpose of chapter is to show how welcome ict seneca
- sql server and oracle
- a software design specification template
Related searches
- support services worker job description
- frontline education support number
- frontline education tech support number
- frontline support customer service
- support worker responsibilities
- client support worker job description
- community support associate job description
- community support services job description
- direct support worker job duties
- role of a support worker
- access illinois personal support worker
- community support staff job description