TSQL OpenRowset to Pervasive

Question

We are trying to connect SQL Server to Sage 300 CRE via ODBC.
Our ODBC connection is working, but the select statement below doesn't work.

Can someone clarify the syntax is correct ?

Thank you,

SELECT a.* 
FROM OPENROWSET('MSDASQL','Driver=Timberline Data;
DSN=Timberline Test;
DBQ=
UID=x;
PWD=abc;
CODEPAGE=1252','SELECT 
                  *
            FROM [APM_MASTER__VENDOR]
            ') 
        AS a

We receive the following message:

Answer 1

It looks correct from the SQL Server side of things. When it comes to Sage... I'm not familiar with Sage, and you would have to inquire in a Sage forum.

Do you have a command-line application that is able to connect to Sage? If so, copy it to the SQL Server machine and run it from xp_cmdshell. That may work - or you may get a better error message.

Answer 2

Hi @vsslasd

Try adding the full schema, like this:

SELECT GroupName, Name, DepartmentID
FROM AdventureWorks2012.HumanR

If not work, please also see this blog: Resolving ODBC Driver schema, table, or column not found errors.

Hope it is helpful.

Best regards,

Cosmog Hong

---

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.