Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
russell
Pyro-ma-ni-yak
5072 Posts |
 Posted - 2011-08-03 : 01:34:08
|
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 (or so I think).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 or 6 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 DATABASEand 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 processquote:
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: quote:
During upgrade, database raised exception 102, severity 25, state 1
The actual error trapped is "incorrect syntax near..." which corresponds with one of the offending procedures of course.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. I can't find any documentation of such, but it appears to be the case.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. Thoughts? Discussion?Add Reply |
|
|
|
|
|
Try/Catch during Upgrade?