Migration of simple SPs from 120 to 150 COMPATIBILITY_LEVEL issue.

  • This is one of the procs, which having issues:

    /****** Object: UserDefinedTableType [dbo].[Identifier_type]******/

    CREATE TYPE [dbo].[Identifier_type] AS TABLE(

    [id] [BIGINT] NOT NULL

    )

    GO

    CREATE PROCEDURE [dbo].[WorkerABC_prc]

    (@workerIds Identifier_type READONLY)

    AS

    BEGIN

    SELECT swm.SyncWorkerId

    FROM SyncWorkerMap swm

    INNER JOIN @workerIds w

    ON swm.SyncWorkerId = w.id

    WHERE swm.IsAuditWorker = 0;

    END;

    GO

  • Thank you for posting the code - I believe the problem you have is going to be the estimation of rows for the table-valued parameter.  You can try modifying the code using OPTION(RECOMPILE) or you can create a regular temp table from the parameter and use that temp table in the actual query, or you can experiment with enabling trace flag 2453.

     

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 2 posts - 16 through 16 (of 16 total)

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