We are migrating data from one database to another in the same server.
We are doing it through stored procedure.
We need to capture row level error such that if the insert command fails for a particular row from source table we have to capture that record and proceed with the other records.
We have tried using try catch but how to do it for a particular erroneous row is baffling me.
You haven't given us any details whether you are migrating SQL server or any other database engines.
What version you are migrating it from? Provide more details about the OS and version.
What is the error message you are getting when you are trying to migrate?
We are migrating SQl server database from 2000 and 2008 to 2012 (Windows)
We aren't receiving any errors.
(for example : for a table) We have used while loops to update data across database tables row by row (record by record).
We want to capture errors generated while updating record by record . We would like to log the corrupted records and process information( like no of succesfull updates ..etc) and proceed with the next successful records. Tried on various ways with Try.. catch unsuccessfully.
Please let us know how to capture record level logs while inserting ?
Have you used upgrade advisor to check whether pre-requisite is met. This will throw errors if any SPs are code needs any change.
Check the below link from BOL.
- Edited by kccrga Wednesday, October 30, 2013 9:58 PM
There is option for script out db object definition along with data too, did you try that?
As that way, you will not be missing any data for sure too and you just need to execute the new developed script on new sql server 2012 instance as per my understanding from sql server 2000 and sql server 2008.
I like doing adventurous ways to work and do something unique which is never tried till date, like you!!
Also try\catch is nto able to catch that error means some error is coming which out of understand of try catch supportability part. Did you compare table rows count before after changes? Check this, this may help you.
However to be on safer side, you can use above mentioned ways too.
You also say that, migration for just data not other services or components, did you try backup\restore, or attach\detach approach which will convert all objects too as per your requirement latter on you can remove the required ones if somehow you want something to work.
There are good data sync tool in the market to do this table wise data transfer from Red gate etc.