Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved "Old-style" join syntax

  1. Advice
0
Votes
Undo
  1. Lynn A Haynes
    Lynn A Haynes
  2. PowerBuilder
  3. Friday, 27 December 2019 20:01 PM UTC

We are migrating a legacy app from SQL Server 2008 to 2012. There are literally THOUSANDS of datawindows that use 'old-style' join syntax that are throwing off errors. I have tried setting the compatibility level to 90, but that's not working. Any advise would be greatly appreciated. 

Source control status not restored
Powerbuilder upgrade
Responses (5)
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Sunday, 29 December 2019 01:53 AM UTC
  2. PowerBuilder
  3. # 1

This is sadly a required syntax change when upgrading MSSQL to compatibility level 90 and higher.

Only remedy I know is to remain at level 80. Newer versions of MSSQL prohibit such low compatibility levels. Remaining at level 80 also means no access to approx. 10 years of improvements to Transact-SQL.

This MSSQL page states very simply that you lose the legacy outer join syntax with level 90 and above.

Sorry, /Michael

Comment
There are no comments made yet.
Ricardo Jasso
Ricardo Jasso Accepted Answer Pending Moderation
  1. Friday, 27 December 2019 23:54 PM UTC
  2. PowerBuilder
  3. # 2

Lynn,

The use of *= and =* was discontinued in SQL Server 2012. That means that even if you set the compatibility level to 100 (SQL Server 2008) it will not get restored.

https://docs.microsoft.com/en-us/sql/database-engine/discontinued-database-engine-functionality-in-sql-server-2016?view=sql-server-2014

I'm afraid you'll have to change it manually, as I did a couple of years ago when migrating a database from SQL Server 2008 to SQL Server 2014, or find a tool that can do it automatically. Since I estimated it was going to take me a week more or less to do the change I didn't search for such a tool but I guess there must be one out there.

Regards,

Ricardo

 

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 27 December 2019 23:24 PM UTC
  2. PowerBuilder
  3. # 3

Hi Lynn;

  Another alternative is to set the SS Join Compatibility mode back to SS2008 level (100)

HTH ... Or at least, buys you some time to get up & running until you can update all that SQL syntax.

Regards ... Chris

Comment
There are no comments made yet.
Lynn A Haynes
Lynn A Haynes Accepted Answer Pending Moderation
  1. Friday, 27 December 2019 22:39 PM UTC
  2. PowerBuilder
  3. # 4

Ugh! There are literally over 1,000 datawindows! Sifting through all that to change join syntax sounds like a job for a utility. Anyone know of any? There must be another way. I have tried OJSyntax=PB, ANSI, even ANSI_ESCAPE, with no change.

Comment
mike S
  1. mike S
  2. Friday, 27 December 2019 22:56 PM UTC
look at one that did not work. Is it syntax or graphical? If syntax then you have to rewrite the sql. if graphical then set the ojsyntax to ANSI in those places i mentioned.



also, *= and Left/right outer join are SIMILAR, but not exactly the same so you have to be aware of what the differences are.







  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Friday, 27 December 2019 20:14 PM UTC
  2. PowerBuilder
  3. # 5

check your datawindows.  If they are SYNTAX rather than graphical, then you will have to recode them if you have *= joins.  Easy to search your application for *= and =* so finding them should be fast.  You need to do this anyway for any in-line sql you may have.

In your database profile setup (this is for development) make sure that you have syntax set to ANSI.  

In your application's connection string (for running the app), make sure you have OJSyntax set to ANSI.

 

Note that there is a option for ANSI escape which should work.  I don't recommend using this.

that should fix a lot of your application.  

If you have complex joins you may have to recode your graphical datawindows to syntax anyway.  I see this mostly with multiple outer joins when more than one column is used to join tables. 

 

 

 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.