Sage MAS 90 and 200 Extended Enterprise Suite

[necouchman]

I've seen a few other posts around the forum about this, but none of them seem to address my particular issue.  I've been successful in setting up a Linked Server object to point to my MAS90 data via the MAS 90 4.0 ODBC driver.  The problem I'm running into is that the linked resource behaves differently based on where you SQL Server Client connection originates.  I'm still trying to track down the exact differences, but, in particular, I need to be able to access our company's timecard data, via the TC tables, remotely via a SQL Server connection.  When I launch the SQL Client on the system that's running the Linked Server instance, and run the following query:

exec sp_tables_ex 'MASDATA'

I see 746 rows, including all of the TC* rows.  However, if I launch it on a remote machine that talks to that SQL server and run that same query, I see 883 rows, and the TC* rows are not included.  Anyone have any suggestions on what's going on, and why the MAS90 ODBC client or SQL Server would behave differently based on the connection origination point?

Thanks,

Nick Couchman

[necouchman]

An update on this...I managed to get rid of the first error by turning off remote collation.  However, I'm running into another error, now.  Again, on the server where SQL Server is installed, everything works fine.  However, on remote servers, I cannot access any of the tables.  If I try a query like this:

SELECT COUNT(*) FROM MASDATA...<TABLE>

I get the following:

Msg 7314, Level 16, State 1, Server SQLlinked\LINKS, Line 1
The OLE DB provider "MSDASQL" for linked server "MASDATA" does not contain the table "<TABLE>". The table either does not exist or the current user does not have permissions on that table.

If I try a query like this:

SELECT COUNT(*) FROM OPENQUERY(MASDATA,'SELECT * FROM <TABLE>')

I get this error:

Msg 7412, Level 0, State 2, Server SQLlinked\LINKS, Line 1
OLE DB provider "MSDASQL" for linked server "MASDATA" returned message "[ProvideX][ODBC Driver][FILEIO]Table is not accessible".
Msg 7321, Level 16, State 2, Server SQLlinked\LINKS, Line 1
An error occurred while preparing the query "SELECT * FROM <TABLE>" for execution against OLE DB provider "MSDASQL" for linked server "MASDATA".

I've enabled debugging on the ODBC connection, and, if I look at the log file, I see entries like this:

GetAccessibleTables:<table=TABLE> Open catalog(s) \\path\to\mas90 , available 0 table(s)

In all of the output above, TABLE is a table name that actually exists that I should be able to query (and can, on the local machine), and \\path\to\mas90 is a UNC path to the MAS90 share that does is exist and is accessible.  I'm also running SQL Server as a domain user, and not a local system account, so I would think this would take care of any issues with security on the UNC share.

Any hints?

Thanks - Nick

[dmilner]

Any luck with this? We're having the same problem.

[dongrubor]

Make sure that all of the SQL services on the server are set to login using a domain administrator account.  We ran into this problem in the past, and found out that the SQL services were logging in with an account that only had privileges on the SQL server and nowhere else. 

Hope  that helps.

[VanMan]

I don't know if this is still a problem for you.  The first thing I thought of was ODBC security associated with the TC tables in ODBC security.  Without proper security you can see the rows but not the data.  Make sure the MAS login being used from the SQL server linked table has ODBC rights to the TC Data.  Good Luck!