migration from sql8.0 compatibility to ansi standard: any tips?

[Former Member]

Hi,

I've this 10+ year old in house application with over 2000 lines of sql script containing *=

(ie:

SELECT     COUNT(*)

FROM tblcases b,tblinvoice a   

WHERE ( b.jobreferenceno *= a.jobreferenceno )

to be updated to

SELECT     COUNT(*)

FROM         tblcases AS b LEFT OUTER JOIN

                      tblinvoice AS a ON b.jobreferenceno = a.jobreferenceno

---

so I've been converting the various sql statements via searching for *= and =*

do I take it when I no longer have any more *=/=*, I'm pretty fine to go? I also think I do not really have to convert anything that is a graphic view datawindow right?

ie:

statements such as

SELECT     COUNT(*)

FROM tblcases b,tblinvoice a   

WHERE ( b.jobreferenceno = a.jobreferenceno )

would be left unaltered and I don't really need to convert to

SELECT     COUNT(*)

FROM         tblcases AS b INNER JOIN

                      tblinvoice AS a ON b.jobreferenceno = a.jobreferenceno

any other tips?

[Former Member · Accepted solution]

What version of SQL Server are you moving to?

There is a setting on the server that you can change to allow the older join syntax. I'm not sure if SS2012 allows it but if the version you have allows it, you could turn it on and you would be able to rewrite the queries over time instead of all at once.

[Former Member · Accepted solution]

I am currently using MS SQL 2008 R2 on sql 8.0 compatibility. (ie: the last version available that supports PB OJ syntax)

however, I'll be "forced" to upgrade to sql server 2014/2016 eventually (I'm hosting ms great plains dynamics. this software is currently version GPS2013, but the next version upgrade that we do eg: GPS2015/GPS2017 and onwards will only run on sql server 2014 onwards).

due to licenses costs etc, as well as server os upgrades in the near future, there's no point sticking with an older sql server version.

---

I'm not sure if there are other things I need to take care of such as image/text to varbinary (max). and dropping ODBC for SNC .. I'm quite confused right now about this

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

Download Microsoft® ODBC Driver 11 for SQL Server® - Windows from Official Microsoft Download Center

Microsoft SQL Server Native Client 11.0 is installed when you install SQL Server 2016 Community Technology Preview 2 (CTP2). There is no SQL Server 2016 Community Technology Preview 2 (CTP2) Native Client.

Microsoft SQL Server Native Client 11.0 is installed when you install SQL Server 2014. There is no SQL Server 2014 Native Client.

I'm not sure what that means, does it mean that sql server 2016/2018 might drop SNC (ie: SQLCA.DBMS = "SNC SQL Native Client(OLE DB)" " and we revert back to ODBC for driver 11? (ie: DBPARM ="ODBC" ??)

although it seems i need not bother with it for sometime since things just work fine connecting to a test environment on sql server express 2014

[Former Member · Accepted solution]

Hi Chris;

Q1:  Microsoft SQL Server Native Client 11.0 is installed when you install SQL Server 2014. There is no SQL Server 2014 Native Client.

A1: Yes, I heard that MS was dropping support for the SNC client. A strange and unwise decision IMHO. 

Q2:  Revert back to ODBC for driver 11?

A2:  I believe that MS's preferred SS client direction is now OLE-DB or ADO.Net.

=> Either way ... its a PIA to change drivers in either direction as you then need to do a full SQL regression test on your application!  

Regards ... Chris

[Former Member]

I have used SwissSQL to migrate SQL scripts containing a couple of thousand procedures

The free version can make up to 50 changes I think

SwisSQL Console: SQL Converter/Translator Tool. Software to Convert SQL from one Dialect to Another

[Former Member]

Hi Chris;

  That looks good for the in-line SQL. For the DWO's that are using dynamic SQL .. just make sure that you specify ... SQLCA.DBParm="OJSyntax='ANSI'"   in your DBParm settings.

  Of course, test, test & if in doubt test once more. 

Good luck!

Regards ... Chris