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.

A little TSQL fun

So I found a script that did a dbcc checkdb in a 'data driven' manor... it was kinda slick but it assumes you can check all of your database in one swoop. Well, I can't. SOOOOoooooooo I decided to do a little date trickery.

The trickery is simple, I have x number of databases, 7 days of the week. I split them evenly along each of the 7 days. so I do y databases a day based upon the day of the week.

The code is pretty easy to follow, I have only run it on SQL 2008 and have yet to put it into production.. so here goes the code


It was formatted, but I know blogger will kill the formatting:


declare @StartID int = (select ((COUNT(*))/7) * DATEPART(weekday,getdate()) from master..sysdatabases),
@EndID int = (select (select (COUNT(*)/7) * DATEPART(weekday,getdate()) from master..sysdatabases)+ (select (COUNT(*))/7 from master..sysdatabases)-1)

declare database_cursor CURSOR for select name from master..sysdatabases where dbid between @StartID and @EndID
declare @database_name sysname

open database_cursor
fetch next from database_cursor into @database_name
while @@FETCH_STATUS=0
begin
print @database_name
dbcc checkdb(@database_name) with no_infomsgs
fetch next from database_cursor into @database_name
end

close database_cursor
deallocate database_cursor