Need to Convert SQL Code Written for Azure SQLDW to SQL Server 2016

  • Hello Community,

    The attached SQL Script (Procedure) is compiled / written for Azure SQL DW. As such it has code like

    WITH

    (

    DISTRIBUTION = ROUND_ROBIN

    ,CLUSTERED COLUMNSTORE INDEX

    )

    or

    SET @sessionId = session_id()

    The above code is supported in regular SQL 2016.

    I was wondering if someone could look at the code and let me know what exactly I would need to remove to make it work with SQL 2016.

    I appreciate there are 100's of line of code, but if someone could just guide me on how to make it work with SQL 2016 or convert the code (if you have time), that would much appreciated.

     

    Thanks

     

     

    Attachments:
    You must be logged in to view attached files.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • The code looks broadly compliant to me, though with that many cascading CTEs I would expect poor performance.

    This part looks like it will cause issues:

    CREATE TABLE probateL1.tmpTblLegalProbateGrantCase
    WITH
    (
    DISTRIBUTION = ROUND_ROBIN
    ,CLUSTERED COLUMNSTORE INDEX
    )
    AS
    WITH
    cteLegalProbateGrantOfRepresentationCase
    AS
    (
    SELECT ceCaseDataId AS legalCaseId,
    ...............

    Instead of doing this, I suggest you create the table and then, as a subsequent separate step, insert data to it.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply