Link to home
Start Free TrialLog in
Avatar of Roxanne25
Roxanne25

asked on

SQL Compatibility Modes - From 80 to 100

Hi there,

We have recently upgraded our DB from SQL 2000 to SQL 2008.  I was not aware that the compatibility modes would remain as if it were a 2000 database.  I tried to change it to 2008 (100) but stuff in our front end application started erroring out with deprecated code.

Is there a third party tool or some extended stored procedure that will automatically check the database to see what is not compatible with 2008?  I'm sort of bummed out cause I cannot use any of the new SQL 2008 stuff unless I get this fixed.  Its not really feasible for me to manually go in a check every single code object to make sure there are no deprecated syntaxes.  

I wish there was a way to set certain stored procedures to run in SQL 2008 and then let the old stuff run in 2000.
ASKER CERTIFIED SOLUTION
Avatar of x-men
x-men
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>Is there a third party tool or some extended stored procedure that will automatically check the database to see what is not compatible with 2008?
You can install sql server migration assistant and scan against this database; this will list you the incompatable objects stored in the database
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Roxanne25
Roxanne25

ASKER

Yea the migration assistant didn't point out compatibility mode things... and we have already migrated to 2008, so the advisor cannot even be ran anymore because we don't have 2000 now.

I was just hoping there was some auto scanning thing... but I believe the link that x-men provided is gonna be my best bet for identifying the things that need to be changed.  I don't think we have any joins written like *= but I believe we have some of the other things listed in the article.  

Thanks guys!
I realize you have already accepted an answer, however I wonder (since you are using SQL Server 2008 and not SQL Server 2008-R2) if you have considered downloading and running the Microsoft® SQL Server® 2008 R2 Upgrade Advisor.  This should show you all the incompatibilities.
Thanks for the reply, I never mentioned what version of 2008 we are on.  We are on R2.