January 13, 2009 at 6:41 am
Hi
We have some servers which I am upgrading from 2000 to 2005.
A developer has pointed out a piece of code which now fails (in test luckily)
DECLARE @Fid INT SET
This looks like a syntax error to me and if you run it on 2005 or 2008 it will generate a syntax error.
In SQL 2000 (or SQL 2000 compatibility mode) it runs ok and declares the variable.
Am I missing a reason for the SET - is it auto defining the variable or is SQL Server 2000 simply ignoring it?
Thanks
Seth
January 13, 2009 at 7:49 am
Strange Syntax.
SQL2000 seems to ignore the SET as does SQL2005 against a version 80 db.
January 13, 2009 at 9:05 am
It looks like incomplete code. I would have thought what was meant was something like:
DECLARE @fId INT;
SET @fId =42;
Of course with 2008 you can:
DECLARE @fId INT = 42;
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2009 at 9:20 am
Hi
I suspect it is incomplete and someone put SET in by mistake and left it there.
The strange thing it that SQL Server 2000 seems to ignore it and 2005 + will return a syntax error.
As it looks like the SET is a syntax error and now concern is this: I am about to update some servers and it looks like there will be code which currently runs, as 2000 is being flexible with syntax, but will now fail as 2005 calls it out.
I really don't want to go and read all the code in the database and the application looking for TSQL syntax errors!
Has anyone else had this sort of problem with a SQL 2005 upgrade - or are all your developers writing bug free code?
January 13, 2009 at 9:27 am
Seth Lynch (1/13/2009)
HiI suspect it is incomplete and someone put SET in by mistake and left it there.
The strange thing it that SQL Server 2000 seems to ignore it and 2005 + will return a syntax error.
As it looks like the SET is a syntax error and now concern is this: I am about to update some servers and it looks like there will be code which currently runs, as 2000 is being flexible with syntax, but will now fail as 2005 calls it out.
I really don't want to go and read all the code in the database and the application looking for TSQL syntax errors!
Has anyone else had this sort of problem with a SQL 2005 upgrade - or are all your developers writing bug free code?
Our developers write perfect code the first time, every time.
And, by the way, I have this lovely bridge for sale, it's in Brooklyn.
Have you tried running the SQL Server Upgrade Advisor against the database? That's available from Microsoft. That has identified most of the syntax problems that we've encountered.
There's another tool coming out from Scalability Experts under a Microsoft license called the Upgrade Assistant. It helps you in capturing a trace and playing it back against an upgraded version of your 2000 database. It works pretty well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2009 at 9:37 am
Thanks - That trace tool could be good.
The problem is that this app was developed over years and some SQL code is in the database (where it should be) and some is in the APP. So some of these problems will only show up when the code is run.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply