...
In SQL Server Management Studio (SSMS)
Paste and execute the query below to create the tracs_log database. You should use the copy button in the code snippet below.
| Code Block |
|---|
USE master GO CREATE DATABASE tracs_log GO use tracs_log GO DECLARE @sql1 VARCHAR(1000) SELECT @sql1='ALTER DATABASE '+quotename(db_name())+' SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE '+quotename(db_name())+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE '+quotename(db_name())+' SET read_committed_snapshot ON ALTER DATABASE '+quotename(db_name())+' SET MULTI_USER' exec(@sql1) |
...
Click the run distribution button.
...
Navigate to the C:\ProgramData\TraCS\Distribution\Templates folder
...
Make sure to give your agencies TraCS SQL user owner rights to the database.
Run the BTDBLog distribution file. (This adds state access levels.)
...
Remove the Distribution log. xml file
Navigate to C:\ProgramData\TraCS\Settings
Delete DistributionLog.xml
In the configuration manager, select the update tab.
Just like above in step 1, in SQL Server Management Studio paste and execute the query below to configure your tracs_log database. You should use the copy button in the code snippet below.
| Code Block | ||
|---|---|---|
| ||
Use tracs_log
CREATE TABLE Attachment (
EmailID NVARCHAR(100),
AttachmentBinary IMAGE,
AttachmentFilename NTEXT
);
CREATE TABLE Email (
EmailID NVARCHAR(100),
Status NVARCHAR(20),
EmailTo NTEXT,
Cc NTEXT,
Bcc NTEXT,
SMTPSetting NVARCHAR(100),
Subject NVARCHAR(255),
Body NTEXT,
SentDate SMALLDATETIME,
ResendCount NVARCHAR(5),
CreateDate SMALLDATETIME,
FailureReason NVARCHAR(255),
PRIMARY KEY(EmailID)
);
CREATE TABLE EmailStatus (
Status NVARCHAR(20),
Description NVARCHAR(100),
PRIMARY KEY(Status)
);
CREATE TABLE SysTransLog (
Source NVARCHAR(255),
Process NVARCHAR(255),
Action NVARCHAR(255),
MachineID NVARCHAR(255),
StartDateTime NVARCHAR(255)
);
CREATE TABLE TraCSLog (
LogID NVARCHAR(255),
ID NVARCHAR(255),
UserID NVARCHAR(255),
LocationID NVARCHAR(255),
MachineID NVARCHAR(255),
FormNumber NVARCHAR(255),
FormName NVARCHAR(255),
FormKey NVARCHAR(255),
ContactKey NVARCHAR(255),
ContactNumber NVARCHAR(255),
ContactType NVARCHAR(255),
LogDate SMALLDATETIME,
LogTime NVARCHAR(255),
ActionName NVARCHAR(255),
Description NVARCHAR(max),
ConnectionKey NVARCHAR(255),
LogTable NVARCHAR(255),
OldValue NVARCHAR(max),
NewValue NVARCHAR(max),
LogKey NVARCHAR(36),
EntryType NVARCHAR(30)
);
CREATE TABLE TransLog (
ID NVARCHAR(255),
LogDate SMALLDATETIME,
LogTime NVARCHAR(255),
ConnectionKey NVARCHAR(255),
LogTable NVARCHAR(255),
Description NVARCHAR(max),
FormNumber NVARCHAR(255),
FormName NVARCHAR(255),
FormKey NVARCHAR(255),
Success NVARCHAR(255),
BatchID NVARCHAR(255),
UserID NVARCHAR(255),
LocationID NVARCHAR(255),
MachineID NVARCHAR(255),
InstructionName NVARCHAR(255),
Type NVARCHAR(255),
LogType NVARCHAR(255),
FormFileName NVARCHAR(255),
ArchiveDate SMALLDATETIME,
SavedFileName NVARCHAR(255),
FormDate SMALLDATETIME,
ArchiveFlag BIT NOT NULL,
PreExpBackup NVARCHAR(255),
PostExpBackup NVARCHAR(255),
PreImportBackup NVARCHAR(255),
TransCommBackup NVARCHAR(255),
ActionName NVARCHAR(255)
);
CREATE TABLE ServiceLog (
ServiceName NVARCHAR(255),
Status NVARCHAR(255),
LastUpdate SMALLDATETIME,
ShowAlert BIT,
UpdateIntervalHours INT
);
CREATE TABLE ServiceLogMessages (
ServiceName NVARCHAR(255),
MessageType INT,
MessageName NVARCHAR(255),
Message NTEXT,
UpdateDate SMALLDATETIME,
Attachment IMAGE,
ExternalLink NVARCHAR(255),
Misc1 NVARCHAR(255),
Misc2 NTEXT,
ShowMessageAlert BIT,
TraCSLogID NTEXT
); |