Bookshelf Home | Contents | Index | Search | PDF |
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.
Bookshelf Home | Contents | Index | Search | PDF |
Siebel Analytics Server Administration Guide Published: 23 June 2003 |