SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL - Testing for specific versions of SQL Server


T-SQL - Testing for specific versions of SQL Server

Author
Message
Leonidas199x
Leonidas199x
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 289
Hi all,

I have been tasked with devising a plan for an upgrade of MSSQL 2005 to MSSQL 2016. Doesn't look without is challenges, least of all ensuring that all code will still work with all the deprecated features between 2005 and 2016.

Apologies if this is a noob question, as my background is in dev, as opposed to administration so I don't have any experience in upgrades, but I wondered if there were any tools available to to check if code is compatible with a specific version, or if it points out which versions it will not be compatible with?

As you can imagine, we have reams of stored procs, so just looking for the easiest way to check that these function, and wanted to check of there was another way, other than upgrading on a test box and setting them off to see.

Appreciate any assistance.
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86210 Visits: 18154
Yes, please try the SQL Server 2016 Upgrade Advisor.

John
Leonidas199x
Leonidas199x
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 289
Thanks very much, downloaded it and run it against a test database.

One thing I am a little confused around, if you can shed any light that'd be appreciated.

Looking at this list:

https://msdn.microsoft.com/en-us/library/ms143729.aspx

A deprecated feature appears to be:

A string enclosed in quotation marks used as a column alias for an expression in a SELECT list:

'string_alias' = expression

But if I write a stored proc:


USE [Test_Database3]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCompatibilityTest]
AS
BEGIN
SELECT 'Date' = GETDATE();
END


The data migration assistant doesn't pick it up as a potential issue, when the target version is set to 2016, on a 2008 database. Do you know how the deprecated features are managed? As another is:

Not ending Transact-SQL statements with a semicolon.

And I know there will be shed loads of code which isn't terminated with a ;

Thanks again for you assitance, appreciate it.
Thom A
Thom A
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48558 Visits: 16183
The reason it doesn't pick it up as an issue is that currently, that format is still permitted. In SQL 2016, 'string_alias' = expression will be accepted, however, it will not be in a future version of SQL.

The same is true for not ending with Semi Colons, It is still permitted in SQL 2016. Deprecated means that it is going to be phased out and removed, not that the functionality has been removed already.

The tool is most likely going to bring back breaking changes, of which details can be found here: https://msdn.microsoft.com/en-us/library/ms143179.aspx


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Leonidas199x
Leonidas199x
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 289
Thanks for the clarification!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search