"beth" wrote:

> I'm having difficulty coming up with a method to store a datetime value when
> inserting new rows into a table. I have tried the following methods, on three
> different columns in the same table;
>
> 1) Default Value (getdate()) on column testInsertedDt
>
> 2) column InsertedDt/getdate() in INSERT statement itself;
>
> INSERT INTO db1..RG_EMPLOYEE
>        (column1, column2, ... InsertedDt)
> SELECT t.column1, t.column2, ... getdate()
>   FROM db2..RG_EMPLOYEE t
>  WHERE rg_id NOT IN (SELECT rg_id FROM db1..RG_EMPLOYEE)
>
> 3) Insert trigger;
>
> create trigger [trggrInsRG_EMPLOYEE]
>     on [dbo].[RG_EMPLOYEE]
>    for insert as
>        update RG_EMPLOYEE
>           set InsertedDtTrigger = getdate()
>          from RG_EMPLOYEE e
>               join inserted i on i.rg_id = e.rg_id
>
> All three methods produce garbage values, such as
>
> 32539-05-20 869:56:11.733
> 1900-01-01 31:04:08.107
> -21424-12-31 3124:26:24.493
>
> What am I doing wrong?
>
> thanks, beth

"Aaron Bertrand [SQL Server MVP]" wrote:

> I can't reproduce this problem.  What tool are you using to view these
> datetime values?

"Aaron Bertrand [SQL Server MVP]" wrote:

> You didn't answer this question:
>
> >> What tool are you using to view these datetime values?
>
> So allow me to elaborate.
>
> In another post, you say that you have valid datetime values when you use
> "SQL-Transact"... is this in Query Analyzer?  Is this not where you are
> using the non-sensical datetime values?  What query are you using to see
> those values, and what tool is being used to display them?

"beth" <b***@discussions.microsoft.com> wrote in message
news:3377CFDC-1CBE-4CEE-A950-ED6951D691D7@microsoft.com...
> I'm having difficulty coming up with a method to store a datetime value
when
> inserting new rows into a table. I have tried the following methods, on
three
> different columns in the same table;
>
> 1) Default Value (getdate()) on column testInsertedDt
>
> 2) column InsertedDt/getdate() in INSERT statement itself;
>
> INSERT INTO db1..RG_EMPLOYEE
>        (column1, column2, ... InsertedDt)
> SELECT t.column1, t.column2, ... getdate()
>   FROM db2..RG_EMPLOYEE t
>  WHERE rg_id NOT IN (SELECT rg_id FROM db1..RG_EMPLOYEE)
>
> 3) Insert trigger;
>
> create trigger [trggrInsRG_EMPLOYEE]
>     on [dbo].[RG_EMPLOYEE]
>    for insert as
>        update RG_EMPLOYEE
>           set InsertedDtTrigger = getdate()
>          from RG_EMPLOYEE e
>               join inserted i on i.rg_id = e.rg_id
>
> All three methods produce garbage values, such as
>
> 32539-05-20 869:56:11.733
> 1900-01-01 31:04:08.107
> -21424-12-31 3124:26:24.493
>
> What am I doing wrong?
>
> thanks, beth

"Brian Selzer" wrote:

> This is really strange.  I've never seen this kind of thing happen.  Sounds
> like a problem in your system.  Do you get garbage when you issue SELECT
> GETDATE() in Query Analyzer?  Have you run DBCC CHECKDB?  You might want to
> reindex the table DBCC REINDEX.  Can you try it on a different system?  If
> all else fails, you could reinstall SQL Server, but I'd call PSS first.
> ($275 per incident.)
>
> "beth" <b***@discussions.microsoft.com> wrote in message
> news:3377CFDC-1CBE-4CEE-A950-ED6951D691D7@microsoft.com...
> > I'm having difficulty coming up with a method to store a datetime value
> when
> > inserting new rows into a table. I have tried the following methods, on
> three
> > different columns in the same table;
> >
> > 1) Default Value (getdate()) on column testInsertedDt
> >
> > 2) column InsertedDt/getdate() in INSERT statement itself;
> >
> > INSERT INTO db1..RG_EMPLOYEE
> >        (column1, column2, ... InsertedDt)
> > SELECT t.column1, t.column2, ... getdate()
> >   FROM db2..RG_EMPLOYEE t
> >  WHERE rg_id NOT IN (SELECT rg_id FROM db1..RG_EMPLOYEE)
> >
> > 3) Insert trigger;
> >
> > create trigger [trggrInsRG_EMPLOYEE]
> >     on [dbo].[RG_EMPLOYEE]
> >    for insert as
> >        update RG_EMPLOYEE
> >           set InsertedDtTrigger = getdate()
> >          from RG_EMPLOYEE e
> >               join inserted i on i.rg_id = e.rg_id
> >
> > All three methods produce garbage values, such as
> >
> > 32539-05-20 869:56:11.733
> > 1900-01-01 31:04:08.107
> > -21424-12-31 3124:26:24.493
> >
> > What am I doing wrong?
> >
> > thanks, beth
>
>
>

"beth" <b***@discussions.microsoft.com> wrote in message
news:6528C80B-A4EF-4447-8D2E-7D25ABE41EB8@microsoft.com...
> I've have not tried DBCC CHECKDB.
>
> In SQL-Transact, I executed the following;
>
> insert into RG_EMPLOYEE
> (rg_id, InsertedDt)
> values ('testID', getdate())
>
> select rg_id, InsertedDt, testInsertedDt, InsertedDtTrigger
> from RG_EMPLOYEE
> where rg_id = 'testID'
>
> Results;
>
> rg_id  InsertedDt              testInsertedDt          InsertedDtTrigger
> testID 2005-07-08 09:57:05.180 2005-07-08 09:57:05.180 2005-07-08

>
> All valid datetime values.
>
> My colleague wondered if the problem is with the form of INSERT that we
are
> using in the stored procedure?
>
> thanks, beth
>
> "Brian Selzer" wrote:
>
> > This is really strange.  I've never seen this kind of thing happen.
Sounds
> > like a problem in your system.  Do you get garbage when you issue SELECT
> > GETDATE() in Query Analyzer?  Have you run DBCC CHECKDB?  You might want
to
> > reindex the table DBCC REINDEX.  Can you try it on a different system?
If
> > all else fails, you could reinstall SQL Server, but I'd call PSS first.
> > ($275 per incident.)
> >
> > "beth" <b***@discussions.microsoft.com> wrote in message
> > news:3377CFDC-1CBE-4CEE-A950-ED6951D691D7@microsoft.com...
> > > I'm having difficulty coming up with a method to store a datetime
value
> > when
> > > inserting new rows into a table. I have tried the following methods,
on
> > three
> > > different columns in the same table;
> > >
> > > 1) Default Value (getdate()) on column testInsertedDt
> > >
> > > 2) column InsertedDt/getdate() in INSERT statement itself;
> > >
> > > INSERT INTO db1..RG_EMPLOYEE
> > >        (column1, column2, ... InsertedDt)
> > > SELECT t.column1, t.column2, ... getdate()
> > >   FROM db2..RG_EMPLOYEE t
> > >  WHERE rg_id NOT IN (SELECT rg_id FROM db1..RG_EMPLOYEE)
> > >
> > > 3) Insert trigger;
> > >
> > > create trigger [trggrInsRG_EMPLOYEE]
> > >     on [dbo].[RG_EMPLOYEE]
> > >    for insert as
> > >        update RG_EMPLOYEE
> > >           set InsertedDtTrigger = getdate()
> > >          from RG_EMPLOYEE e
> > >               join inserted i on i.rg_id = e.rg_id
> > >
> > > All three methods produce garbage values, such as
> > >
> > > 32539-05-20 869:56:11.733
> > > 1900-01-01 31:04:08.107
> > > -21424-12-31 3124:26:24.493
> > >
> > > What am I doing wrong?
> > >
> > > thanks, beth
> >
> >
> >

>It can't be the form of the insert.  The bad date values shouldn't be
>allowed to be stored in the database.  I'm not sure, but you've said in one
>of the posts that a DTS package loads the data.  Have you tried creating a
>new DTS package to load the data from the other system in a separate real
>table without executing a stored procedure, so you can verify that the data
>is arriving in SQL Server correctly?  First verify that the data is arriving
>correctly.  There shouldn't be any way to load garbage into a datetime
>column, so there must be a couple of problems.  First, the existence of the
>garbage is a problem.  Second, how the garbage failed to cause an error is a
>problem, and Third, where the garbage came from is a problem.  I would
>attack the third problem first, make sure that the data transformations are
>correct.  Then I would try to find out who set what flag that bypassed the
>default type checking and constraint checking or what bug in SQL Server
>exists that allowed the garbage to be passed in and ultimately stored in the
>table.
>
>
>"beth" <b***@discussions.microsoft.com> wrote in message
>news:6528C80B-A4EF-4447-8D2E-7D25ABE41EB8@microsoft.com...
> 
>
>>I've have not tried DBCC CHECKDB.
>>
>>In SQL-Transact, I executed the following;
>>
>>insert into RG_EMPLOYEE
>>(rg_id, InsertedDt)
>>values ('testID', getdate())
>>
>>select rg_id, InsertedDt, testInsertedDt, InsertedDtTrigger
>>from RG_EMPLOYEE
>>where rg_id = 'testID'
>>
>>Results;
>>
>>rg_id  InsertedDt              testInsertedDt          InsertedDtTrigger
>>testID 2005-07-08 09:57:05.180 2005-07-08 09:57:05.180 2005-07-08
>>   
>>
>09:57:05.180
> 
>
>>All valid datetime values.
>>
>>My colleague wondered if the problem is with the form of INSERT that we
>>   
>>
>are
> 
>
>>using in the stored procedure?
>>
>>thanks, beth
>>
>>"Brian Selzer" wrote:
>>
>>   
>>
>>>This is really strange.  I've never seen this kind of thing happen.
>>>     
>>>
>Sounds
> 
>
>>>like a problem in your system.  Do you get garbage when you issue SELECT
>>>GETDATE() in Query Analyzer?  Have you run DBCC CHECKDB?  You might want
>>>     
>>>
>to
> 
>
>>>reindex the table DBCC REINDEX.  Can you try it on a different system?
>>>     
>>>
>If
> 
>
>>>all else fails, you could reinstall SQL Server, but I'd call PSS first.
>>>($275 per incident.)
>>>
>>>"beth" <b***@discussions.microsoft.com> wrote in message
>>>news:3377CFDC-1CBE-4CEE-A950-ED6951D691D7@microsoft.com...
>>>     
>>>
>>>>I'm having difficulty coming up with a method to store a datetime
>>>>       
>>>>
>value
> 
>
>>>when
>>>     
>>>
>>>>inserting new rows into a table. I have tried the following methods,
>>>>       
>>>>
>on
> 
>
>>>three
>>>     
>>>
>>>>different columns in the same table;
>>>>
>>>>1) Default Value (getdate()) on column testInsertedDt
>>>>
>>>>2) column InsertedDt/getdate() in INSERT statement itself;
>>>>
>>>>INSERT INTO db1..RG_EMPLOYEE
>>>>       (column1, column2, ... InsertedDt)
>>>>SELECT t.column1, t.column2, ... getdate()
>>>>  FROM db2..RG_EMPLOYEE t
>>>> WHERE rg_id NOT IN (SELECT rg_id FROM db1..RG_EMPLOYEE)
>>>>
>>>>3) Insert trigger;
>>>>
>>>>create trigger [trggrInsRG_EMPLOYEE]
>>>>    on [dbo].[RG_EMPLOYEE]
>>>>   for insert as
>>>>       update RG_EMPLOYEE
>>>>          set InsertedDtTrigger = getdate()
>>>>         from RG_EMPLOYEE e
>>>>              join inserted i on i.rg_id = e.rg_id
>>>>
>>>>All three methods produce garbage values, such as
>>>>
>>>>32539-05-20 869:56:11.733
>>>>1900-01-01 31:04:08.107
>>>>-21424-12-31 3124:26:24.493
>>>>
>>>>What am I doing wrong?
>>>>
>>>>thanks, beth
>>>>       
>>>>
>>>
>>>     
>>>
>
>
> 
>

"Steve Kass" wrote:

> Though Beth said the inserts and updates are from stored
> procedures run by a DTS process, perhaps it does have
> something to do with DTS.  I've seen something similar
> with invalid decimal data getting into a table through DTS
> because of a bum data provider, though from what Beth
> has said so far, I'm not sure where a data provider (for
> a non-SQL Server data source) would be involved.
>
> Beth, if there is a DTS process involved here, can you say
> anything more about the source of the data and how it is
> brought in?  Maybe something is going wrong in a different
> place than the obvious one.
>
> SK
>
> Brian Selzer wrote:
>
> >It can't be the form of the insert.  The bad date values shouldn't be
> >allowed to be stored in the database.  I'm not sure, but you've said in one
> >of the posts that a DTS package loads the data.  Have you tried creating a
> >new DTS package to load the data from the other system in a separate real
> >table without executing a stored procedure, so you can verify that the data
> >is arriving in SQL Server correctly?  First verify that the data is arriving
> >correctly.  There shouldn't be any way to load garbage into a datetime
> >column, so there must be a couple of problems.  First, the existence of the
> >garbage is a problem.  Second, how the garbage failed to cause an error is a
> >problem, and Third, where the garbage came from is a problem.  I would
> >attack the third problem first, make sure that the data transformations are
> >correct.  Then I would try to find out who set what flag that bypassed the
> >default type checking and constraint checking or what bug in SQL Server
> >exists that allowed the garbage to be passed in and ultimately stored in the
> >table.

"beth" <b***@discussions.microsoft.com> wrote in message
news:5FC9AB73-206C-4771-823A-EBD0315CFA41@microsoft.com...
> Steve and Brian,
>
> The DTS package steps are described, briefly, below. I think, based what
> you've both suggested, that I will create a new package that will perform
> only steps 1, 2 and 4. The rest I will execute in additional steps in the
SQL
> job. If DTS is the problem, perhaps that will solve the problem.
>
> I appreciate your help and suggestions. I am not a DBA, just a programmer
> trying to figure out why something that ought to be easy is proving to be
so
> hard.
>
> thanks, beth
>
> DTS Package
>
> 1 - Copy SQL Server Objects Task
>
>   Copies tables 3 tables from remote server to local
>   server with the following properties selected;
>
>   Create Destination Objects
>     Drop destination objects first
>     Include all dependent objects
>   Copy data
>     Replace existing data
>
> 2 - Execute SQL Task
>
>   drops/creates a local table
>
> 3 - Execute SQL Task
>
>   truncate table RG_EMPLOYEE ("master" table)
>
> 4 - Transform Data Task
>
>   Source is a remote server. Data is copied into
>   the table created in 2 using an SQL Query.
>
> 5 - Execute SQL Task
>
>   exec RG_EMPLOYEE_BUILD (loads the "master" table)
>
> 6 - Execute SQL Task (EMNTODS)
>
>   exec RG_EMPLOYEE_UPDATE (insert/update from "master" to "active" table)
>
> 7 - Execute SQL Task
>
>   drops tables created in 1 and 2
>
> "Steve Kass" wrote:
>
> > Though Beth said the inserts and updates are from stored
> > procedures run by a DTS process, perhaps it does have
> > something to do with DTS.  I've seen something similar
> > with invalid decimal data getting into a table through DTS
> > because of a bum data provider, though from what Beth
> > has said so far, I'm not sure where a data provider (for
> > a non-SQL Server data source) would be involved.
> >
> > Beth, if there is a DTS process involved here, can you say
> > anything more about the source of the data and how it is
> > brought in?  Maybe something is going wrong in a different
> > place than the obvious one.
> >
> > SK
> >
> > Brian Selzer wrote:
> >
> > >It can't be the form of the insert.  The bad date values shouldn't be
> > >allowed to be stored in the database.  I'm not sure, but you've said in
one
> > >of the posts that a DTS package loads the data.  Have you tried
creating a
> > >new DTS package to load the data from the other system in a separate
real
> > >table without executing a stored procedure, so you can verify that the
data
> > >is arriving in SQL Server correctly?  First verify that the data is
arriving
> > >correctly.  There shouldn't be any way to load garbage into a datetime
> > >column, so there must be a couple of problems.  First, the existence of
the
> > >garbage is a problem.  Second, how the garbage failed to cause an error
is a
> > >problem, and Third, where the garbage came from is a problem.  I would
> > >attack the third problem first, make sure that the data transformations
are
> > >correct.  Then I would try to find out who set what flag that bypassed
the
> > >default type checking and constraint checking or what bug in SQL Server
> > >exists that allowed the garbage to be passed in and ultimately stored
in the
> > >table.

"beth" <b***@discussions.microsoft.com> wrote in message
news:3377CFDC-1CBE-4CEE-A950-ED6951D691D7@microsoft.com...
> I'm having difficulty coming up with a method to store a datetime value
> when
> inserting new rows into a table. I have tried the following methods, on
> three
> different columns in the same table;
>
> 1) Default Value (getdate()) on column testInsertedDt
>
> 2) column InsertedDt/getdate() in INSERT statement itself;
>
> INSERT INTO db1..RG_EMPLOYEE
>       (column1, column2, ... InsertedDt)
> SELECT t.column1, t.column2, ... getdate()
>  FROM db2..RG_EMPLOYEE t
> WHERE rg_id NOT IN (SELECT rg_id FROM db1..RG_EMPLOYEE)
>
> 3) Insert trigger;
>
> create trigger [trggrInsRG_EMPLOYEE]
>    on [dbo].[RG_EMPLOYEE]
>   for insert as
>       update RG_EMPLOYEE
>          set InsertedDtTrigger = getdate()
>         from RG_EMPLOYEE e
>              join inserted i on i.rg_id = e.rg_id
>
> All three methods produce garbage values, such as
>
> 32539-05-20 869:56:11.733
> 1900-01-01 31:04:08.107
> -21424-12-31 3124:26:24.493
>
> What am I doing wrong?
>
> thanks, beth

"Keith Kratochvil" wrote:

> What is the layout of your table?
> From Query Analyzer issue the following commands:
>
> ------------
> USE DbNameGoesHere
> GO
> exec sp_help 'TableNameGoesHere'
> GO
> ------------
>
> When you query your table and observe the "bad" data what tool are you using
> to pull the data?
> Is the column that you are trying to insert into a datetime data type?
> What happens when you run this example within Query Analyzer?
>
> ------------
> create table #test (col datetime)
> insert into #test (col) values (getdate())
> select * from #test
> go
> select getdate()
> ------------
>
> --
> Keith
>
>
> "beth" <b***@discussions.microsoft.com> wrote in message
> news:3377CFDC-1CBE-4CEE-A950-ED6951D691D7@microsoft.com...
> > I'm having difficulty coming up with a method to store a datetime value
> > when
> > inserting new rows into a table. I have tried the following methods, on
> > three
> > different columns in the same table;

"beth" <b***@discussions.microsoft.com> wrote in message
news:3377CFDC-1CBE-4CEE-A950-ED6951D691D7@microsoft.com...
> I'm having difficulty coming up with a method to store a datetime value
when
> inserting new rows into a table. I have tried the following methods, on
three
> different columns in the same table;
>
> 1) Default Value (getdate()) on column testInsertedDt
>
> 2) column InsertedDt/getdate() in INSERT statement itself;
>
> INSERT INTO db1..RG_EMPLOYEE
>        (column1, column2, ... InsertedDt)
> SELECT t.column1, t.column2, ... getdate()
>   FROM db2..RG_EMPLOYEE t
>  WHERE rg_id NOT IN (SELECT rg_id FROM db1..RG_EMPLOYEE)
>
> 3) Insert trigger;
>
> create trigger [trggrInsRG_EMPLOYEE]
>     on [dbo].[RG_EMPLOYEE]
>    for insert as
>        update RG_EMPLOYEE
>           set InsertedDtTrigger = getdate()
>          from RG_EMPLOYEE e
>               join inserted i on i.rg_id = e.rg_id
>
> All three methods produce garbage values, such as
>
> 32539-05-20 869:56:11.733
> 1900-01-01 31:04:08.107
> -21424-12-31 3124:26:24.493
>
> What am I doing wrong?
>
> thanks, beth

"JT" wrote:

> When you eventually discover the root cause of this issue, then post back
> with the story for those of us who are eager to know.

>I wish I had your confidence; I'm out of ideas.
>
>thanks, beth
>
>"JT" wrote:
>
> 
>
>>When you eventually discover the root cause of this issue, then post back
>>with the story for those of us who are eager to know.
>>   
>>

"Steve Kass" wrote:

> Beth,
>
>   Can you post the binary contents of some of the bad values, along
> with a guess as to what the correct date-time should be?  If these
> bad values are bit-shifted or byte-reordered from what they should
> be, it might give a useful clue.  Also (now or later, if not convenient),
> can you post the values of the [bit] columns in the rows with the
> bad data, and also check that they are what was inserted?  Those bit
> rows would precede the datetime value on the data page.  The variable
> length varchar columns would come afterwards and are less likely
> to be an issue.
>
>   My guess is that you are seeing an obscure bug having to do
> with bit columns (or possibly code pages), or having to do with
> tables with a history of ALTER TABLE ALTER COLUMN or
> ALTER TABLE ADD operations.   Something seems to have
> gotten messed up with offsets in the INSERT .. VALUES
> statement.
>
> There are other things to check, like what DBCC PAGE
> shows, but the binary of the datetime columns is a good start.
> You can get that by selecting
>
> select ... cast(datetimecolumn as varbinary) as b_datetimecolumn...
>
>   Also, let us know if the database compatibility setting is
> less than 80, or if there is or ever was an unusual collation
> or code page in place (especially a double-byte character set).
>
> Steve Kass
> Drew University

>I wish I had your confidence; I'm out of ideas.
>
>thanks, beth
>
>"JT" wrote:
>
> 
>
>>When you eventually discover the root cause of this issue, then post back
>>with the story for those of us who are eager to know.
>>   
>>

"Steve Kass" wrote:

> Beth,
>
>   Also, I don't see where you've posted the version of SQL Server 2000.
> Hopefully you are running sp3, sp3a, or sp4, but can you post the result
> of
>
> SELECT @@version
>
> for us?  If it is not up-to-date on service packs, you could be
> seeing a long-ago-fixed bug.
>
> SK

>Steve,
>
>We're running;
>
>Microsoft SQL Server  2000 - 8.00.384 (Intel X86)
> May 23 2001 00:02:52
> Copyright (c) 1988-2000 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
>"Steve Kass" wrote:
>
> 
>
>>Beth,
>>
>>  Also, I don't see where you've posted the version of SQL Server 2000.
>>Hopefully you are running sp3, sp3a, or sp4, but can you post the result
>>of
>>
>>SELECT @@version
>>
>>for us?  If it is not up-to-date on service packs, you could be
>>seeing a long-ago-fixed bug.
>>
>>SK
>>   
>>

"Steve Kass" wrote:

> Beth,
>
>   As Anith notes, you are two service packs (and two and one-half years)
> behind in SQL Server updates.  Many bug and performance fixes, and
> important security updates (such as protection against the "SQL Slammer")
> have been provided since then, and my best advice is for you to apply
> Service Pack 4, or at least Service Pack 3a along with the security
> fix MS03-031, and see if the bug is still present.
>
> See http://support.microsoft.com/kb/821277, the article for the MS03-031
> patch, which also links to the "latest service pack for SQL Server" article.
>
> The binary data you provided looks like it belongs elsewhere than in
> a date time, because of its regularity (0x008100810081007B,
> 0x00A6009500840084), so this is looking somewhat more like
> a bug to me.
>
> SK
>
> beth wrote:
>
> >Steve,
> >
> >We're running;
> >
> >Microsoft SQL Server  2000 - 8.00.384 (Intel X86)
> > May 23 2001 00:02:52
> > Copyright (c) 1988-2000 Microsoft Corporation
> > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> >
> >"Steve Kass" wrote:
> >
> > 
> >
> >>Beth,
> >>
> >>  Also, I don't see where you've posted the version of SQL Server 2000.
> >>Hopefully you are running sp3, sp3a, or sp4, but can you post the result
> >>of
> >>
> >>SELECT @@version
> >>
> >>for us?  If it is not up-to-date on service packs, you could be
> >>seeing a long-ago-fixed bug.
> >>
> >>SK
> >>   
> >>
>

"beth" <b***@discussions.microsoft.com> wrote in message
news:040F8D55-9E26-4772-A931-280F99909A3C@microsoft.com...
> Steve, Anith,
>
> I'm confused. It looks like we are using SP4??
>
> Microsoft SQL Server  2000 - 8.00.384 (Intel X86)
> May 23 2001 00:02:52
> Copyright (c) 1988-2000 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
> "Steve Kass" wrote:
>
>> Beth,
>>
>>   As Anith notes, you are two service packs (and two and one-half years)
>> behind in SQL Server updates.  Many bug and performance fixes, and
>> important security updates (such as protection against the "SQL Slammer")
>> have been provided since then, and my best advice is for you to apply
>> Service Pack 4, or at least Service Pack 3a along with the security
>> fix MS03-031, and see if the bug is still present.
>>
>> See http://support.microsoft.com/kb/821277, the article for the MS03-031
>> patch, which also links to the "latest service pack for SQL Server"
>> article.
>>
>> The binary data you provided looks like it belongs elsewhere than in
>> a date time, because of its regularity (0x008100810081007B,
>> 0x00A6009500840084), so this is looking somewhat more like
>> a bug to me.
>>
>> SK
>>
>> beth wrote:
>>
>> >Steve,
>> >
>> >We're running;
>> >
>> >Microsoft SQL Server  2000 - 8.00.384 (Intel X86)
>> > May 23 2001 00:02:52
>> > Copyright (c) 1988-2000 Microsoft Corporation
>> > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>> >
>> >"Steve Kass" wrote:
>> >
>> >
>> >
>> >>Beth,
>> >>
>> >>  Also, I don't see where you've posted the version of SQL Server 2000.
>> >>Hopefully you are running sp3, sp3a, or sp4, but can you post the
>> >>result
>> >>of
>> >>
>> >>SELECT @@version
>> >>
>> >>for us?  If it is not up-to-date on service packs, you could be
>> >>seeing a long-ago-fixed bug.
>> >>
>> >>SK
>> >>
>> >>
>>

"Mike Epprecht (SQL MVP)" wrote:

> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
> = Windows 2000, Standard Edition, Service Pack 4
>
>
> Microsoft SQL Server  2000 - 8.00.384 (Intel X86)
>
> = SQL Server 2000, Standard Edition, Service Pack 1
>
> 8.00.2040 is the SP4 Hotfix for AWE
> 8.00.2039 is the SP4
> 8.00.0818 is the SP3/SP3a security rollup (MS03-031)
> 8.00.0760 is SP3/SP3a
> 8.00.0534 is SP2
> 8.00.0384 is SP1
> 8.00.0194 is SQL 2000 RTM
>
> Regards
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> IM: m***@epprecht.net
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
> "beth" <b***@discussions.microsoft.com> wrote in message
> news:040F8D55-9E26-4772-A931-280F99909A3C@microsoft.com...
> > Steve, Anith,
> >
> > I'm confused. It looks like we are using SP4??
> >
> > Microsoft SQL Server  2000 - 8.00.384 (Intel X86)
> > May 23 2001 00:02:52
> > Copyright (c) 1988-2000 Microsoft Corporation
> > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> >
> > "Steve Kass" wrote:
> >
> >> Beth,
> >>
> >>   As Anith notes, you are two service packs (and two and one-half years)
> >> behind in SQL Server updates.  Many bug and performance fixes, and
> >> important security updates (such as protection against the "SQL Slammer")
> >> have been provided since then, and my best advice is for you to apply
> >> Service Pack 4, or at least Service Pack 3a along with the security
> >> fix MS03-031, and see if the bug is still present.
> >>
> >> See http://support.microsoft.com/kb/821277, the article for the MS03-031
> >> patch, which also links to the "latest service pack for SQL Server"
> >> article.
> >>
> >> The binary data you provided looks like it belongs elsewhere than in
> >> a date time, because of its regularity (0x008100810081007B,
> >> 0x00A6009500840084), so this is looking somewhat more like
> >> a bug to me.
> >>
> >> SK
> >>
> >> beth wrote:
> >>
> >> >Steve,
> >> >
> >> >We're running;
> >> >
> >> >Microsoft SQL Server  2000 - 8.00.384 (Intel X86)
> >> > May 23 2001 00:02:52
> >> > Copyright (c) 1988-2000 Microsoft Corporation
> >> > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> >> >
> >> >"Steve Kass" wrote:
> >> >
> >> >
> >> >
> >> >>Beth,
> >> >>
> >> >>  Also, I don't see where you've posted the version of SQL Server 2000.
> >> >>Hopefully you are running sp3, sp3a, or sp4, but can you post the
> >> >>result
> >> >>of
> >> >>
> >> >>SELECT @@version
> >> >>
> >> >>for us?  If it is not up-to-date on service packs, you could be
> >> >>seeing a long-ago-fixed bug.
> >> >>
> >> >>SK
> >> >>
> >> >>
> >>
>
>
>

>Well, moving execution of the RG_EMPLOYEE_UPDATE procedure from inside the
>DTS package to a subsequent step in the SQL job did not fix the problem, so
>if there is a bug, I don't think it is specific to DTS.  I added some selects
>to the RG_EMPLOYEE_UPDATE procedure to try and help diagnose what is
>happening.  There has not been much activity yet, but there were two inserts
>yesterday;
>
>rg_id    InsertedDt              LastUpdateDt
>-------- ----------------------- -----------------------
>50094    2005-07-11 12:04:16.230 (null)
>26194    2005-07-11 12:04:16.230 (null)
>
>This morning, two rows were updated;
>
>                rg_id     InsertedDt                  LastUpdateDt          
>                              
>--------------- --------  --------------------------- -----------------------
>Update - before 50060     (null)                      (null)
>         after  50060     -23201-12-31 2609:47:20.960 2005-07-12 04:02:23.160
>Update - before 28813     (null)                      (null)
>         after  28813     28232-11-30 497:06:09.707   2005-07-12 04:02:35.143
>
>(note we did not bother pre-setting InsertedDt or LastUpdateDt when we added
>the two columns last week)
>
>Running the job again later this morning updated another row;
>
>                rg_id     InsertedDt                  LastUpdateDt
>--------------- --------  --------------------------- -----------------------
>Update - before 01907     (null)                      (null)
>         after  01907     1900-01-01 00:02:16.943     2005-07-12 10:54:19.207
>
>All this time I had been thinking InsertedDt was getting fouled up during an
>INSERT, but it's looking like UPDATE is the source of the problem.
>
>I sent a query to our Help Desk, asking why this SQL server version is so
>old; have not received an answer yet.
>
>beth
> 
>

"Steve Kass" wrote:

> Hi Beth,
>
>   I found one newsgroup report similar to what you describe:
>
> http://groups.google.co.uk/groups?q=%2229335-01-30+993%3A14%3A04.160%22
>
> (The date of that post is a week after the release of Service Pack 2.)
>
> There's a workaround in that thread, which is to reset the
> problem columns to their own values in each update statement.  In other
> words, wherever you have
>
> update T set
>   ...
>
> change it to
>
> update T set
>   ...,
>   InsertedDt = InsertedDt,
>   LastUpdateDt = LastUpdateDt
>
>
> Of course this shouldn't be required, but maybe it will help until
> you find out if an upgrade fixes the problem once and for all.
>
> SK

"Steve Kass" wrote:

> Hi Beth,
>
>   I found one newsgroup report similar to what you describe:
>
> http://groups.google.co.uk/groups?q=%2229335-01-30+993%3A14%3A04.160%22
>
> (The date of that post is a week after the release of Service Pack 2.)
>
> There's a workaround in that thread, which is to reset the
> problem columns to their own values in each update statement.  In other
> words, wherever you have
>
> update T set
>   ...
>
> change it to
>
> update T set
>   ...,
>   InsertedDt = InsertedDt,
>   LastUpdateDt = LastUpdateDt
>
>
> Of course this shouldn't be required, but maybe it will help until
> you find out if an upgrade fixes the problem once and for all.
>
> SK