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.
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Roxanne25

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
x-men

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Aneesh

>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
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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!
Anthony Perkins

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Roxanne25

ASKER
Thanks for the reply, I never mentioned what version of 2008 we are on.  We are on R2.