SQL: TOP 10 THINGS I HATE ABOUT SQL SERVER

Quite humorous, if you spend any time in SQL server: TOP 10 THINGS I HATE ABOUT SQL SERVER

SQL: More smalldatetime surprises

Earlier I mentioned some unexpected behavior with ISDATE(@foo) and CAST(@foo AS smalldatetime). This time the surprise came when looking at some existing code that compared a date to an empty string, expecting that @Date <> '' would yield false if the date had been set. Not so fast:

DECLARE @Date1 smalldatetime, @Date2 smalldatetime
SELECT @Date1 = '1/1/1900', @Date2 = ''
IF @Date1 = @Date2 
  PRINT 'funny'
ELSE 
  PRINT 'expected'

Results: funny << Statement Executed Successfully >>

RSVME

RSVME is a nifty, free and simple tool for creating questionaires like this one: Favorite .NET Language. I'll definitely use this next time I try to solicit answers from a group. It sure beats the pants off the voting buttons in Outlook

Politics: Looking ahead

A compilation of blogs covering the Race for 2008

Mobile Net Switch - The Network Connection Manager

Back when I had a company laptop that I'd occasionally bring home, I really wanted a product like this: Mobile Net Switch - The Network Connection Manager

"Mobile Net Switch is a network switch utility, also called a netswitcher or multinetwork manager and enables you to create multiple network profiles.

"The program has been designed to allow you to switch between several network profiles instantly, without needing to restart your computer. It also incorporates a professional yet easy to use interface and even allows you to enable none-admin users to configure network settings without technical knowledge.

"A (network) profile can be created for each location. With the click of a button the following settings can be modified instantly: IP Addressing (IP Address, Subnet mask, WINS, DNS, Default gateway), Internet Explorer and Firefox Proxy Server settings, Drive mappings, Internet Connection Firewall / Windows Firewall, ISA Server Firewall client 2000 and ISA Server Firewall client 2004, DNS Suffix, NetBIOS over TCP/IP, Internet Explorer Start Page, MAC Address, Dialup and VPN connection, Dialing rules, Default Printer, Mapi (Outlook) Profile, Time Zone, Outgoing SMTP mail server, Hosts file content, LMHosts file content, Sound volume, Sound mute, Wallpaper, Display Resolution, Numlock state, Restarting network cards, Enabling and disabling modems and more. "

Windows: XP Tweaks

Exceedingly poorly written, but some good tips: Windows XP Tweaks and TIPS - addict3d.org

ASP.NET: PageMethods

PageMethods by metaSapiens is a framework providing well-defined (or typed) URLs for your ASP.NET sites. Definitely worth looking into for my next ASP.NET project, whenever that might be.

Politics: Resorting to semantics

The trouble with being a senator - they keep track of what you say and do:

[...] Senate Majority Leader Bill Frist will arrange early next week for the presiding officer of the Senate to declare that it's unconstitutional to filibuster judicial nominees. There's just one catch: On March 8, 2000, Frist himself tried to filibuster a judicial nominee.

Earlier this week on the Senate floor, New York Sen. Chuck Schumer asked Frist about his vote in favor of filibustering Richard Paez, a judge Bill Clinton appointed to the U.S. Court of Appeals for the Ninth Circuit. Frist stammered through the beginnings of an explanation -- "Mr. President, the, in response, the Paez nomination ..." -- and then said he'd return to the Senate floor later to explain his filibuster vote further.

from Salon.com News | Resorting to semantics

Our Top Story Tonight: Crime in the News | Handout

You don't have to watch the Daily Show to know that the news media is getting a little too excited for its own good: Our Top Story Tonight: Crime in the News.

Murder reports - the fad of the 90's news media.

.NET CrossTabs versus SQL Server CrossTabs

I'll implement the C# version next time I have a need for cross tab/pivot table output: .NET CrossTabs versus SQL Server CrossTabs

Windows Media Player Bug & Simple Fix

My Auto Playlists in Windows Media Player 10 stopped working suddenly - I kept getting a "property was not found" error. Luckily Google came to the rescue - again: Windows Media Newsgroups - Error code C00D32CA in Windows Media Player

"in ur media library under All Audio, you'll find an untitled song in it, delete it away from your list and you Auto-playlist works.."

Politics: As lives get longer, benefits should come later

Amen: USATODAY.com - As lives get longer, benefits should come later

Social Security is such an easy thing to fix, relatively speaking. Input must equal output. Medicare on the other hand, is a huge mess that will require substantial changes in American society's attitudes towards health-care in general and end-of-life care in particular.

More useful stuff: Mailinator

Get a free, temporary email address that's created when email arrives: Mailinator

DontCrush.com - The Campaign to Save Electric Cars

Help protect the few remaining Electric cars currently available: DontCrush.com - The Campaign to Save Electric Cars

Useful Resource

dropload allows you to upload a file for another user to download within 7 days. Useful for getting around email attachment size restrictions. See the FAQ for more details.

SQL: DataDictionary System Function

The following lengthy SQL script will create a system function in SQL Server that returns a data dictionary for any database on the server when called like this:

select * from ::fn_datadictionary()

The output includes the Description of the objects.

More info on creating system functions can be found in the Dec 2001 edition of SQL Server Professional.

Now, for the SQL:

EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
USE master
go
CREATE FUNCTION system_function_schema.fn_datadictionary ()
RETURNS @DataDictionary TABLE (
 object_id int,
 object_name nvarchar(128),
 object_type nvarchar(128),
 column_order int NULL,
 column_name varchar(60) NULL,
 column_datatype varchar(20) NULL,
 column_length int NULL,
 object_description varchar(500) NULL
)
AS
BEGIN

 DECLARE @object_name nvarchar(128)
-------------------------
--Tables and Columns
-------------------------
 DECLARE table_cursor CURSOR FOR 
  SELECT name FROM sysobjects 
  WHERE type IN ('U') AND status > 1 ORDER BY name

 OPEN table_cursor
 FETCH NEXT FROM table_cursor INTO @object_name
 WHILE @@FETCH_STATUS = 0
 BEGIN
  --Tables
  INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Table',
   column_order = 0, column_name = NULL, 
   column_datatype = NULL, column_length = NULL,
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o 
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @object_name, null, default) e 
    ON o.name = e.objname
  WHERE o.name = @object_name

  --Columns
   INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Table Column',
    column_order = c.colorder, column_name = c.[name], 
   column_datatype = t.[name], column_length = c.[length],
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @object_name, N'column', null) e 
    ON c.name = e.objname
  WHERE o.name = @object_name
  ORDER BY c.colorder

    FETCH NEXT FROM table_cursor INTO @object_name
 END
 CLOSE table_cursor
 DEALLOCATE table_cursor

-------------------------
--Views and Columns
-------------------------
 DECLARE view_cursor CURSOR FOR 
  SELECT name FROM sysobjects 
  WHERE type IN ('V') AND status > 1 ORDER BY name

 OPEN view_cursor
 FETCH NEXT FROM view_cursor INTO @object_name
 WHILE @@FETCH_STATUS = 0
 BEGIN
  --Views
  INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'View',
   column_order = 0, column_name = NULL, 
   column_datatype = NULL, column_length = NULL,
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o 
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'view', @object_name, null, default) e 
    ON o.name = e.objname
  WHERE o.name = @object_name

  --Columns
   INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'View Column',
    column_order = c.colorder, column_name = c.[name], 
   column_datatype = t.[name], column_length = c.[length],
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'view', @object_name, N'column', null) e 
    ON c.name = e.objname
  WHERE o.name = @object_name
  ORDER BY c.colorder

    FETCH NEXT FROM view_cursor INTO @object_name
 END
 CLOSE view_cursor
 DEALLOCATE view_cursor


---------------------------
--Procedures and Parameters
---------------------------
 DECLARE proc_cursor CURSOR FOR 
  SELECT name FROM sysobjects 
  WHERE type IN ('P') AND status > 1 ORDER BY name

 OPEN proc_cursor
 FETCH NEXT FROM proc_cursor INTO @object_name
 WHILE @@FETCH_STATUS = 0
 BEGIN
  --Procedures
  INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Procedure',
   column_order = 0, column_name = NULL, 
   column_datatype = NULL, column_length = NULL,
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o 
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'procedure', @object_name, null, default) e 
    ON o.name = e.objname
  WHERE o.name = @object_name

  --Parameters
   INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Procedure Parameter',
    column_order = c.colorder, column_name = c.[name], 
   column_datatype = t.[name], column_length = c.[length],
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'procedure', @object_name, N'parameter', null) e 
    ON c.name = e.objname
  WHERE o.name = @object_name
   AND t.name <> 'sysname'
  ORDER BY c.colorder

    FETCH NEXT FROM proc_cursor INTO @object_name
 END
 CLOSE proc_cursor
 DEALLOCATE proc_cursor



---------------------------
--Functions and Parameters
---------------------------
 DECLARE func_cursor CURSOR FOR 
  SELECT name FROM sysobjects 
  WHERE type IN ('FN', 'TF', 'IF') AND status > 1 ORDER BY name

 OPEN func_cursor
 FETCH NEXT FROM func_cursor INTO @object_name
 WHILE @@FETCH_STATUS = 0
 BEGIN
  --Functions
  INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Function',
   column_order = 0, column_name = NULL, 
   column_datatype = NULL, column_length = NULL,
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o 
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'function', @object_name, null, default) e 
    ON o.name = e.objname
  WHERE o.name = @object_name

  --Parameters
   INSERT @DataDictionary 
  SELECT object_id = o.[id], object_name = o.[name], object_type = 'Function Parameter',
    column_order = c.colorder, 
   column_name = CASE WHEN c.[name] = '' THEN '' ELSE c.[name] END, 
   column_datatype = t.[name], column_length = c.[length],
    object_description = LTRIM(CAST(e.value AS varchar(500)))
  FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id INNER JOIN systypes t ON c.xtype = t.xtype
   LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'function', @object_name, N'parameter', null) e 
    ON c.name = e.objname
  WHERE o.name = @object_name
   AND t.name <> 'sysname'
  ORDER BY c.colorder

    FETCH NEXT FROM func_cursor INTO @object_name
 END
 CLOSE func_cursor
 DEALLOCATE func_cursor
 RETURN
END
GO
EXEC SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE
GO

tilde: XML Websites Without Any Programming

This sounds contradictory enough that I'll have to take a look: tilde: XML Websites Without Any Programming

SQL Using CROSS APPLY in SQL Server 2005

This is getting interesting: Microsoft SQL Server - Using CROSS APPLY in SQL Server 2005 - SQLTeam.com

AC Propulsion -- Plug-In HEV with Vehicle to Grid Power Flow

AC Propulsion converted a Jetta to a serial hybrid, with plug-in and vehicle-to-grid power flow capabilities. Interesting stuff: Business Information: AC Propulsion -- Plug-In HEV with Vehicle to Grid Power Flow

They also have a 3.6 sec 0-60 all-electric sports car with a 300 mile range. Why do we need hydrogen-based fuel cell vehicles?

Not the India I remember

Slashdot relays reports on India Launching World's First [Civilian] Stereo Imaging Satellite from a space station near Chennai (formerly Madras).

This is definitely not the same type of Indian transportation that I remember from 12 years ago, with its Auto Rickshaws, Ambassadors, Enfield Bullets, and Tata trucks...

How to read (and reset) the Service Engine Soon code on a Murano

Thanks to Tyler_Canada: NISSAN MURANO . ORG - Resetting SES light?

Wink - the latest winner of the Noble Piece Prize

Via Slashdot:   Wink is a free software tool for building Flash animations meant for tutorials, though it should also be able to create any other simple Flash presentation.

As the Slashdot blurb points out, Wink is the latest recipient of the Noble Piece Prize.

KCRW: Hitchhiker's Guide to the Galaxy

Now if only someone could bundle these into mp3 files: KCRW: Hitchhiker's Guide to the Galaxy

SQL: The myth of COUNT(FooID)

There is a popular ongoing myth about the performace benefit of specifying an ID when using the COUNT function in SQL. For example, Darrel Norton is normally on the ball about stuff, but here he is just wrong.

SELECT COUNT(FooID) FROM Foo

is not faster than

SELECT COUNT(*) FROM Foo

As the documentation states:

COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without eliminating duplicates. It counts each row separately, including rows that contain null values.

For later reading: Samsung - the Seoul Machine

Wired 13.05: Seoul Machine