locked
Need to convert thousands of old-style joins... RRS feed

  • Question

  • Hi all

    We are going to migrate a SQL Server 2005 instance to SQL 2017.
    In the current instance, we have around 400 databases and more than six thousand views using old-style joins.
    I did some research to find out if there is a reliable tool that can automatically manage the bulk of the conversion.
    Since these are not particularly complicated queries, the SSMS 'design' function seems to work well, but I don't know how to recall it from script.
    Can anyone suggest me a way to do that?

    Thanks in advance

    Wednesday, April 8, 2020 3:44 PM

All replies

  • Hi Luca70 dba,

    You could use Data Migration Assistant (DMA) to migrate SQL Server. DMA Supported migration from SQL Server 2005 to SQL Server 2017. You could download it from here.

    For more details about DMA, please refer to the following articles:

    Overview of Data Migration Assistant

    Upgrade SQL Server using the Data Migration Assistant

    An overview of the Database Migration Assistant tool provided by Microsoft

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 9, 2020 2:20 AM
  • Thanks for the answer. I check that ASAP.

    Thursday, April 9, 2020 8:18 AM
  • Looks like DMA is able to find the old-style joins, but doesn't convert them, either if you perform a complete migration.

    As I wrote before, the design tool of Management Studio does it, just opening any single view.

    My problem is that I have more than 6,000 views to convert...

    Thursday, April 9, 2020 11:03 AM
  • Hi Luca70 dba,

    Based on my research, it seems there is no tool to convert old-style joins automatically.

    You may need to convert them manually after migration.

    Please refer to the following threads which might help:

    T SQL joins to ansi sql joins conversion

    converting old sql outer join syntax =*/*=

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 10, 2020 8:11 AM
  • Thanks again, Amelia.

    Luca

    Friday, April 10, 2020 10:56 AM
  • There is no way to fully "automatically" convert the old style =* and *= outer joins to new ANSI style joins.  The old style was ambiguous, which is why it was removed.  

    You will need to convert them and test them to verify they return the same values as previously, manually.

    Friday, April 10, 2020 11:59 AM
  • It's what I've been afraid of since I opened this post ....
    Thanks to you too, Tom

    Luca

    Friday, April 10, 2020 3:23 PM
  • Hi Luca,

    Did the answers above help you?

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and encourage the community member to keep working on your issues.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 13, 2020 6:13 AM
  • Hi Amelia,

    Honestly, I can't say that I had an "answer" to my issue, even if is always a pleasure come here and discuss aboout Sql questions and people are very kind and professional...

    Still hoping then I could find a solution as looks like SSMS designer does what I need... If I find a way to automatize the process, I'll come here to update all of you.

    Luca

     

    Monday, April 13, 2020 6:02 PM