Here are all the 36 numbers between 0 and 9000 that have a different LEN than DATALENGTH.
Number
Binary digits
...
DECLARE @Interfaces TABLE
(
RowID INT IDENTITY(0, 1),
Interface CHAR(38),
IP VARCHAR(15)
)
INSERT @Interfaces
(
Interface
)
EXEC master..xp_regenumkeys N'HKEY_LOCAL_MACHINE',
N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces'
DECLARE @RowID INT,
@IP VARCHAR(15),
@Key NVARCHAR(200)
SELECT @RowID = MAX(RowID)
FROM @Interfaces
WHILE @RowID >= 0
BEGIN
SELECT @Key = N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces\' + Interface
FROM @Interfaces
WHERE RowID = @RowID
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
@Key,
N'DhcpIPAddress',
@IP OUTPUT
IF @IP <> '0.0.0.0'
UPDATE @Interfaces
SET IP = @IP
WHERE RowID = @RowID
SET @RowID = @RowID - 1
END
SELECT IP
FROM @Interfaces
WHERE IP IS NOT NULL
I started out with typing
SELECT @@VERSION
and got the result as
Microsoft SQL Server 2005 - 9.00.3215.00 (Intel X86)
Dec 8 2007 18:51:32
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
So far so good. Now I created some sample data like this
-- Prepare sample data
DECLARE @Sample TABLE (RecNo TINYINT, ID TINYINT, EventDate SMALLDATETIME, OrderStatus VARCHAR(9))
INSERT @Sample
SELECT 1, 10, '7/5/2008', 'Opened' UNION ALL
SELECT 2, 11, '7/5/2008', 'Closed' UNION ALL
SELECT 3, 12, '7/5/2008', 'Closed' UNION ALL
SELECT 4, 13, '7/6/2008', 'Opened' UNION ALL
SELECT 4, 14, '7/6/2008', 'Opened' UNION ALL
SELECT 4, 15, '7/6/2008', 'Closed' UNION ALL
SELECT 1, 16, '7/7/2008',...
When Windows Server 2008 is more common, you will be surprised how the Firewall is blocking all versions of SQL Server when upgrading the OS.
Here are some links to remedy the problems
http://msdn.microsoft.com/en-us/library/cc646023(SQL.100).aspx
http://technet.microsoft.com/en-us/network/bb545423.aspx