Stored SQL Procedures

The following stored procedures are supported for maintenance of the BarTender System Database.

SpDeleteOlderRecords

The SpDeleteOlderRecords procedure removes database records that are older than a specified amount of time. It takes the following parameters:

Constant

Description

BTMS

BarTender messages

BTLI

BarTender printed item images

BTLD

BarTender item database

BTPJ

BarTender print jobs

BTFB

BarTender document blobs

BTFM

BarTender documents

APMS

Application messages

PMPR

Printer Maestro printer events

PMIN

Printer Maestro inventory events

SCEV

Security Center events

ALLR

All records

The following sample code deletes all records that are older than six months.

BarTender now supports maintenance of a remote BarTender Database. Maintenance can be locked so that only one BarTender System Service runs it at a time. As a result, the SpDeleteOlderRecords procedure was altered to include a "use lock" input parameter and a "lock host" output parameter. If "use lock" is nonzero, the stored procedure tries to lock the maintenance function. If the lock fails, the procedure returns -9 in the "result" output parameter and the name of the host computer that is currently holding the lock in the "lock host" output parameter. If the procedure succeeds, the "result" is 1.

If only one BarTender System Service is connected to a particular SQL Server Database, the "use lock" parameter can be zero, and no lock attempt is made (because it is not necessary).

DECLARE @timeThreshold int
DECLARE @timeUnits int
DECLARE @recordType nvarchar(1024)
DECLARE @useLock int
DECLARE @lockHost nvarchar(1024)
DECLARE @result int
DECLARE @numRows int


SET @timeThreshold = 6
SET @timeUnits = 2
SET @recordType = N'ALLR'
SET @useLock = 1
SET @result = 0


EXEC [dbo].[SpDeleteOlderRecords] @timeThreshold, @timeUnits, @recordType, @useLock, @lockHost output, @result output

SpPurgeDatabase

The SpPurgeDatabase procedure removes all logged data that is contained in the BarTender System Database. In times of heavy logging, do not use this procedure, because this process might interfere with the logging operations that are being processed by the BarTender System Database. The SpPurgeDatabase procedure contains no additional parameters.

The SpPurgeDatabase procedure is non-reversible. Therefore, you cannot retrieve any records that were purged from the database. Use this procedure with caution.

The following sample code purges all records from the database.

EXEC dbo.SpPurgeDatabase