July 22, 2008 at 7:51 am
I have a job on SQL 2000.
First step "DCDBtransfer" is doing a few writes to
Import_Log table:
insert [192.168.81.56].vsImport.dbo.Import_log(message,[Table],[time])
values('Niad_Transfer_From_DCDB procedure started','Custom-Entry',getdate())
....
insert [192.168.81.56].vsImport.dbo.Import_log(message,[Table],[time])
values('Niad_Transfer_From_DCDB procedure finished','Dundee-Custom-Entry',getdate())
[192.168.81.56] is a linked server.
Second Job Step runs SSIS.
Third Job Step executes DTS package that executes
procedure "IMPORT_NIAD_DELTA_LOAD".
CREATE TABLE [dbo].[Import_log] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[message] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Table] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryKey] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ErrorCode] [int] NULL ,
[Line] [int] NULL ,
[status] [int] NULL ,
[time] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Import_log] ADD
CONSTRAINT [PK_Import_log] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
So in the Import_Log table I'm expecting to see
first entries from Step 1,2 and then 3.
If I order records by "id" column the sequence of actions is correct
but "time" values are not in sync. See below:
2008-07-22 06:00:13.0271617Truncate_Tables executed
2008-07-22 06:00:27.8231618NIAD_ACCOUNTS,NIAD_BRANCH_PERMISSIONS,NIAD_BRANCHES populated
2008-07-22 06:00:32.8871619NIAD_COUNTRIES,NIAD_CLIENTS,NIAD_RESIDENCES populated
2008-07-22 06:00:37.3401620NIAD_RR_CODES,NIAD_USER_RR_CODES,NIAD_USERS populated
2008-07-22 06:00:37.3701621Niad_Transfer_From_DCDB procedure finished
2008-07-22 05:58:55.4031622Niad.dtsx started
2008-07-22 05:58:56.1071623Niad.dtsx finished
2008-07-22 05:58:58.6701624NIAD_IMPORT_DELTA_LOAD...START
2008-07-22 05:58:58.6701625Countries...Start
2008-07-22 05:58:59.1701626Countries...End
For example "Truncate_Tables executed" action is the first Job action.
"NIAD_IMPORT_DELTA_LOAD...START" is the 3rd or the 4th action.
Then how come the Import_Log.time for "Truncate_Tables executed" is
2008-07-22 06:00:13
and Import_Log.time for "NIAD_IMPORT_DELTA_LOAD...START" is
05:58:58.670
It doesn't make sense.
The job is scheduled to start at 6:00 AM
but Import_Log table is sitting on another server and the time might not be in sync on two servers.
July 22, 2008 at 8:10 am
Guys,
Time is not in sync on two servers.
That's why.
We should not use code like
"...INSERT Import_Log(time) VALUES(getdate())..."
because we're running job steps on different servers.
Import_Log should have:
( [time] [datetime] DEFAULT getdate() NOT NULL)
Then all the time reported is a local system time always.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply