[T-SQL] How to get length of TEXT field

To calculate the lenght of a TEXT field the LEN function used to calculate the length of VARCHAR fields won't work. 

You need to use the DATALENGTH T-SQL function:

SELECT DATALENGTH(myTextField) AS lengthOfMyTextField

[SQLServer] Error 3205: too many devices specified for backup or restore only 64 are allowed

Problem: restoring a backup on SQLServer I get the following error:
[Error 3205: too many devices specified for backup or restore only 64 are allowed]

Solution: you're probably trying to restore a SQL2005 backup on a SQL2000 instance. There's no backward compatibility for backup operations bewteen SQL2005 and SQL2000. Installing SQL2005 will make the error go away (I know - it sucks).

I recently came across the cryptic error message in subject. At first I was puzzled - then google helped me understand the problem was not my lack of sweet SQLServer skills...

Learning SQL with Ganesh - Part 1 of ?

I found (and shared) this desperate help request yesterday on my google reader - I felt a burning desire to help the poor guy and I immediately followed the link to StackOverflow, but folks over there already edited the heck out of the original question.

Anyway this is what I'do to find AND delete duplicate rows on a table (with Identity):

DELETE
FROM    MyTable
WHERE   ColumnID NOT IN
(    SELECT   MIN(ColumnID)
FROM MyTable
GROUP BY    Column1, Column2, ..., ColumnN )

If you don't have an identity on the table you can add it then drop it when you're done.

Anyway - I obviously up-modded the guy and went ahead to have look at other questions from him and - guess what - I found a pair of pearls (the previous one was pretty OK besides the original title):

Probably weirdest SQL questions ever asked, but anyway, even if I HATE SQL, I tried to find answers anyway ('cause that's human nature - I guess).

To get the top 10 rows without sorting I'd probably do some rocket science:

select top 10 *
from MyTable
-- duh!

but then, if random order is required (?), maybe something like:

select top 10 *
from MyTable
order by NEWID()

To get the last 5 rows without sorting I'd rather go with:

select top 5 *
from MyTable
order by ColumnID desc

But what if you don't have an ID? What does without sorting mean? In doubt, I'd probably do something like the following:

select top 5 *
from ( select *
 from MyTable
 order by newId() desc)

Which is some crazy shit and without doubt doesn't make any sense.

Fags and The Military Got My .NET

I use to read Monsters Got My .NET posts. I subscribed to the feed on google reader and I very much enjoy the concept behind this blog, being a fan of horror movies and a programmer.

Posts on this blog are usually quite verbose - it usually takes me a while to figure out what the heck they talk about (maybe 'cause I am a bit slow), but the content is quite good and honestly I can't wait to see the horror movie mini-poster on the next post. 

I particularly enjoyed a recent post (it was pretty straightforward and enjoyable compared to the intellectualoid standard) and decided to leave a comment that went something like:

This is a very nice post, short enough to be readable and useless enough for me to be able to enjoy it.

I really like the concept behind this site - I'll probably start another blog and call it "Fags and The Military Got My .NET", instead of horror movies I'll post military posters with subtle homosexual hints.

The author didn't appreciate my comment - meant to be a joke - and canceled it mentioning in another comment that some retard posted an inappropriate comment forcing him to moderate his blog.

At this point I had no other chance left than creating Fags and the Military Got My .NET for real:

How to get and format date in a bat file

Sooner or later everyone needs to mess up with some batch file Voodoo for some reason - often to pipe the output of some console application into some kind of log file. At this point you'll probably want to put a timestamp on the log file - but you need to reformat the standard date to elminate slashes and stuff. This is how you do it (depending on your operating system):

REM US Operating System: set yyyymmdd=%date:~10%%date:~4,2%%date:~7,2%
REM EU Operating System: set yyyymmdd=%date:~6,4%%date:~3,2%%date:~0,2%

This formats the date into a variable called yyyymmdd (pretty self-explanatory, uh), which you can use later in our lame script in you log file name (or whatever you need it for - In this case I am piping stuff into a log file):

REM EU Operating System
set yyyymmdd=%date:~6,4%%date:~3,2%%date:~0,2%

ECHO myConsoleApp output: >> myLog_%yyyymmdd%.txt
myConsoleApp.exe >> myLog_%yyyymmdd%.txt

This stuff sucks - but I couldn't find the date formatting thing - pretty cryptic, uh? - anywhere on the web (I stole it off a collegue of mine who's kind of a batch files guru) so it might be useful to people.

How I feel about SQL

I really tried hard to make friends with SQL, I even got a few books, and O'Reilly ones (they all sucked, but - hey - it's SQL we're talking about). I eventually came to cope with my feelings - if you gotta hate something, SQL ain't too bad a pick. Ain't too bad for poor SQL either, there's a whole lot of awesome indian dudes who love it to death. 

Maybe it's because I've been too lazy to install intellisense plugins for SQL Management Studio so far, maybe because of the cryptic error messages ("there's a fuck up around line 345" - jeez, Javascript is easier to debug) or maybe just because that's the way it is, you gotta hate it. 

P.S. There's been a lot going on lately - mostly going crazy with a solution that builds fine manually but fails to build through CruiseControl.NET - but I've been a lazy ass and didn't post anything about it. You can read about it here and here for now.

New Warnings World Record?

Just wondering if this might be the Visual Studio Warnings World Record:

1493 is a pretty darn good number - I am getting it from a monster solution composed by something like 65 projects (.NET, COM, MFC/C++ ... a bit of everything).

Tried to Google up "Visual Studio Warnings World Record", but no luck. It'd be nice to make it to the Guinness Book of World Records for something so useless. 

Let that boy copy paste (with SQL2005 and Excel)

I heard Papa tell Mama

Let that boy copy paste from SQLServer2005 to Excel
It's in him

And it got to come out

John Lee Hooker - Boogie Chillen (1948)

Being a software engineer - I pretty much hate messing with DBs.

Nonetheless, more often than not, even the brightest designers - such as myself - are called to get their hands dirty with some SQL or moving some data around when the team's DB guy (usually indian) is badly sick at home.

I remember how painful it was the first time I had to understand with SQL2000 how to export to excel a simple resultset from a select - must have wasted a full afternoon of frustration trying to do that a few years back. 

A couple of months ago I was shocked in discovering that with SQL2005 you can just select all and copy/paste a resultset to excel. This means that if you have to import/export some records you don't need to mess with import/export wizards and all that comes after. Shrinking to 3 clicks (select all - copy - paste) a 5 minutes painful procedure. 

Forget about TRY CATCH, CLR support, and all that fancy stuff, copy/paste to/from excel is my favourite SQL2005 new feature (ok - this sounds something like 3 years late, but it's not what this post is about).

DB purists might not like it but copy/paste is "de way 2 go".