IDOL Category Component 24.2

# Set up Microsoft SQL Server to Store Tracking Information

This section describes how to use Microsoft SQL Server as your SQL back end.

## Configuration Example for Microsoft SQL Server

For the Microsoft SQL Server back end, you must set the `ConnectionString` configuration parameter in the `[DocumentTracking]` configuration section. This parameter must contain a valid connection string, as understood by SQL Server and your ODBC driver manager.

OpenText recommends that you use a DSN if you are running IDOL on a Windows operating system. This option lets the operating system save your settings. If you use Windows integrated authentication, use a connection string of the following form:

    ConnectionString=DSN=dt;database=odbc_test_db

If you use SQL Authentication [user+password], add `TRUSTED_CONNECTION=yes;`. For example:

    ConnectionString=DSN=dt;TRUSTED_CONNECTION=yes;database=odbc_test_db

NOTE: You can set the database that you want to connect to in the DSN configuration GUI, or Microsoft SQL server. However, for SQL Server 2005, SQL Server 2008, and SQL Server 2012, OpenText recommends that you explicitly specify it in the connection string connection options.

You can also use a DSN on UNIX operating systems, but you might find configuration easier if you include all parameters in the `ConnectionString` parameter and omit the DSNs.

## Troubleshoot Connection and Authentication Problems

The following table describes some common connection and authentication problems that you can identify in the SQL Management Studio, on the server side.

Issue | Suggestion
--- | ---
SQL Server is not configured to allow SQL Authentication. | In Server Properties, review the settings on the Security tab.
SQL Server is not configured to allow remote connections. | In Server Properties, review the settings on the Connections tab.
SQL Server is not mapping the user to the correct permissions for your database. | In the properties for the user, review the settings on the User Mapping tab.

For the client, check the following in the DSN configuration GUI:

- The driver must be `SQL Server Native Client 10.0`.

- The DSN must have Use ANSI quoted identifiers, Use ANSI nulls, paddings, warnings, and Perform translation for character data selected.

## Initialization Commands

You can run the following commands from an SQL command-line interface, or from the GUI in Microsoft SQL Server Management Studio.

Copy

    CREATE TABLE type(
       typeid integer identity PRIMARY KEY,
       type varchar(64) NOT NULL UNIQUE,
       is_error smallint,
       is_terminal smallint
    );
    SET IDENTITY_INSERT type ON
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES(0,'Unknown',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (10,'Committed',0,1);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (20,'Deleted',0,1);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (30,'Indexed',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (40,'Received',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (50,'Updated',0,1);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (-10,'Warning',1,0)
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (-20,'Error',1,1);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (-30,'Rejected',1,1);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (90,'Added',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (100,'Delete received.',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (110,'Update received.',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (120,'Non-importing add received.',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (130,'Import:Queue',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (140,'Import:Importing',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (150,'Import:Pre',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (160,'Import:Post',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (170,'Import:Finished',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (180,'Import:Cancel',0,1);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (190,'Import:Extracting metadata',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (200,'Import:Extracting metadata finished',0,0);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (210,'Import:ExtractMetaAbort',1,1);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (220,'Import:Abort',1,1);
    INSERT INTO type(typeid, type, is_error, is_terminal) VALUES (230,'Replaced',0,0);

    CREATE TABLE source(
       sourceid integer identity PRIMARY KEY,
       source varchar(128) NOT NULL UNIQUE
    );

    CREATE TABLE event(
       eventid integer identity PRIMARY KEY,
       docuid varchar(128) NOT NULL,
       typeid int NOT NULL,
       sourceid int NOT NULL,
       timestamp bigint NOT NULL
    );

    CREATE TABLE metadata(
       metadataid integer identity PRIMARY KEY,
       [key] varchar(32) NOT NULL,
       value varchar(1024) NOT NULL
    );

    CREATE TABLE event_metadata(
      eventid int NOT NULL,
      metadataid int NOT NULL,
    );

    CREATE TABLE docuid_reference(
       docuid varchar(128) NOT NULL,
       ref varchar(900) NOT NULL,
    );

    CREATE TABLE doctrack_schema_version(
       [key] varchar(64) NOT NULL,
       value varchar(128) NOT NULL
    );

    INSERT INTO doctrack_schema_version([key],value) VALUES ('major_version', '1');
    INSERT INTO doctrack_schema_version([key],value) VALUES ('minor_version','1');
    INSERT INTO doctrack_schema_version([key],value) VALUES ('IDOL_version','10.9');

Copyright 2024 Open Text
Version 24.2 | Last updated March 2024

Send documentation feedback

Close

Send documentation feedback

To send feedback using your default email client, open an email window.

Otherwise, send your feedback and the information below to MFI-swpdl.idoldocsfeedback@opentext.com.

We appreciate your feedback!

Product: IDOL IDOL Category Component 24.2

Topic Title: Set up Microsoft SQL Server to Store Tracking Information