0

I am moving an app from SQL Server 2008 to SQL Server 2012 Express, and all is well but several stored procedures are saying I have a syntax error. I have looked online and have not found an answer - are there types of syntax that are supported in SQL Server 2008 (Standard) but not in SQL Server 2012 Express?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
zortacon
  • 617
  • 5
  • 15
  • 7
    None that I can think of. Maybe you can show an example and the ACTUAL error message. Some smart folks here, but very few mind-readers. – Aaron Bertrand May 21 '12 at 23:59
  • 1
    Sorry, I know it is vague but the errors are all over the place. Some syntax, some saying an object does not exist, etc. I have not worked with MS products before but I thought it could be something like going from perl4 to perl5, or php 4 to php5, where certain things break and there is a conversion guide out there. I don't know that to be the case and I have not found one but I suspected something like that could be the answer. I will add some actual error messages. Thanks. – zortacon May 22 '12 at 07:20

2 Answers2

5

I guess there is one possibility, if in 2008 you were using 2000 compatibility mode, and your stored procedures had old-style outer joins:

SELECT o.name, c.name
FROM sys.objects AS o, sys.columns AS c
WHERE o.[object_id] *= c.[object_id];

This syntax works in SQL Server 2000, but has been deprecated since then. In 2005, 2008 and 2008 R2 you could shoe-horn it in if you use 80 compatibility mode. As of SQL Server 2012, you can no longer use 80 compat mode, so the above code would fail with:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '*='.

In 2008, you would get this error message instead:

Msg 4147, Level 15, State 1, Line 3
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

But if you alter the database as suggested in the error message, it would work:

ALTER DATABASE foo SET COMPATIBILITY_LEVEL = 80;

This seems like a stretch. But without some real information it's about the only guess I have.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • The "Incorrect syntax near '*='" was one of the messages I got. It was a version 2000 compatible database before. I had never seen that kind of syntax. What is the best way to go about converting or learning about what needs to be converted? – zortacon May 22 '12 at 07:14
  • 1
    @zortacon: The error message says it all: `It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN).` – ypercubeᵀᴹ May 22 '12 at 11:05
3

Here's a list of deprecated features in SQL Server 2012-- take a look at the T-SQL section(s) in particular. You could also use SSDT (SQL Server Data Tools), create an offline copy of your DB and then set the version targeting to SQL Server 2012 - the output would show show many of the syntax incompatibilities between versions. I wrote a blog about SSDT that might be helpful here.

Lynn Langit
  • 4,030
  • 1
  • 23
  • 31