Options
NTEXT issues with SQL Data Compare
phispher
Posts: 11
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
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
Comments
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
Product Manager
Redgate Software
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.
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.
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.
Project Manager
Red Gate Software Ltd