SQL and Progress for linked server

Question

Right now, we have a Linked Server set up from the production SQL server called SQL4 to the kproduction Progress Database. Linked servers have some limitations, or maybe I haven’t found the right solution. I can’t pass in a dynamic variable into the OpenQuery script. So, if I want to pull a list of orders shipped yesterday from the kproduction Progress Database, I have to hard code the “yesterday” part of the query.

Is a Linked Server the only option here? Is there a better solution to enable us to pull data from both systems?

Answer 1

Hi，@kkran

Welcome to Microsoft T-SQL Q&A Forum!

Maybe you have seen the official documentation, OPENQUERY will not accept variables, maybe you can use a temporary table, extract data from the table into local variables,
or use dynamic queries, which are also written in the sql series of books, please refer to Link.
For the link between the progress database and the current sql, please refer to this document.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can prepare the whole query (all statement) as a sql string (including OpenQuery) and then execute using sp_executeSQL procedure.

Answer 3

You can run four-part queries:

   SELECT * FROM SERVER.db.schema.tbl WHERE OrderDate > @date  

But there may be limitations that prevents this from working with Progress.

EXEC AT supports parameters:

   EXEC('SELECT * FROM Orders WHERE OrderDate > ?', [@](/users/na/?userId=19179bf8-7ffe-0003-0000-000000000000)) AT PROGRESS  

With OPENQUERY you need to build a query string dynamically. Which certainly is not any fun.