We help IT Professionals succeed at work.

SQL 2008 operand type clash: smalint is imcompatiable with date

hi all,

we have SQL replication running and on our replicated database users are getting errors in our app that related to SQL

so i went onto the sql database at that site that is replicated and started diagnosis

this command
Update Clients Set ContactNumberSecondary ='123' where ID = 13031

returns
Msg 206, Level 16, State 2, Procedure sp_MSsync_upd_Clients_1, Line 0
Operand type clash: smallint is incompatible with date
The statement has been terminated.

this command runs fine
select * from Clients where  ID = 13031

ive searched and searched and have come up trumps, ive checked the table for datatypes and everything is fine, ive seen errors about column layout in the two tables, it matches

i run
SELECT table_name,ordinal_position,column_name,data_type,  is_nullable,character_maximum_length FROM
information_schema.COLUMNS WHERE table_name = 'Clients'
ORDER BY ordinal_position

on both tables and the spreadsheet attached shows both tables match completley

anyone know any other ways to troubleshoot and solve this?

Thanks

tblLayout.xls
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
please check the triggers on the table you run the update against ...
you must have some issue there, which you don't have in the actual UPDATE as such.
did you modify the table's design after you implemented the replication?
we reinitiatlized the snapshot and its all working now!

Author

Commented:
reinitilization works!