Sage 100

Has anyone done a linked server with SQL on a different box than Sage 100? Did you have speed issues?

Yes - We have an application server and SQL on a dedicated SQL server at a couple of clients. Have not run into performance issues. We did run into issues related to permissions that had to be worked out so that all users had correct access to all functions.

We're doing this and haven't resolved issue with Lot distribution window taking 3-4 seconds to open and printing of customized invoices randomly being slow.

Are there some weird joins in the Crystal form? If so, you might try writing a view in SQL and eliminate the parsing of the joins in Crystal.

It is slow just right-clicking on the view that points to the linked server and ""Select top 1000 rows"" in this instance. I had not run into this before but had always had SQL on the same server as MAS.

I've done this and saw slowness on history files. I had tune my OPENQUERY SELECT by doing an ORDER BY on the key fields. Then speed was much much better. Apparently it wasn't taking advantage of KPRIMARY index before that. In my case of AR_InvoiceHistoryHeader it was ORDER BY InvoiceNo. I could've added HeaderSeqNo but it was already fast at the that point and dog slow before. You can also ORDER BY other indexes on the table for speed.

Even cancelling a query take several minutes to stop...

@AlnoorCassim thanks for the suggestion but this goes deeper than that. I have tried it with both std and cs ODBC. A Crystal Report listing the APVendor table from the Sage 100 ODBC pops up immediately, but the linked server query is still running at 7 minutes.

Just tested the linked server we recently set up. It takes 1:09 minutes/seconds to return all 557,051 AR Invoice History Header records. When I added an ORDER BY InvoicesNo, it took :52 seconds.

Oh, forgot to mention that Sage 100 Advanced is not running on the SQL server box.