Sql 2008 Vs 2012 Error: Incorrect Syntax Near The Keyword 'Compute'

SQL 2008 VS 2012 Error: Incorrect syntax near the keyword 'COMPUTE'

COMPUTE is no longer available in SQL server 2012, thats why you are getting that error. See this page:

• Discontinued Database Engine Functionality in SQL Server 2012

It said that:

This topic describes the Database Engine features that are no longer
available in SQL Server 2012:

*Transact-SQL syntax | COMPUTE / COMPUTE BY *

Incorrect syntax near 'order'. Running Total. SQL Server 2012

The syntax seems fine - so it should work against SQL Server 2012.

There's two possibilities that it's still not valid:

• you're running SQL Server 2012 Management Studio, but against an older SQL Server engine version - you can check this by running:

  SELECT @@VERSION
  

• you're running with a database that has a lower compatibility level and thus doesn't support the SQL Server 2012 features, like the OVER (ORDER BY...) window function.

  Find out by running this against your database:

  SELECT name, compatibility_level 
  FROM sys.databases 
  WHERE database_id = DB_ID()
  

  The compatibility level should be 110 for SQL Server 2012 - if it's lower that that, you might want to change that.

Update: since you've determined that the compatibility level of this database is 80 (= SQL Server 2000), you need to change that to a more recent version. To upgrade your database to compatibility level 105 (SQL Server 2008 R2) which should support those window functions, use this command:

ALTER DATABASE (YourDatabaseName)
SET COMPATIBILITY_LEVEL = 105;

and you're done. Now your query should work just fine.

Can't execute a COMPUTE statement

The COMPUTE clause is no longer supported in SQL Server 2012. The documentation suggests using ROLLUP instead.

Incorrect syntax near ''

There was no IIF in SQL Server 2008R2.

Replace it with CASE

ALTER TABLE [dbo].[WorkTimeEntries] ADD [TimeFinishedForRuntime] AS ISNULL(
    [TimeFinished],
    CASE WHEN [TimeStarted] < SYSUTCDATETIME() THEN [dbo].[udf_GetCurrentDateTimeOffsetInTimeZone](DATENAME(TZOFFSET, [TimeStarted])) ELSE [TimeStarted] END);

Incorrect syntax near 'OFFSET' modift sql comm 2012 to 2008

As found out in the comments the reason for the error is because of the fact that SQL Server 2008 does not support it. You may try to change the query according to SQL Server 2012.

Something like this:-

SELECT column1
FROM   (
          SELECT column1, ROW_NUMBER() OVER (ORDER BY column_id) AS x
          FROM   mytable
       ) AS tbl
WHERE  tbl.x BETWEEN 20 AND 30

In your code:-

SELECT * FROM  
(SELECT ROW_NUMBER() OVER(ORDER BY q.qId) AS rownumber 
FROM tblQuestion AS q 
INNER JOIN tblUser AS u ON q.uId = u.uId 
INNER JOIN tblQuestionCategory AS qcat ON q.qcatId = qcat.qcatId ) as somex 
WHERE  somex.rownumber  BETWEEN 11 AND 20

---

The issue is because you have not defined @page.

Try this (As you have not mentioned what is @page. I am taking it as some constant or may be you can declare it and then set the value for it):-

declare @page int
set @page = 5  // You may set any value here.

SELECT q.qTitle, q.qDescription, q.qCreatedOn, u.uCode, 
u.uFullname, qcat.qcatTitle, q.qId, q.qStatus 
FROM tblQuestion AS q 
INNER JOIN tblUser AS u ON q.uId = u.uId 
INNER JOIN tblQuestionCategory AS qcat ON q.qcatId = qcat.qcatId 
WHERE (q.qStatus = 1) 
ORDER BY q.qCreatedOn DESC 
OFFSET (@page*10) ROWS
FETCH NEXT 10 ROWS ONLY

Incorrect Syntax near Rows after trying to calculate Running Total on column

I don't think you need to use CTE or ROWS UNBOUNDED PRECEDING :

SELECT [DATE], [Description], [PRICE],
       SUM([PRICE]) OVER (ORDER BY [DATE]) AS RUNNINGTOTAL
FROM PRODUCTS;

Incorrect Syntax When Using Compute By on SQL Server Management Studio 2012

COMPUTE / COMPUTE BY is discontiuned in SQL 2012. http://msdn.microsoft.com/en-us/library/ms144262.aspx
Instead of COMPUTE / COMPUTE BY use ROLLUP.

Incorrect syntax near ''

Such unexpected problems can appear when you copy the code from a web page or email and the text contains unprintable characters like individual CR or LF and non-breaking spaces.

Getting error Incorrect syntax near the keyword 'SET'. while Updating the db tables

This works for me:

DECLARE @name VARCHAR(50)
DECLARE @TQ VARCHAR(500)
DECLARE @ContainName VARCHAR(500)

DECLARE db_cursor CURSOR
FOR
    SELECT  TABLE_NAME
    FROM    INFORMATION_SCHEMA.TABLES
    WHERE   TABLE_TYPE = 'BASE TABLE'

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0
    BEGIN   
        IF COL_LENGTH(@name, 'IsDeleted') IS NOT NULL
            BEGIN
                IF COL_LENGTH(@name, 'IsActive') IS NOT NULL
                    BEGIN
                        EXEC ('UPDATE '+@name+' SET  [IsActive] = 1 - [IsDeleted]')
                    END      
                ELSE
                    BEGIN
                        EXEC('ALTER TABLE '+@name+' ADD IsActive bit')
                        EXEC ('UPDATE '+@name+' SET  [IsActive] = 1 - [IsDeleted]')
                        EXEC('ALTER TABLE '+@name+' ADD CONSTRAINT DF_'+@name+' DEFAULT(0) for [IsActive]')

                    END
                IF EXISTS ( SELECT  *
                            FROM    sysobjects o
                                    INNER JOIN syscolumns c ON o.id = c.cdefault
                                    INNER JOIN sysobjects t ON c.id = t.id
                            WHERE   o.xtype = 'D'
                                    AND c.name = 'IsDeleted'
                                    AND t.name = @name )
                    BEGIN
                        SET @ContainName = ( SELECT o.name
                                             FROM   sysobjects o
                                                    INNER JOIN syscolumns c ON o.id = c.cdefault
                                                    INNER JOIN sysobjects t ON c.id = t.id
                                             WHERE  o.xtype = 'D'
                                                    AND c.name = 'IsDeleted'
                                                    AND t.name = @name
                                           )
                        EXEC('ALTER TABLE ' + @name + ' drop constraint ' + @ContainName)
                    END
                EXEC('ALTER TABLE '+@name+' DROP COLUMN [IsDeleted]')
            END

        FETCH NEXT FROM db_cursor INTO @name   
    END   

CLOSE db_cursor   
DEALLOCATE db_cursor

---

Related Topics