Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Compatabilty error Expand / Collapse
Author
Message
Posted Thursday, September 27, 2012 3:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 2:40 AM
Points: 52, Visits: 136
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
Post #1365107
Posted Thursday, September 27, 2012 3:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 31, 2014 9:19 PM
Points: 1,128, Visits: 1,162
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
Post #1365119
Posted Thursday, September 27, 2012 3:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:19 AM
Points: 1,863, Visits: 2,977
Can you post your query? It's probably using some structures not allowed in compat level 100.
Thanks
Post #1365120
Posted Thursday, September 27, 2012 4:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 2:40 AM
Points: 52, Visits: 136
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.
Post #1365125
Posted Thursday, September 27, 2012 4:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 2:40 AM
Points: 52, Visits: 136
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.
Post #1365126
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse