How to call a Syspro Business Object from a SQL Trigger

Sometimes one of the worst options for customising Syspro, using a SQL Trigger, is the best option you've got. In that circumstance, how do you call a Syspro Business Object from within the SQL Trigger? Here are some ways:

Create a CLR Stored Procedure

Create a CLR Stored Procedure and call that from your SQL Trigger. In your CLR Stored Procedure, you can call the Business Object via one of Syspro's COM Objects, or via Syspro's web service or Syspro's WCF service. However, you must watch out for the 32/64-bit issue:

Watch out for the 32/64-bit issue

If you are using one of Syspro's COM objects, you may run into the problem of trying to run a 32-bit application in a 64-bit process, which can't be done (unless you want to hack around with the registry as described here - good luck). This problem arises because if your version of SQL Server is 64-bit, then you won't be able to access any 32-bit COM objects from it (such as Encore.dll or Syspro32.dll); you could use Syspro.dll (which is 64-bit), but at the time of writing, September 2014, it had't been released.

You could overcome the 32-64 bit disconnect by using Inter-Process Communication or some other communication method, but you may just find it easier to use another method.

Call a COM Object

You CAN call a COM object directly from a SQL Trigger using the extended stored procedures, sp_OACreate and sp_OAMethod (details here).
Then you could either call a web service, or call Encore.dll or Syspro.dll (being mindful of the 32/64-bit issue as mentioned above). Here's one example.
However, this is not a good solution due to the level of security you have to give to run those extended stored procedures.

Use Document Flow Manager

You could dump out files and feed them into Syspro's Document Flow Manager, a server process which monitors a directory for files and feeds them into Syspro via one of Syspro's e.Net interfaces.

Call a separate process via xp_cmdshell

The quickest and simplest method is: from your SQL Trigger, use the xp_cmdshell function, e.g.

EXEC master..xp_cmdshell @yourParameters

This has the same security issues as calling a COM Object, but it isolates your process from the SQL Server, and so this is probably the best method.

Best Practices for SQL Server for Syspro

These are recommended best practices for configuring SQL Server for use with Syspro.

Basic configuration

• The collation order must be Latin1_General_Bin for Syspro.
• Use Mixed Mode Authentication.
• Store your database on a DIFFERENT disk to your database's log file.
• Store your backups on a third disk.
• Use RAID 5 (recommended by Syspro) or RAID 1+0 for data protection.
• Give your database server plenty of memory. As a point of reference, 10 Gb is very nice for a 20 user site.
• Create at least one user for Syspro to access the database; don't use the system administrator user (sa) for Syspro!

Backup basics

• Copy your backups off your server, ideally daily
• Take a copy of your backup OFF SITE regularly
• Keep backups at several levels: yearly, monthly, weekly and daily,

Really bad backup practices to avoid at all costs

• Backing up to the same disk as the database - if the disk dies, you’ll lose all your data for ever!
• Not backing up at all!

SQL Log file settings

If using the FULL recovery model, the SQL log file should be set so that if it fills up, it will leave a few Gb unused on the file-system so that you don’t crash Windows.

If using the SIMPLE recovery model, the SQL Log file should have its maximum size set to a safe limit so that if it fills up to that limit, it WON’T fill up the hard disk. As a rough guide, that limit should be about 2 to 4 times the normal database size; anything beyond that would need special circumstances.

Other recommended log file settings:

• AUTOGROWTH - Yes
• AUTOSHRINK - NO; definitely not. Setting it to Yes is bad practice; the log file should ideally be a static size, large enough for each day’s transactions.
• Recovery Model - Full Recovery Model is recommended; SIMPLE Recovery Model is OK.

Anti-virus software on your SQL Server?

It sounds like a good thing, but it's the wrong tool for the job. You should be using a host intrusion detection system (H-IDS) such as Tripwire. See this post for details.
However, if you do have anti-virus software on your SQL and/or Syspro server, you should exempt these files from being scanned: your database files including log and backup files (*.mdf, *.ldf, *.bak), and all your Syspro settings, work and data files (C:\Syspro\Work\*, C:\Syspro\Data\*, C:\Syspro\Base\Settings\*).

How to configure SQL Server for SYSPRO

These instructions apply to SQL Server 2005 and later, including SQL Server 2008 R2, SQL Server 2012, and SQL Server Express versions.

Set Collation Order

The collation order for a SYSPRO database MUST be case-sensitive; the recommended collation order is Latin1_General-BIN. Normally you would set the database server's default collation to be this order, but you can set it at the individual database level by creating a new database and setting its collation order; you must do this BEFORE you get Syspro to populate the database with tables etc.

Set Mixed Authentication Mode

Your SQL Server should be set to SQL Server and Windows Authentication mode in your Server properties.

Database Usage

The Compatibility level of your database must be set to SQL Server 2005 or later; SQL Server 2000 is not supported by Syspro.

See this document for more details: SQL Server Tricks and Best Practices.pdf

Set Desktop Heap size on the server

If you have a large number of users, you should increase your desktop heap limit on the Syspro Application Server. See this post on the Syspro Forum for more details.