DB Upgrade and Try Catch

Last Post 27 Jul 2012 12:23 PM by hlattanzio. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
russellb
New Member
New Member

--
02 Aug 2011 07:42 AM

I discovered something interesting over the last few days.  Haven't found any type of documentation that explains this, but I've figured out what's happening.

Here's the scenario.

Target server: SQL Server 2008 EE x64, Win Server 2008 EE.

I'm attempting to attach a database where the source is SQL 2005 EE x64 but the database is in SQL 2000 compatibility mode (it was originally moved from a SQL 2000 instance 5 years ago).  The database has known compatibility issues that were never fixed, but they work fine in production with the compatibility mode still in 2000.

This database will attach or restore just fine using any of
- CREATE DATABASE FOR ATTACH
- sp_attach_db
- RESTORE DATABASE

and the upgrade process finishes without issue.

BUT...wrap any of the above methods in a Try/Catch block and it fails (passes control to the catch block) during the upgrade process

Converting database 'xyz' from version 611 to the current version 655.
Database 'xyz' running the upgrade step from version 611 to version 621.
Database 'xyz' running the upgrade step from version 621 to version 622.
Database 'xyz' running the upgrade step from version 622 to version 625.
Database 'xyz' running the upgrade step from version 625 to version 626.
Database 'xyz' running the upgrade step from version 626 to version 627.
Database 'xyz' running the upgrade step from version 627 to version 628.
Database 'xyz' running the upgrade step from version 628 to version 629.
Notice the upgrade stops before it gets to 655.

And the event logs show exactly what you'd expect here:
During upgrade, database raised exception 102, severity 25, state 1
The actual error trapped is "incorrect syntax near..."

Seems to me that there is something built into the upgrade that ignores errors below a certain severity.  But wrap it in a try/catch and it is trapped.

Take away the try/catch and all 3 methods work just fine.  Obviously the real fix is to re-write the stored procs that are known to have syntax that is no longer supported (or just don't use try/catch).  Still, I find this behavior interesting and wasn't aware that the TRY will catch errors not returned to the application were it not used.

hlattanzio
New Member
New Member

--
27 Jul 2012 12:23 PM
We just hit the same thing. We can restore our 2005 backed up database in SQL 2012, but as soon as we put the restore commands into a TRY CATCH, it fails.

It turns out there are some triggers that use discontinued code in SQL 2012, so I can understand why it fails. What I can't understand is why, without the TRY CATCH, it does not fail and apparenlty allows the code. The database is converted just fine; it completes the upgrade steps without the TRY CATCH, but does not within the TRY CATCH. I expect these triggers won't work, (that's ok, I am restoring as a reporting only database so will not need the triggers) but why did the database RESTORE fully without error?

Has anyone else run into this or knows why this works this way? This is the only post that appears to talk about this.
You are not authorized to post a reply.

Acceptable Use Policy