Invalid character value for cast specification error

[jschaddock]

Hello

I am running SQL server 7 and have replication running between two servers. The distribution agent has recently fallen over with the error

Invalid character value for cast specification

From looking on the web I know this is something to do with inserting records into a table (the last command is {CALL sp_MSins_unmatched}). So far the only solution I can see is re-establishing replication with a snapshot. Although this isn't a problem I don't really want to do it, does anyone have a solution as to how to fix this problem without running a snapshot?

Thanks

JC

 

[SQLBill]

A snapshot probably won't work either.

Here's what the error means.

You have data that will not convert (cast) to the datatype of the target type.

For example:

Current database has a column of varchar that you are mapping to an integer column in the replication database.

CASTing or CONVERTing the VARCHAR to INT will work ONLY if there are numbers only in the VARCHAR column. If you have even one row with a non-INT character, the replication will fail with the error you got.

So, check your script for any CAST or CONVERT being done. Check the original columns to make sure that the data in it/them can actually be CAST or CONVERTed to the new column datatype.

-SQLBill

 

[jschaddock]

Thanks for the reply but this doesn't really help. Both the replicated table and the table being replicated to are exactly the same, the subscriber was originally set up using a snapshot hence the table was scripted/created by the snapshot agent.
It looks like the agent is having a problem moving a null value and internally is trying to CAST this before putting it into the subscriber table. Unfortunately there doesn't seem to be any way of stopping it doing this and from what I have found on the web it appears to be a bug that occured in version 6.5 but was (apparently but obviously not) removed from version 7, it also appears as a sybase error in a lot of places so maybe this something that Microsoft inherited when they split from sybase and made SQL server. Running a snapshot appears to have solved the problem for now (thankfully the database was fairly small) but it's not a great situation for Microsoft to put us in, wondering if replication will fail and be unrecoverable without snapshotting.

Thanks anyway

JC