Odd "Replication" error
So I have been attempting to do some replication on my SQL Server 2008 Enterprise (64 bit) cluster.
Well, I was building a subscription on a 2008 Enterprise SQL Server on Windows 2008 Enterprise to be subscribed to by a 2008 Enterprise 64 bit SQL Server on Windows 2008 R2.
However the subscriber doesn't really enter this equasion.
I had built several large publications, but one really gave me an issue.
The error I got ( and could not find ANYWHERE on the 'net) was
Error messages:
Message: Data conversion failed
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
Now, there was more to that stack dump but the key was the Message: Data conversion Failed.
So I did what a programmer (by training) would do, and first I searched the error, and since I didn't see anything about it I narrowed the possibilites. I figured it was a table that was bombing when trying to build the BCP files because that is the process that was failing. So I did a little manual 'binary' search, split the tables in half and got the error. Split them again, didn't. Did the second half got it... after a while I got it down to one table.
Great. Something was corrupted in my table. But what? Then daily life at the office got in the way and I had to drop it. Today I did a DBCC CheckTable with a few options to be specific
WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, TABLOCK, DATA_PURITY
The data purity told me that an updatedate column was out of range... but the data it brought back was less than helpful. So I ran a couple of queries that did the trick.
SELECT ID,CreateDate FROM Prescriber
WHERE UpdateDate < '1/1/1753 12:00:00 AM' OR UpdateDate > '12/31/9999 11:59:59 PM'
and
SELECT ID FROM Prescriber WHERE
((DATEPART(ms,UpdateDate)+ (1000*DATEPART(s,UpdateDate)) + (1000*60*DATEPART(mi,UpdateDate)) + (1000*60*60*DATEPART(hh,UpdateDate)))/(1000*0.00333))
> 25919999
And I found about 830 rows that must have been BCP'ed in many moons ago that had bad dates. I nulled the dates out (as they aren't needed really) and bam. Everything works.
So, long story short, Conversion error points to bad data. And in retrospect my debugging worked out well, because it took 5 minutes to scan that one table, and there were several much larger tables in this database so a DBCC CheckDB is out of the question.
NOW
My more expireced DBA readers will say "Why aren't you doing CHCECKDB weekly.... yeah, I have a script that I'm still testing for this. I need to tweak it, but real life has prohibited that.
Well, I was building a subscription on a 2008 Enterprise SQL Server on Windows 2008 Enterprise to be subscribed to by a 2008 Enterprise 64 bit SQL Server on Windows 2008 R2.
However the subscriber doesn't really enter this equasion.
I had built several large publications, but one really gave me an issue.
The error I got ( and could not find ANYWHERE on the 'net) was
Error messages:
Message: Data conversion failed
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
Now, there was more to that stack dump but the key was the Message: Data conversion Failed.
So I did what a programmer (by training) would do, and first I searched the error, and since I didn't see anything about it I narrowed the possibilites. I figured it was a table that was bombing when trying to build the BCP files because that is the process that was failing. So I did a little manual 'binary' search, split the tables in half and got the error. Split them again, didn't. Did the second half got it... after a while I got it down to one table.
Great. Something was corrupted in my table. But what? Then daily life at the office got in the way and I had to drop it. Today I did a DBCC CheckTable with a few options to be specific
WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, TABLOCK, DATA_PURITY
The data purity told me that an updatedate column was out of range... but the data it brought back was less than helpful. So I ran a couple of queries that did the trick.
SELECT ID,CreateDate FROM Prescriber
WHERE UpdateDate < '1/1/1753 12:00:00 AM' OR UpdateDate > '12/31/9999 11:59:59 PM'
and
SELECT ID FROM Prescriber WHERE
((DATEPART(ms,UpdateDate)+ (1000*DATEPART(s,UpdateDate)) + (1000*60*DATEPART(mi,UpdateDate)) + (1000*60*60*DATEPART(hh,UpdateDate)))/(1000*0.00333))
> 25919999
And I found about 830 rows that must have been BCP'ed in many moons ago that had bad dates. I nulled the dates out (as they aren't needed really) and bam. Everything works.
So, long story short, Conversion error points to bad data. And in retrospect my debugging worked out well, because it took 5 minutes to scan that one table, and there were several much larger tables in this database so a DBCC CheckDB is out of the question.
NOW
My more expireced DBA readers will say "Why aren't you doing CHCECKDB weekly.... yeah, I have a script that I'm still testing for this. I need to tweak it, but real life has prohibited that.
Labels: DBCC CheckDB, Message: Data conversion failed, SQL, SQL 2008, SQL 2008 Replication
posted by TedS at 10:55 AM
1 Comments:
One note on this post. I ended up replicating the error trying to apply the BCP files to the subsciber. SO I found the data in question and instead of nulling it out I updated the date with the most likely valid date. Well DBCC CheckTable still logged the records as being in error AND the scripts in this thread didn't detect the bad data. Lucky for me this was in test so I was able to null the field for the entire table... however now I know to NULL the data first THEN update it with the proper date.
Live, learn.
Post a Comment
<< Home