But but but

Another SQL 2000 failure that seems to be OK anywhere else you try it.

-- Do something
GOGO
-- Do other stuff

Why that isn't an error in 2005 is a little curious

What's in a name?

IN everything but SQL2000 this is not a problem. Seems in 2000, the tableName does not get
an automatic name reference, such that the use of tableName.xxx results in a syntax error.


You must alias the table name even if it is the same name.


SELECT tableName.Col FROM tableName tableName

What was that name again?

In SQL 2005 this is OK


SELECT 1
FROM sysobjects (nolock) o
JOIN syscolumns (nolock) c ON o.id = c.id


But we build in SQL 2000 and it must be like this


SELECT 1
FROM sysobjects o (nolock)
JOIN syscolumns c (nolock) ON o.id = c.id


So unless you alias the table before the hint, you will get a syntax error in some cases.


Incorrect syntax near 'o'.

GO speedracer GO

PRINT 'Here is a test'
/*
Here is a place for a comment to
GO
*/


The commented GO statement within the /* */ still executes and as such will cause a syntax error. This is corrected in 2005 servers but you will have to watch out for it on current boxes.

Update August fifthe 2009
We got hit by this again so I did some further experiments. Turns out it is related to the OSQL/ISQLW/ISQL installed and not the server. You can run this same script from a machine with the 2005 tools installed and point to the 2000 server and there is no error.

Point is then, when you are testing for compatibility of your scripts, don't run it from the 2005 management studio. You will get a false positive.

Do AS I say

Try this on a SQL 7 or 2000 box


DECLARE @Fred AS TABLE (ID INT)


and what you will get is a syntax error. Another oddity that works fine once you move to SQL 2005 but that little AS in the declaration is a syntax error anywhere else. Seems most people do not run into this little gem because the AS is not required, and why would you type it if its not needed? That would be like using a LET assignment keyword :(