How to give a good problem report

If you call SYSPRO's support department, we will try to fix your problem as quickly as possible, of course, However, you can make this process much more efficient if you get us all the information we need, up front, so  here's a guide:

1. Always provide a screen shot!
   Make the screen shot FULL-SCREEN, because there are other clues on the screen that are really helpful, including the TIME and DATE (bottom right), the SYSPRO Company, the SYSPRO Port number, …
2. Draw a red box around the problem(s).
   Just because it’s obvious to you doesn’t mean it’s obvious to us. We are not mind readers.
   (Greenshot is free software, is easy to use, and takes good screen shots.)
3. State what values or behaviour was wrong.
4. State what values or behaviour you expected.
5. List the steps necessary to reproduce the problem.
6. State how often the problem happens:
1. Every single time.
2. Only in particular circumstances.
   Also state what those circumstances are.
3. Intermittently – not sure what circumstances trigger it.
4. Just once so far.
7. State all of the following:
1. What SYSPRO login were you using?
2. What company were you using?
3. What role were you using?
4. What SYSPRO version and port?
5. What program version (press Shift + F7 while in the program)?

1. Tell us about all error messages you saw before the problem appeared.
2. Tell us about anything else that may have influenced the issue.
3. Tell us what you have tried or done about the problem.

If you provide the above information, you’ll save lots of time!

The more detail, the better.

Remember the Two Golden Rules of Problem Reports:

1. Provide a screen shot.
2. Provide all the details.

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\*).