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.