Migrating T-SQL from SQL 2000 to SQL 2008

Question

Migrating T-SQL from SQL 2000 to SQL 2008

lev.gurevich

Say Hey Kid

Points: 683
More actions
May 4, 2011 at 2:15 pm

#250378

Everybody,
I have to migrate T-SQL stored procedures and functions from SQL 2000 to SQL 2008. Is there any list of incompatibility issues or a tool that I can use to find the SQL 2000 code that will not work in SQL 2008?
I'm not asking about all differences between SQL Server 2000 and 2008. I need to identify only the issues while migrating my T-SQL code (around 700 objects) quickly.
Many thanks for your help.
Lev

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

This should be what you're after:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=f5a6c5e9-4cd9-4e42-a21c-7291e7f0f852&displaylang=en#AdditionalInfo

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

Nuts. I'm slow. Yes, the Upgrade Advisor is the best source for this information. It's the required first step to perform this kind of upgrade anyway. Follow Microsoft's documentation. It's good.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

lev.gurevich Say Hey Kid Points: 683 More actions · Answer 3

Thank you for your advice.

I tried the Microsoft SQL Server 2008 Upgrade Advisor and it helped. But it turned out it couldn't identify all issues. For example, the SELECT statements with SQL 2000 syntax for HOLDLOCK were not discovered with the Upgrade Advisor, e.g.

SELECT @last_used_nbr = last_used_nbr

FROM next_workorder_nbr HOLDLOCK

SQL 2008 requires the statement to be like that:

SELECT @last_used_nbr = last_used_nbr

FROM next_workorder_nbr WITH (HOLDLOCK)

Are there alternative tools? Is there a list of the differences made for T-SQL in SQL 2008 vs. 2000?

Thanks again,

Lev

HowardW One Orange Chip Points: 29109 More actions · Answer 4

I doubt it's 100% perfect, but in that specific example, it's deprecated rather than incompatible, so it will work if the compatibility of the database was still set to 80 after you upgraded (which it would be by default)

lev.gurevich Say Hey Kid Points: 683 More actions · Answer 5

Howard,

That's true that the mentioned statement works with the 80 compatibility mode. The problem is that our server upgrade assumes the 100 compatibility mode to be set. The reason is that with the 80 compatibility we'll lose new features.

That's why I have to repeat my questions:

- Are there alternative tools?

- Is there a list of the differences made for T-SQL in SQL 2008 vs. 2000?

Thanks,

Lev

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

yes, it's called quality assurance. It's the part of the plan where you rerun all the parts of your app on a test server and see what breaks.

This is also why I never build an app without unit tests. Makes migration so much easier.

Sorry to be so blunt but there's no shortcut here... only you knows you application. Nobody else on the planet does.

HowardW One Orange Chip Points: 29109 More actions · Answer 7

The lists of deprecated/discontinued features for SQL 2008 are linked from here:

http://weblogs.sqlteam.com/derekc/archive/2008/06/06/60618.aspx

You'll also have to consider SQL 2005's versions of the same lists as you're jumping two versions.

No, there's no alternative tool - in any case, any of these tools are a first pass, not a certification of compatibility. There's no alternative for thorough testing after upgrade.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

You might as well run the R2 upgrade advisor while you're at it... might save you some work on the next upgrade and future devs.

lev.gurevich Say Hey Kid Points: 683 More actions · Answer 9

lev.gurevich

Say Hey Kid

Points: 683

May 5, 2011 at 8:32 am

#1320456

Howard,

Thank you very much. It helps.

Regards,

lev

lev.gurevich Say Hey Kid Points: 683 More actions · Answer 10

Ninja,

Thanks for your advice. I'll consider it.

Regards,

Lev

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 11

There is no alternative tool. That's the best one.

You will absolutely need to plan for thorough testing. One of the issues we found early on when migrating to 2005, but it was also there for migrating to 2008, was that certain ADO calls that worked in 2000 absolutely did not work in 2005/2008. No amount of database testing identified that. It required us to also test the app. Please take that into account.

If the Upgrade Advisor missed something, I'd strongly suggest you open a Connect item on it. I'll be Microsoft will be reasonably quick to answer those. They want people to upgrade.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

lev.gurevich Say Hey Kid Points: 683 More actions · Answer 12

Grant,

Thanks for your advice. We'll be definitely testing our apps.

What do you mean by opening a Connect item? What is the Connect item? Where should it be opened?

Lev

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

lev.gurevich (5/5/2011)
Grant,
Thanks for your advice. We'll be definitely testing our apps.
What do you mean by opening a Connect item? What is the Connect item? Where should it be opened?
Lev

It's the official Microsoft bug report tool.

http://connect.microsoft.com/SQLServer

lev.gurevich Say Hey Kid Points: 683 More actions · Answer 14

lev.gurevich

Say Hey Kid

Points: 683

May 5, 2011 at 11:05 am

#1320566

Thanks. I'll submit an item there.