Migrating from SQL2000 to SQL2017 - syntax changes

Question

0.00/5 (No votes)

See more:

SQL-Server-2016

Hi all,

I've done some searching but not found anything in particular so thought one of you clever people might have done this yourselves and made some notes possibly..

We have some ancient VB legacy apps running on SQL2000 and we intend to go up to SQL2017.

Now I know the compatibility level is going to stop us in our tracks however, all the table creation code and field definitions, along with relationships and indexes are coded within the app itself, i.e. if i want to create a new database for our client, we have a template set of CREATE TABLEs, CREATE INDEXes and relationships etc... so I am happy to export the data from 2000 into a unicode CSV via code.

Then run the create database .net code in SQL2016 so our tables, indexes and relationships are recreated with empty content, then import each table back into 2017, obviously using the new datatype names where they have changed.

At least thats what I have in mind - my issue, is the differences between syntax on SQL commands - we dont use procedures or anything that is tied to SQL Server, <everything> runs from VB6 or VB.NET code via ADODB or OLEDB so the only thing Im looking for (if I am on the right track, that is) is changes to our SQL code itself when doing SELECTs against our own tables, such as
SELECT TOP 10 * FROM MyTable
is probably now SELECT * FROM MyTable LIMIT 10 OFFSET 0

but are there other changes - such as RIGHT JOINS, CAST, CONVERT, CASE WHEN.

We also do not use BLOBS and I know some of our datatypes ARE going to be different, ntext I believe is now nvarchar(max), we dont have to put N' for inserting or update unicode field text data - are there any others - is there a cheat sheet available that someone know about listing all syntax differences. I know I probably wont be able to connect to sql server 2017 via ADO but need to use ODBC which is slower connecting but at least it'll keep us working until we are full OLEDB compatible.

Any thoughts on the above or suggestions that I might have missed?

What I have tried:

not tried it yet! just trying to do a bit of planning

Posted 26-Jul-18 2:20am

Member 12561559

Add a Solution

Comments

ZurdoDev 26-Jul-18 8:59am

I would suggest just trying it and see what happens.

You don't have to use LIMIT in a TOP statement. You can also leave N' for your strings. I wouldn't change anything unless it is broken. That's just me.

Member 12561559 26-Jul-18 11:26am

Sorry, I would have put the LIMIT at the end of the statement but remove the TOP N out of the statement. By the looks not much has to be done ! Thanks for your input

Richard Deeming 26-Jul-18 10:25am

"we dont have to put N' for inserting or update unicode field text data"

You do, but only if you're using string concatenation to build your queries, which would leave you open to SQL Injection.

Use parameterized queries, and you won't have to worry about it.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

"I know I probably wont be able to connect to sql server 2017 via ADO"

I don't see why there would be any problem using ADO to connect:
Using ADO with SQL Server Native Client[^]

Member 12561559 26-Jul-18 11:28am

Sorry I meant, I would leave out the N part i.e. update abc set mytext=N'some unicode chars' to update abc set mytext='some unicode text' would be fine for unicode now - cheers

Richard Deeming 26-Jul-18 11:31am

No, it wouldn't. You still need the N' prefix for Unicode text; otherwise, it will be converted to ANSI using the default code page, and then converted back to mangled Unicode.

But if you use parameters, you avoid that problem entirely, as well as protecting yourself from SQL Injection.

Richard Deeming 26-Jul-18 10:27am

Upgrade SQL Server | Microsoft Docs[^]

Member 12561559 26-Jul-18 11:27am

Thanks Richard! Most appreciated

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)