Article

How to query a Progress database via a Microsoft SQL Server Linked Server?

« Go Back

Information

 
EnvironmentProduct: Progress
Version: 9.1x
Product: OpenEdge
Version: All supported versions
OS: All supported platforms
Other: Microsoft SQL Server
Question/Problem Description
How to query a Progress database via a Microsoft SQL Server Linked Server?
How to query an OpenEdge database via a Microsoft SQL Server Linked Server?
What SQL statement needs to be used to query an OpenEdge database via a Linked Server on Microsoft SQL Server?
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
Use OpenQuery() function to query Progress Database on the SQL Server. The OpenQuery() function executes the specified pass-through query on the specified linked server; the SQL query will be executed server-side, e.g. by the OpenEdge SQL engine, meaning that any Progress SQL-92 compliant SQL query can be executed:

SELECT * FROM OpenQuery([LINKEDSERVERNAME], '<Any Progress SQL-92 compliant SQL statement here>')

Fuller syntax options are:

SELECT [*] | [fieldname], ["field-name"] FROM OpenQuery([LINKEDSERVERNAME], 'SELECT [*] | [fieldname], ["field-name"] FROM PUB.[mytable] | ["my-table"]')

SELECT [*] | [fieldname], ["field-name"] FROM OpenQuery([LINKEDSERVERNAME], 'SELECT [*] | [fieldname], ["field-name"] FROM PUB.[mytable] | ["my-table"] WHERE 
                              [fieldname] | ["field-name"] = 123 | ''one''')


If the field in the WHERE clause is character, two sets of single quotes are needed around the string value.  In the above example, the string value *one* is wrapped with two sets of SINGLE quotes instead of one sets of double quotes.  The last single quote is the closing single quote of the SELECT query inside the OpenQuery() function.
The pipe (|) is denoted for OR or option.  The example shows that if the table or field names include the dashes (-), you need to add double quotes around it.

The following examples demonstrate the OpenQuery against the sample sports2000 database:

1.  Show all field records of the customer table from the sports2000 database:

SELECT * FROM OpenQuery([sqlsport2k], 'SELECT * FROM PUB.customer')

2. Show only records available pertaining to Massachusetts from the State table of sports2000 database:

SELECT * FROM OpenQuery([sqlsport2k], 'SELECT * FROM PUB.state WHERE state = ''MA''')

3. Show only the custnum and name fields records of the customer table from the sports sports2000 database:

SELECT * FROM OpenQuery([sqlsport2k], 'SELECT custnum, name FROM PUB.customer')

4. Show name field records of the customer table from the sports2000 database:

SELECT name FROM OpenQuery([sqlsport2k], 'SELECT custnum, name FROM PUB.customer')


5.  Show all field records from a query that returns fields Salesrep.salesrep, Customer.custnum, Customer.name, Order.ordernum from the Salesrep table joined on Customer table left outer joined on the Order table of the sports2000 database:

SELECT * FROM OpenQuery([sqlsport2k], 'SELECT t0.salesrep, t1.custnum, t1.name, t2.ordernum FROM pub.salesrep AS t0 INNER JOIN pub.customer AS t1 ON t0.salesrep = t1.salesrep LEFT OUTER JOIN pub.order AS t2 ON t1.custnum = t2.custnum')
Workaround
Notes
It is possible to execute queries against a Linked Server database without using OpenQuery. However, such queries are executed client-side and therefore may not perform as well. Examples:

SELECT * FROM [SQLSPORT2K].[SPORTS2000].[PUB].[Salesrep]

SELECT t0.salesrep, t1.custnum, t1.name, t2.ordernum FROM [SQLSPORT2K].[SPORTS2000].[PUB].[Salesrep] AS t0 INNER JOIN [SQLSPORT2K].[SPORTS2000].[PUB].[Customer] AS t1 ON t0.salesrep = t1.salesrep LEFT OUTER JOIN [SQLSPORT2K].[SPORTS2000].[PUB].[Order] AS t2 ON t1.custnum = t2.custnum



References to other documentation:

Microsoft Documentation:
OPENQUERY (Transact-SQL): http://msdn2.microsoft.com/en-us/library/ms188427.aspx
Error messages when you perform an UPDATE, INSERT, or DELETE Transact-SQL statement on a remote table by using the OpenQuery function: "7357" and "7320" - http://support.microsoft.com/kb/270119   

Progress Article(s): 
P120484, How to link to a Progress / OpenEdge database from Microsoft SQL Server using a Progress / OpenEdge ODBC Driver ?   
P116579, How to determine the optimal setting for the Fetch Array Size feature in the OpenEdge ODBC Driver ?   
20255, Transaction Isolation Level: Proper Syntax and Definitions   
P131153, How to create a Microsoft SQL Server Reporting Services Report Model on an OpenEdge database   
Attachment 
Feedback
 
Was this article helpful?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful.



Characters Remaining: 255