Sorry this is such a late follow-up, but I was googling around for
thoughts on how to speed up my linked server MAS90 connection, and saw
your question.
To set up a linked server from SQL Server 2000 to MAS90 using Enterprise
Manager:
1. In Enterprise Manager, open the Security tab on your server
2. Right click Linked Servers, click New Linked Server...
3. Type in a useful name to remember it by in the Linked Server: text
box at the top (I use MAS90 and MAS90_TST for production and testing,
respectively).
4. Under Server Type: select Other Data Source
5. Under Provider name: select Microsoft OLE DB Provider for ODBC
Drivers
6. Under Provider string: type "Driver={MAS 90 32-Bit ODBC Driver};
UID=<MAS90 USERNAME>; PWD=<MAS90 PASSWORD>; Directory=<BASE DIRECTORY OF
MAS90>; Company=<COMPANY CODE>; SERVER=NotTheServer" (without the
quotes, and of course with the proper values filled in)
MAS90 doesn't seem to like direct linked server access ("SELECT * FROM
MAS90.AR1_CustomerMaster"). Instead, you'll have to use the OPENQUERY or
OPENROWSET macros. I found the easiest solution to be creating views
that retrieve the data that can be more easily referenced. The SQL
statement (for the above example) would be "SELECT * FROM OPENQUERY(
MAS90, 'SELECT * FROM AR1_CustomerMaster') AR1".
MAS90 has its own SQL syntax that's sometimes cumbersome. If you're okay
with the performance hit of retrieving more records than you need, it's
easier to filter and join after the data's been retrieved.
http://pvx.com has the (rather poor) reference manual for its SQL
syntax.
Another side note is that joining data from linked queries against MAS90
can be an heavy performance hit. It is _incredibly_ faster to store
retrieved data in a temporary table (in memory (DECLARE @temp_table_name
TABLE) if under 10,000 rows, on the drive (CREATE TABLE
#temp_table_name) if over).
Something else to consider is that MAS90's ODBC connector has a few
show-stoppers. Under certain conditions (making too many requests at
once), it'll refuse to return a rowset and SQL Server will have a wait
resource lock that stays and keeps other connections from occurring. To
get around this in my multi-user (and multi-automated code data
retriever) environment, I've had to creating a queueing system for
requests. Another method is to serialize all requests using table locks,
but those can cause their own headaches.
My final solution for MAS90 was to set up queueing (an external DLL
library that all programs that need MAS90 data reference) on one
instance of SQL server that lets users and code make requests one at a
time. But not everything needs up-to-the-second data. At first, I
created a procedure that took a snapshot of all the tables needed
regularly, and ran it daily in the wee hours. This way, any data older
than a day was easily accessible as a regular table on SQL Server. I've
since replaced that with a service that runs all the time on a separate
instance of SQL Server, using the CHECKSUM() function and some outer
joins to detect changes to MAS90 data and propogate these to static
tables. To keep in line with the multi-request issue, only one table is
updated at a time.
The update frequency is weighted based on how often that table changes
and how often more recent data is needed from that table. For the most
commonly used data, it's never more than 5 minutes old. This snapshot
data set is being used for most tasks. Data about to be imported,
however, still uses the queued system so that code-assigned sales order
numbers, customer numbers, etc., are at a very low risk of colliding
with human-assigned numbers (it's a lot easier if you make your sequence
styles unique, so that human-generated numbers are how MAS90 normally
does them, and code-generated numbers all start with a "C" or something,
but other limitations can keep you from choosing that route... like
users). At the moment of import by a VI job, collisions are filtered
out (rather than doubling up two orders on one and overwriting data) and
have to get new unique identifiers assigned before the next import.
Well, this has been quite an essay of a post, but I wish I'd had
something like this to read when I first started out fighting MAS90, so
maybe some hapless monkey out there will get a better head start than I
did.
--Thomas
*** Sent via Developersdex
http://www.developersdex.com ***
