Siebel Analytics Server Administration Guide > Sample Scripts and Load Procedures for Usage Tracking Data >

Extracting to SQL Server

Before extracting usage tracking log file data, a table needs to be created to store the data. The following is a sample command script to create the table S_NQ_ACCT for SQL Server 7 (SAACCT.MSSQL7.sql).

NOTE:  StartTime and EndTime are defined as CHAR(19) due to SQL Server limitations with timestamps.

drop table S_NQ_ACCT
create table S_NQ_ACCT
(
   UserName          varchar(128),
   RepositoryName    varchar(128),
   SubjectAreaName   varchar(128),
   NodeId            varchar(15),
   StartTimestamp    char(19),
   StartDate         char(10),
   StartHourMin      char(5),
   EndTimestamp      char(19),
   EndDate           char(10),
   EndHourMin        char(5),
   QueryText         varchar(1024),
   SuccessFlag       integer,
   RowCnt            integer default 0,
   TotalTimeInSecs   integer default 0,
   CompileTimeInSecs integer default 0,
   NumDBQueries      integer default 0,
   CumDBTimeInSecs   integer default 0,
   CumDBRows         integer default 0
);

The bulk copy utility (bcp) is used to load the usage tracking log files into the SQL Server database table. An example of the command used to invoke bcp follows (where /U is the database user ID, /P is the database password, /S is the name of the SQL Server machine, and /e is the name of the error output file):

bcp northwind.dbo.S_NQ_ACCT in D:\TEMP\NQAcct.20010214.071500.log /e D:\TEMP\NQAcct.err /c/t;/r\n/Sserver1 /Usa /P

NOTE:  Loaders may issue errors if the length of a string in the data file exceeds the maximum width of the string's corresponding column.


 Siebel Analytics Server Administration Guide 
 Published: 23 June 2003