MAS 200 uses the Pervasive database system, and pulling data from it can be a bit difficult.
Belinda Zenk of Argenta Sytems has contributed the following notes on how to use SQL Server to create a linked server to the MAS 200 database. This means that Inaport can read from the linked tables in SQL Server, which removes many issues with the specialised SQL that the Providex ODBC driver requires.
If you are linking directly the MAS 200 using the Proxidex ODBC driver, see the seperate post on SQL.
MAS 200 Linked Server
Create Linked Server:
EXEC sp_addlinkedserver
@server='MAS', -- Name for Link Server
@provider='MSDASQL',
@srvproduct = 'ProvideX',
@datasrc='Best Dynalink' -- Name of ODBC DSN
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'MAS',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'XXXXXX', --MAS 200 Username
@rmtpassword='XXXXXX' --MAS 200 Password
Go
Drop Linked Server:
EXEC sp_dropserver MAS, droplogins
GO
Example Query1: Basic
SELECT * FROM OPENQUERY([MAS], 'SELECT * FROM AR_CustomerSalesHistory')
Example Query2: Use SQL syntax
Select ARDivisionNo, CustomerNo, sum(DollarsSold) as LYTD
From(
SELECT * FROM OPENQUERY([MAS], 'SELECT * FROM AR_CustomerSalesHistory')
) as AR
where FiscalYear = cast(year(getdate())-1 as varchar(4))
and FiscalPeriod < right('00' + cast(month(getdate())-1 as varchar(2)),2)
Group by ARDivisionNo, CustomerNo
GO
Note: Cannot Query ‘directly' ie this does not work:
Select ARDivisionNo, CustomerNo, sum(DollarsSold) as LYTD
From MAS...AR_CustomerSalesHistory
where FiscalYear = cast(year(getdate())-1 as varchar(4))
and FiscalPeriod < right('00' + cast(month(getdate())-1 as varchar(2)),2)
Group by ARDivisionNo, CustomerNo