NTEXT issues with SQL Data Compare

phispher

Hi,

I'm getting the following error when attempting to synchronize between two databases with exactly the same schema using the SQL Data Compare API:

System.Data.SqlClient.SqlException: Page (1:72383), slot 24 for text, ntext, or image node does not exist.
at System.Data.SqlClient.SqlDataReader.Read()
at RedGate.SQLDataCompare.Engine.ComparisonSession._1(_83 , _83 , TableMapping , ProgressPercentageTracker )
at RedGate.SQLDataCompare.Engine.ComparisonSession.CompareDatabases(Database db1, Database db2, TableMappings mappings, SessionSettings sessionSettings)
at RedGate.SQLDataCompare.Engine.ComparisonSession.CompareDatabases(Database db1, Database db2, SchemaMappings mappings)

I deleted the suspect table in the destination database and used SQL Compare to recreate it, then ran the data compare with the API again. It worked fine when pulling the data over to the empty table. However, on a followup run after that, the same error occured. Would you have any ideas why this is occuring and what I can do to fix this?

Just to add to the confusion, when i run a data compare using SQL Data Compare 4 API on the same set of databases, it works fine. I never get any errors. I have run DBCC checkdb and checktable on both databases and receive no errors, so I'm at a loss.

Thanks

David Atkinson

Hi,

Would it be possible for you to send me the sql to generate the table in question? If the data is important in making it fail, could you provide this too? If you supply me with this I can try to reproduce the problem over here.

Have you tried this using the DataCompare UI as well as the API? If so, did it work?

Thanks!

David Atkinson

phispher

Hi,

I played with the options in the UI for a little bit and found an optimum configuration that worked for me.

mappings.Options = new EngineDataCompareOptions(
MappingOptions.IgnoreCase | MappingOptions.IgnoreSpaces |
MappingOptions.IncludeIndexedViews | MappingOptions.IncludeIdentities |
MappingOptions.IncludeTimestamps,
ComparisonOptions.Default,
SqlOptions.DisableKeys| SqlOptions.UseTransactions)

Thanks for your quick response, if I see any other issues, I'll post them here.

Brian Donahue

Hi,

Just a guess: is the table with the ntext column perhaps missing a primary key or unique index column? I don't know if this had been addressed in v5, but v3 could not synchronize any BLOBs when there wasn't a unique index on the table.

richardjm

Having a look this doesn't seem to be something to do with SQL Data Compare more one of your tables being corrupt in some way. Not sure quite what could be going on. I came across http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=170397 that does seem to talk about an error similar to yours.

I've never seen that error in any testing but it is raised directly from SQL Server when we attempt to retreive the row data.

Not sure what more I can suggest.