Article

How to query Progress Database on the link server on the Microsoft SQL Server?

« Go Back

Information

 
EnvironmentProgress 9.x
OpenEdge 10.x
Windows
Question/Problem Description
How to query Progress Database on the link server on the Microsoft SQL Server?
How to query Progress Database on the linked server on the Microsoft SQL Server?
What SQL do I need to use to query Progress Database on the link server on Microsoft SQL Server?
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
Use OpenQuery() function to query Progress Database on the SQL Server as the following syntax:

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''')

The following examples are used with sports2000 database:

-- This example will show all the fields records of the customer table from the sports2000 database:

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

-- This example will show records available pertaining to Massachusetts from the state table of sports2000 database:

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

-- This example will show custnum and name fields records of the customer table from the sports sports2000 database:

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

-- This example will show name field records of the customer table from the sports2000 database:

SELECT name FROM OpenQuery([sqlsport2k], 'SELECT custnum, name FROM PUB.customer')
Workaround
Notes
If the field in the WHERE clause is character, you need to use two sets of single quotes around the string value.  On 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.
References to Written Documentation:

http://msdn2.microsoft.com/en-us/library/ms188427.aspx

http://support.microsoft.com/kb/270119
Attachment 
Feedback
 
Was this article helpful?

   

Your feedback is appreciated.

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



Characters Remaining: 255

 



Copyright © 1993-2014. Progress Software Corporation. All Rights Reserved. See the next generation of application development and data connectivity software, try now!