Stored Procedures

A stored procedure is a collection of SQL statements residing on the server, stored as text in a table in the database. Stored procedures provide an efficient environment for Database Connectors because, once they are executed on the server, they don't need to be parsed and optimized each time they are executed. (However, if the server goes down, the stored procedure will be parsed and optimized again the first time the procedure is called after the database restarts.)

If you run a set of stored procedures for the database where data is manipulated (your production database), this database must be the setting for A_MSSQL_LOCK_DB. If you run the stored procedures against both the lock database and the production database, the lock database can be the setting for A_MSSQL_LOCK_DB. Note that if you run one version of stored procedures against the lock database and another version against the production database, the stored procedures in the lock database override those in the production database. Therefore, we recommend that you always update your stored procedures with each new installation of Database Connector for MSSQL, so that these procedures are consistent when you run them against the tables in your database.

Note: If you are upgrading from an earlier version of Database Connectors, be sure to install the new stored procedures. Stored procedures are always upgraded in such a way that they will be compatible with older versions of the product, so installing new stored procedures over old ones does not affect your ability to run with an older version of the interface software.