views:

630

answers:

1

I am running into an error I am having trouble figuring out.

I have 2 tables and I'm trying to copy data from one to the other (simplified view):

MyTable
-------
ID varchar(11) do not allow nulls
Field01 numeric(6,0) allow nulls

MyTable_Temp
------------
ID varchar(11) do not allow nulls
Field01 numeric(6,0) allow nulls

My query looks like this:

DELETE FROM dbo.MyTable
INSERT INTO dbo.MyTable([ID],[Field01])
SELECT ID, Field01 FROM [dbo].MyTable_Temp WITH (NOLOCK)

However when I run my query it throws this error:

Msg 242, Level 16, State 3, Procedure TRG_MyTable, Line 6 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

If I comment out the Field01 part of the query it runs fine. How can a numeric field throw a datetime error?

+1  A: 

It looks to me like you've got some kind of trigger on the destination table that's firing (TRG_MyTable is a giveaway) It's probably doing something like inserting a timestamped record into an audit table somewhere and is getting confused.

Ken Keenan
I'm not too familiar with triggers. Where can I find these? When I look in the triggers section there are none and when I script out the database it doesn't seem to script out any triggers.
metanaito
Ok.. never mind, I'm stupid. It is under the table triggers not database triggers...
metanaito