I am having issue accessing any MAS 200 tables via SQL SERVER dblink. MAS 200 client is installed on the same server.
I set up an ODBC on the same SQL Server that is pointing to SAGE MAS 200 database. The ODBC works because I can successfully read the tables via Access.
When I try to set up the dblink on SQL SERVER to MAS 200 it is successful. This is the code I've used:
@server = 'lnkMAS200TEST',
@provider = 'MSDASQL',
@srvproduct='MAS 90 4.0 ODBC Driver',
@useself=N'true', --tried either true or false same result
@rmtuser= --my MAS 200 Username
@rmtpassword= --my MAS 200 Password
exec sp_tables_ex 'lnkMAS200Test' --> this works, successfully returns all tables.
This instead is failing (AP_Division is one of the table returned by sp above):
select * from OPENQUERY(lnkMAS200Test, 'SELECT * FROM AP_Division')
This is the error:
OLE DB provider "MSDASQL" for linked server "lnkMAS200Test" returned message "[ProvideX][ODBC Driver][FILEIO]Table is not accessible".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT * FROM AP_Division" for execution against OLE DB provider "MSDASQL" for linked server "lnkMAS200Test".
I appreciate any help on this one, since I don't know what else to try. I read it could be a permission issue but I tried to log in on the SQL Server into MAS 200 (it is installed) via either the account specified above as security or the sql server service account, both works.