Conversion failed when converting the nvarchar value '' to data type int

While this may sound like a beginner 101 problem I think it is a bit more complicated.

I have two instances of SQL server, one is a log-shipped read/only standby copy of the master database that is used for reporting purposes.

They are both 64-bit SQL 2005, SP3.

The LogShipped Instance is: 9.00.4035.00 (Standard Edition) The Original Instance is: 9.00.4035.00 (Enterprise Edition) in an Active / Passive Cluster.

Server collation is Latin1_General_CI_AI on both and they both run on Server 2003 64 bit.

I have a query that runs and executes fine on the master database server but it fa开发者_如何学编程ils on the standby / read-only copy with a conversion of nvarchar to int.

The code is identical and i've copied and pasted it from the main instance query window just to double check.

Is there a bug in SQL server somewhere? I can paste the query if needed (its a bog-standard select with some in-line tables)

Just don't understand why it works on the one yet the log-shipped copy fails.

Any pointers is much appreciated.

-- Edit

I have found the culprit.. the transaction log database contains invalid data that isn't in the primary database.. quite why they are out of sych I do not know yet as the transaction log shipping is still working and I have no errors in the job-history.

Just a few orphaned records that are invalid that are not in the primary db.. how odd

Are you sure the data is the same?

If you try

select convert(int, char(10))

it will probably get a similar error, so your query will fail if the value you are converting is char(10), which may not be be obvious when viewing the data.

check any trigger if that is updating any view(having int for the specific column) based on your table