SQL Compatabilty error

  • Hi Guys,

    Please help me on this, I have executed this SP EXEC sp_dbcmptlevel 'CDDB6', 100, before runing my Sql Query

    I am getting the below error.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Core'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Discovery'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Discovery'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Discovery'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Discovery'.

    (1 row(s) affected)

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Design'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Design'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Design'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Design'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Execution'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Execution'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Execution'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Execution'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Factor_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'per'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Defect'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Variation_TARGET'.

    Msg 102, Level 15, State 1, Line 1

  • what query ?

    If it's syntax error inside procedure ; then stored procedure would never have complied.

    can you please elaborate , with DDL ??

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Can you post your query? It's probably using some structures not allowed in compat level 100.

    Thanks

  • DECLARE @LEANTEMP TABLE

    (

    ID INT IDENTITY(1,1),

    SETUPDETID BIGINT ,

    LEVELVALUEID BIGINT,

    METRICSID BIGINT,

    METRICNAME VARCHAR(250),

    DIMENSIONID BIGINT,

    DIMENSIONVALUEID BIGINT,

    TODATE DATE

    )

    INSERT @LEANTEMP ( SETUPDETID, LEVELVALUEID , METRICSID , METRICNAME, DIMENSIONID , DIMENSIONVALUEID ,TODATE )

    SELECT

    SCHEMAMANLOG.TrnMetricsSetupDetails.SetupDetailsId,

    NOTEXIST.LEVELVALUEID,

    METDET.METRICSID,

    METDET.MetricsName,

    SCHEMAMANLOG.TRNMETRICSSETUPMETRICSDIMENSIONS.DIMENSIONID ,

    SCHEMAMANLOG.TRNMETRICSSETUPMETRICSDIMENSIONS.DIMENSIONVALUEID,

    NOTEXIST.TODATE

    FROM @PROJECTSETUPNOTEXIST "NOTEXIST"

    INNER JOIN SCHEMAMANLOG.TRNMETRICSSETUPDETAILS

    ON SCHEMAMANLOG.TRNMETRICSSETUPDETAILS.SETUPID = NOTEXIST.SETUPID

    INNER JOIN SCHEMAMANLOG.TRNMETRICSSETUPMETRICSDIMENSIONS

    ON SCHEMAMANLOG.TRNMETRICSSETUPDETAILS.SETUPDETAILSID = SCHEMAMANLOG.TRNMETRICSSETUPMETRICSDIMENSIONS.SETUPDETAILSID

    INNER JOIN SCHEMAMANLOG.MASMETRICSDIMENSIONS

    ON SCHEMAMANLOG.MASMETRICSDIMENSIONS.DIMENSIONID = SCHEMAMANLOG.TRNMETRICSSETUPMETRICSDIMENSIONS.DIMENSIONID

    AND SCHEMAMANLOG.MASMETRICSDIMENSIONS.ISACTIVE = 1

    INNER JOIN SCHEMAMANLOG.MasMetrics AS METDET

    ON METDET.TagId =1

    AND METDET.IsMeasure = 0

    AND METDET.METRICSID = SCHEMAMANLOG.TRNMETRICSSETUPDETAILS.METRICSID

    DECLARE @COUNT INT

    DECLARE @LOOPCOUNT INT

    DECLARE @COLUMNNAME VARCHAR(200);

    DECLARE @METRICID INT

    DECLARE @QUERY VARCHAR(max);

    DECLARE @Dimension INT

    DECLARE @DIMENSIONVALUE BIGINT

    DECLARE @SETUPDET BIGINT

    DECLARE @LEVELVID BIGINT

    DECLARE @TDATE DATE

    SET @LOOPCOUNT = ( SELECT COUNT(ID) FROM @LEANTEMP )

    SET @COUNT = 1

    WHILE( @COUNT <= @LOOPCOUNT )

    BEGIN

    SET @COLUMNNAME = NULL

    SELECT @METRICID = METRICSID,@LEVELVID = LEVELVALUEID,@TDATE=TODATE,@DIMENSION = DIMENSIONID,

    @SETUPDET = SETUPDETID,@DIMENSIONVALUE = DIMENSIONVALUEID,@COLUMNNAME =METRICNAME FROM @LEANTEMP WHERE ID = @COUNT

    SET @COLUMNNAME = REPLACE(@COLUMNNAME, 'trnLeanMetricsValue.', '')

    IF(@DIMENSION=1 AND @METRICID in(143,144,145,146,147,149,150,151,152,153,154,155,156,157,158,195,197,200,205,206,207,208))

    BEGIN

    set @Query = 'UPDATE SETDIM SET SETDIM.TARGET = ISNULL('+@ColumnName+'_TARGET,SETDIM.GOAL) FROM

    SCHEMAMANLOG.TRNMETRICSSETUPMETRICSDIMENSIONS SETDIM

    INNER JOIN SCHEMAMANLOG.trnLeanMetricsValue METVAL

    ON METVAL.C20PhaseID is null

    AND C20ProcessDisciplineID is null

    AND SETDIM.SetupDetailsId='+ CAST( @SETUPDET AS VARCHAR(10)) +'

    AND SETDIM.DIMENSIONID=1

    AND METVAL.LevelValueID=' + CAST( @LEVELVID AS VARCHAR(10)) +'

    AND METVAL.TODATE='''+ CAST( @TDATE AS VARCHAR(12)) +''''

    END

    END

    Here I am updating TRNMETRICSSETUPMETRICSDIMENSIONS Table.

  • Hi guys thanks, the issue is resolved. I just taken the value for @columname inside while loop. actually i was taking @column name value from @leantemp temporary table. Now i have taken the value inside while loop, its working now. I dont know whts the exact problem.

    Both the methods are fetching same column name only. But the issue is resolved now.

Viewing 5 posts - 1 through 4 (of 4 total)

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