20071202

Less is more

This is the story of a man named Jed
A poor programmer that had been hit in the head
Then one day he did a query for a home
Did a top one and ended in Oklahom (a)

Thing is, this guy correctly figured that for every address on a street, there was only one possible number. But there could be more than one person registered at a household and he really only wants one of them. So he does

SELECT TOP 1 FROM Adresses WHERE Street = @a and Address = @b

Thing he forgot about was that there might possibly be a 123 Main Street in more than one city. In a different state.
They're still digging themselves out of the huge data corruption that this created.

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

20071116

Wait a minute

OK, Check this out. There are no errors in 2005 or 2008 even with that comma dangling off the end ...

CREATE TABLE [dbo].[AdvertiserSamCategory] (
[AdvertiserSAMID] [int]
IDENTITY (1, 1) NOT NULL ,
[AdvertiserID] [INT]
NOT NULL ,
[DomainLogin] [nvarchar] (50)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SamCategoryID] [INT] NOT NULL ,
)
ON [PRIMARY]


Guess you really don't need that definition

20071112

Where am I

Here is the Microsoft way to figure the version and servicepack levels

Declare @V varchar(10), @sp varchar(5), @ed varchar(32), @nm varchar(32)
SELECT @v=convert(varchar(10),SERVERPROPERTY('productversion'))
,@sp=convert(varchar(5),SERVERPROPERTY ('productlevel'))
,@ed=convert(varchar(32),SERVERPROPERTY ('edition'))
,@nm=convert(varchar(32),@@servername)
Print 'SERVER:' + @nm
Print 'Version ' + @v
Print 'Level ' + @sp
Print @ed

My local 2008 server for example produces the following:

SERVER:WEB-TESTMULE
Version 10.0.1049. <====Note the extra period! Level CTP Developer Edition

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'.

20071026

#27

Misfits converge.

... think about it ...

20071008

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.

20070912

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 :(

20070811

Where are you going with that?

This is the where clause I found when fixing a procedure one day...

FROM Rotation SR LEFT JOIN Break_Type BT ON BT.BT_ID = SR.BT_ID,
(select bandklocka, sp_id from Spot) S,
spot cg
where sr.rotationid = @CopyGroupRef
and sr.rotationid = cg.bandklocka
and s.bandklocka = sr.spotid
and sr.spotid = s.bandklocka
ORDER BY SR.STARTDATE

Now you may not know what a Bandklocka is, but I bet you can see what is off with its usage here.

License & copyright

Creative Commons License
StinkySQL weblog and any related pages, including the weblog's archives are licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.