609.5

From another conversation ...

"It reminds me of the handyman who caught a fish shaped like the letter L.

He, as it turns out, metacarple".

#604

One of the 1.3 million returned results from a google search of PINK LEMONADE will get you this ...

http://digg.com/videos/comedy/Horrific_Projectile_Vomit_Ensues_After_Drinking_Mysterious_Lemonade

In the old days, a 20MB harddrives setup as a fileshare would get loaded up with junk after a couple of months. It would be horribly disorganized where you couldn't find anything. No one would clean it out because they were afraid they would lose something important. Someone would finally just get another drive. Then the same thing would happen again.

Now its just more people messing it up. More harddrives. The internet should have some standards set that we all have to adhere to before it fills up with porn and puke.

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

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

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

#27

Misfits converge.

... think about it ...

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

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.