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

« Go Back


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
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,, 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,, 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')
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 t0.salesrep, t1.custnum,, 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:
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" -   

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   
Last Modified Date10/2/2015 6:57 AM

The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.

Was this article helpful?


Your feedback is appreciated.

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

Characters Remaining: 255