Flamed By Celko?

I found this funny pic of the man who gave us newsgroup gems like this for example: ">> Any suggestion is greatly appreciated. <<


EVERYTHING you are doing is TOTALLY wrong. You have just been cussed
out by one of the people who wrote this language. If you have brain
instead of an ego, you might want to listen.

This is a (bad) COBOL program written in SQL! There is so much
formatting done in SQL code! The bad news -- for me-- is that this
code is so awful I cannot use it in my next book as a bad example
because it is too proprietary! You could be famous!


Your code is so awful, you even use the "tbl-" prefixes to tell us you
have no idea about RDBMS! You keep converting dates to strings because
you are writing COBOL in SQL and want strings!


Why do your have "CREATE TABLE #tmp_sched" when view would work?
Answer: because magnetic tape files have to be materialized


Why do you spit on ISO-11179 rules and use a "tbl-" prefix? Because
you know only BASIC programming, which needs the prefixes for the one
pass compiler.


You write SQL with flags like it was 1950's Assembly language! Flags
in SQL!! Ghod Damn!! Varying length identifiers!? And I loved the way
spit on ANSI/ISO Standards with "SET QUOTED_IDENTIFIER OFF", etc.?


You need help you cannot get on a newsgroup.
"

The Chapter On SQL Server 2005 Service Pack 2 Is Closed

This is the final word on SQL Server 2005 Service Pack 2 (at least from me, I promise)

It is all explained here: http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx


So as you can see from that link, after applying the critical updates you should be on either build 9.00.3054 or 9.00.3159

Does Anyone Use ~ (Bitwise NOT)?

I have Used ¦ (Bitwise OR) and & (Bitwise AND) but I have never used ~ (Bitwise NOT)
Lets' see what it does

SELECT ~170
this returns -171, OK Fair enough.

SELECT ~convert(tinyint,170)
this returns 85, mmmmmmm

Here is some more interesting stuff

DECLARE @i int, @i2 tinyint,@i3 smallint, @i4 bigint
SELECT @i =170,@i2 = 170,@i3 =170,@i4 = 170

SELECT ~@i int, ~@i2 tinyint,~@i3 smallint, ~@i4 bigint

output
-171 85 -171 -171

So for the tinyint it is 85, for the other datatypes it is -171

What does BOL say?

Remarks
The bitwise ~ operator performs a bitwise logical NOT for the expression, taking each corresponding bit. The bits in the result are set to 1 if one bit (for the current bit being resolved) in expression has a value of 0; otherwise, the bit in the result is cleared to a value of 1.

The ~ bitwise operator can be used only on columns of the integer data type category.



Important
When performing any kind of bitwise operation, the storage length of the expression used in the bitwise operation is important. It is recommended that you use the same number of bytes when storing values. For example, storing the decimal value of 5 as a tinyint, smallint, or int produces a value stored with different numbers of bytes. tinyint stores data using 1 byte, smallint stores data using 2 bytes, and int stores data using 4 bytes. Therefore, performing a bitwise operation on an int decimal value can produce different results as compared to a direct binary or hexidecimal translation, especially when the ~ (bitwise NOT) operator is used. The bitwise NOT operation may occur on a variable of a shorter length that, when converted to a longer data type variable, may not have the bits in the upper 8 bits set to the expected value. It is recommended that you convert the smaller data type variable to the larger data type, and then perform the NOT operation on the result.

There you have it.

Hilarious Paris Hilton Subway Ad

What else needs to be said. Poor Tinkerbell or whatever the name of that dog is.

Script Multiple Procs With Create And Drop Statements In SQL Server 2005

In SQL Server 2005 Management studio you can not script out multiple procedures with a create and drop statement
This is what you have to do:
1. Open you favorite database in the management studio.
2. Make sure the summary window is visible. (Press F7)
3. In the summary window, double click databases.
4. Double click 'YourDatabase'
5. Double Click 'Programability'
6. Double Click 'Stored Procedures'
7. Select the stored procedures you want to script. (using CTRL/SHIFT clicking).
8. Right click -> Script Stored Procedures As -> Drop -> New Query Editor Window.

Repeat step 8 for the Create.

Who wants to do that? Not me.
Here is a pure T-SQL solution, I didn't feel like using SMO. I can just run this code, copy and paste the output in a new window and I am done.

This code takes care of schema's it also grabs just procedures and only the ones that are created by users (OBJECTPROPERTY 'IsMSShipped' takes care of that)



USE AdventureWorks
GO

SET NOCOUNT ON

CREATE TABLE #ProcHolder (ID int identity,ProcName VARCHAR(8000), ProcDefinition VARCHAR(MAX))

INSERT #ProcHolder
SELECT QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME),OBJECT_DEFINITION(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME)))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME)),'IsMSShipped') = 0
--SELECT * FROM #ProcHolder --if you want to test


DECLARE @LoopID int,@MaxLoopID int,@ProcDefinition VARCHAR(MAX),@ProcName VARCHAR(8000)
SELECT @LoopID =1,@MaxLoopID = MAX(id) FROM #ProcHolder

WHILE @LoopID < @MaxLoopID
BEGIN
SELECT @ProcDefinition = ProcDefinition,@ProcName =ProcName
FROM #ProcHolder
WHERE id = @LoopID

PRINT 'if exists (select * from dbo.sysobjects where id = OBJECT_ID(N''' + @ProcName + ''') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure ' + @ProcName
PRINT 'GO'
PRINT ''
PRINT ''

PRINT @ProcDefinition
PRINT 'GO'
PRINT ''
PRINT ''

SET @LoopID = @LoopID +1
END



DROP TABLE #ProcHolder


That is it, let me know what you think

Sopranos: Bobby Baccalla beats the crap out of Tony Soprano

That was a great episode last night, I am glad to see that this season started much better than last season. I watched the last episode of last season on Saturday night and I was quite excited to start watching the final season. Bobby Baccalla beat the crap out of Tony Soprano who insulted Janice after everyone got drunk while playing monopoly. Did you know that my wife’s family does the same thing with that extra rule that they made up to increase the pot?

Janice is one of those people you just cannot stand; I hate her with a passion. They way she acts towards the baby sitter is just plain wrong. BTW it was Tony’s birthday yesterday and he got golf clubs as a present among other things ;-). Yesterday was also my birthday but I did not get any golf clubs ;-(. But then again I don’t play golf or will ever play golf since it seems so boring to me. What is the point? Hit a ball then walk for 15 minutes and hit another ball, golf is not a sport I don’t care what they say otherwise.
Back to the Sopranos, I hope the other episodes are as good as the one from yesterday. Remember last season? The first episode was good; Tony got shot by Uncle Junior. Then the next episode went into the coma/dream nonsense and it was all down the hill after that.

The Real Microsoft Vista Marketing

Got this in an email today, pretty funny

Two New Hotfixes Available To Fix The Maintenance Plan Issues Caused By SQL Server 2005 SP2

Run the following query

SELECT @@VERSION.

If the SQL Server 2005 version is between 3042 and 3053, download build 3054 from http://support.microsoft.com/kb/934458

If the SQL Server 2005 version is between 3150 and 3158, download build 3159 from http://support.microsoft.com/kb/934459

April 8th Is Almost Here

Why should you care? Here are 4 reasons

1) My Birthday
2) Final season of Sopranos starts
3) New season of Entourage starts
4) Easter


So finally the Sopranos will end ;-( I must admit that the last season wasn’t that great, the episode in which Tony had that crazy dream with that suitcase was just horrible. Watch out for the ducks in the last episode (remember the ducks in the first episode? They will be back and we've come full circle with the show)

I did not start watching Entourage until last season, since then I have watched all the episodes (thank you Comcast on demand). Jeremy Piven (Ari Gold) and Kevin Dillon (Johnny "Drama" Chase) are my favorite characters.


Easter, oh yes the day that I have to go to Church with my wife (I made a wedding day promise). My older son will have a lot of fun with all the eggs/chocolates and gifts that the Easter Bunny will bring

It is also my birthday on April 8th, I will be getting closer to midlife crisis. I will turn 37 that day, wait isn’t 37 the new 27?

SQL Server 2007 Beta1 CTP Released

Microsoft released SQL Server 2007 Beta1 CTP earlier today. You can download it here
A ton of new things, support for spatial data. New functions to deal with numeric data (IsInt, IsBigInt, IsSmallInt). Also new is the addition of Natural Join, a bunch of new dynamic management views that make it easier to prevent deadlocks. Another cool thing is the addition of the new BigDateTime datatype, this makes it possible to enter dates before 1753, you can actually go back to -3000. That is such a big plus for databases that deal with data that's older than 1753. What are you waiting for? Download it here now

SQL Server 2005 Books Online Scoped Search Provided By search.live.com

Check out the new Books On Line search provided by live.com. The link below provides a scoped search of Books Online. It will only return entries for the online version of Books On Line.

http://search.live.com/macros/sql_server_user_education/booksonline

TEMPDB and SQL Server 2005 Tip

When upgrading to SQL server 2005 you already know that you have to rebuild your indexes and update the statistics. In addition to that, create 1 data file per CPU core for the TEMPDB (Dual core counts as 2 CPUs; logical procs (hyperthreading) do not)
This will make a big difference, SQL Server 2005 uses the tempdb different than 2000, by adding more files you will see a dramatic performance improvement for big queries

Our queries on SQL Server 2005 ran slower than on 2000, by adding more files the queries are twice as fast than on the SQL Server 2000 box now

This behaviour is documented here: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

Look at number 7 (Consider configuration of TEMPDB database)
and number 8 (Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads. )

A list of SQL Server 2005 Whitepapers and SQL Server 2000 Whitepapers

Kimberly Tripp compiled a nice list of all the top whitepapers available for SQL Server 2000 and SQL server 2005. Some of them are written by Bob Beauchemin and some of them are written by Kimberly herself. The number of available whitepapers for SQL server 2005 is 29 at this moment.

Get them all here: http://www.sqlskills.com/whitepapers.asp

How Not To Do Search Engine Optimization

Just ask Alex Chiu. This is what he wrote:

" Google controls 50% of the world's searches. This famous website is so controversial that it has been banned by the most popular search engine in the world 'Google'. That's right. You cannot find alexchiu.com in Google system. Some very important people don't want you to know about Alex Chiu. Alex Chiu is on more than 30 TV interviews, 250 radio interviews, and in business ever since 1996. Yet AlexChiu.com cannot show up on Google? "

Why is that? Very simple, take a look at his site http://alexchiu.com/
At the bottom of the page you will find a text area with over a thousand keywords, some of these keywords are:
System Heart Aorta Arteries Arterioles Capillaries Venules Veins Vena cava Pulmonaryarteries Lungs veins Blood Digestive Mouth Pharynx Esophagus Stomach Pancreas Gallbladder

This list goes on and on.

End result? Banned by Google.

SQL Server 2005 SP2 Has Added The OBJECT_SCHEMA_NAME Function, OBJECT_NAME Has Been Enhanced

SQL Server 2005 SP2 has an important enhancement to the OBJECT_NAME metadata function and a new OBJECT_SCHEMA_NAME metadata function. Because you pass object_id, and database_id to the OBJECT_NAME function it is not needed anymore to write dynamic SQL to get multi DB results.

The OBJECT_SCHEMA_NAME metadata function can be used to return the schema name of a schema-scoped object like a table or a view by specifying the object identifier and optional database identifier.

Read more about these 2 functions here, there is also SQL code available that show you how to use the functions on the site.

30 + Tools and Hacks for Gmail

This makeuseof site has some cool Gmail hacks and tools. There are FireFox Extensions, GreaseMonkey scripts,Desktop APPS & Web TOOLS and plain old tips and hacks. The ones that I like the best are Gmail attachment icons and label colors. below is an image of all these things in action

Link: http://www.makeuseof.com/tag/gmail-craze-30-tools-to-make-your-gmail-better/

Microsoft SQL Server Samples and Community Projects On CodePlex

Codeplex is the project hosting site for Microsoft SQL Server Samples and Community Projects. This portal page for SQL Server on Codeplex catalogs samples included with Product Releases, samples from the community, and independent SQL Server Community projects.

Link: http://www.codeplex.com/SqlServerSamples

Make sure you check out the DBScript Utility

The Ten Most Asked SQL Server Related Programming Questions In Newsgroups

I have been participating in newsgroups/forums for about five years now and lately I have noticed that at least 60% of the questions are the same ten questions.
That is why I will create a blogpost that lists these ten questions and also provides an answer for them. I think that to write this will take me about a week.

Here is what I have so far

1 selecting only current day/yesterday from a table
2 adding days/weeks/years/months to dates
3 splitting string values
4 select * from one table that doesn't exist in another table
5 getting all rows from one table and only the latest from the child table
6 getting all characters until some character (charindex + left)
7 NULL stuff ( a bunch of stuff here dealing with nulls)
8 Row values to column (PIVOT)
9 Show 0001 as 1 and show 1 as 0001
10 CASE and checking for NULLs

I guess I could have added date display formatting. Did I miss any other obvious questions?

SQL Server Speaks Almost As Many Languages As I Do

Saw this error while running a job

Error Code: 4860
Cannot bulk load. The file "%ls" does not exist.
Massenladen ist nicht möglich. Die Datei "%1!" ist nicht vorhanden.
Chargement en bloc impossible. Le fichier "%1!" n'existe pas.
???????????????? "%1!" ???????? No se puede realizar la carga masiva......


This is the first time that I have seen a multi-language error. It appears that SQL Server 2005 is a multi-language 'speaker'.
So we have 4 languages in this error

English
Cannot bulk load. The file "%ls" does not exist.

German
Massenladen ist nicht möglich. Die Datei "%1!" ist nicht vorhanden.

French
Chargement en bloc impossible. Le fichier "%1!" n'existe pas.

Spanish
???????????????? "%1!" ???????? No se puede realizar la carga masiva (don't know what the deal is with those question marks, probabaly a missing font)


So I speak 2 of these languages and 2.5 other languages and also a dialect. But I can curse in many more ;-)

Microsoft Joins OpenAjax

OpenAjax Alliance, an open industry collaboration dedicated to developing and expanding Ajax, today announced that its membership has grown to 72 with the addition of Microsoft Corporation and 30 other companies.

"Microsoft is joining the OpenAJAX Alliance to collaborate with other industry leaders to help evolve AJAX-style development by ensuring a high degree of interoperability," said Keith Smith, group product manager of the Core Web Platform & Tools to UX Web/Client Platform & Tools team at Microsoft Corp. "By joining OpenAJAX, Microsoft is continuing its commitment to empower Web developers with technology that works cross-browser and cross-platform."

The newest OpenAjax Alliance members include: 24SevenOffice, ActiveGrid, ActiveState, Appeon, Aptana, Arimaan Global Consulting, Custom Credit Systems (Thinwire), ESRI, Getahead (DWR), Global Computer Enterprises, GoETC, Helmi Technologies, HR-XML, iPolipo, Isomorphic Software, JSSL, Lightstreamer, Microsoft, MobileAware, NetScript Technologies, OpenSpot, OpenSymphony (OpenQA), OpSource, OS3.IT, Redmonk, Tealeaf Technology, Teleca Mobile, Transmend, Visible Measures, Visual WebGui and Volantis Systems.

The Alliance is also announcing that the following members have all been awarded OpenAjax Interoperability certificates: Apache XAP, Dojo Foundation, ICEsoft, ILOG, Isomorphic, IT Mill, Lightstreamer, Open Link, Open Spot, Nexaweb, Software AG and TIBCO. The interoperability certificates represent progress by both OpenAjax Alliance and its members towards defining and achieving industry support for OpenAjax Conformance.

Read the press release here: http://www.marketwire.com/mw/release_html_b1?release_id=228535