The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools

Mark Russinovich the guy who revealed to the world that Sony CDs had that nasty rootkit installed has made available a single suite of tools for download. These tools are a must have for every single windows developer.

The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools. This file contains the individual troubleshooting tools and help files. It does not contain non-troubleshooting tools like the BSOD Screen Saver or NotMyFault.

Download it here

Here is what is included:

• AccessChk

v2.0 (11/1/2006)
This tool shows you the accesses the user or group you specify has to files, Registry keys or Windows services.

• AccessEnum

v1.32 (11/1/2006)
This simple yet powerful security tool shows you who has what access to directories, files and Registry keys on your systems. Use it to find holes in your permissions.

• AdRestore

v1.1 (11/1/2006)
Undelete Server 2003 Active Directory objects

• Autologon

v2.10 (11/1/2006)
Bypass password screen during logon.

• Autoruns

v8.61 (1/22/2007)
See what programs are configured to startup automatically when your system boots and you login. Autoruns also shows you the full list of Registry and file locations where applications can configure auto-start settings.

• BgInfo

v4.0 (11/1/2006)
This fully-configurable program automatically generates desktop backgrounds that include important information about the system including IP addresses, computer name, network adapters, and more.

• BlueScreen

v3.2 (11/1/2006)
This screen saver not only accurately simulates Blue Screens, but simulated reboots as well (complete with CHKDSK), and works on Windows NT 4, Windows 2000, Windows XP, Server 2003 and Windows 9x.

[EDIT]
BlueScreen is NOT part of the suite, you can download it here
[/EDIT]

• CacheSet

v1.0 (11/1/2006)
CacheSet is a program that allows you to control the Cache Manager's working set size using functions provided by NT. It's compatible with all versions of NT and full source code is provided.

• ClockRes

v1.0 (11/1/2006)
View the resolution of the system clock, which is also the maximum timer resolution

• Contig

v1.53 (11/1/2006)
Wish you could quickly defragment your frequently used files? Use Contig to optimize individual files, or to create new files that are contiguous.

• Ctrl2cap

v2.0 (11/1/2006)
This is a kernel-mode driver that demonstrates keyboard input filtering just above the keyboard class driver in order to turn caps-locks into control keys. Filtering at this level allows conversion and hiding of keys before NT even "sees" them. Full source is included. Ctrl2cap also shows how to use NtDisplayString() to print messages to the initialization blue-screen.

• DebugView

v4.64 (1/8/2007)
Another first from Sysinternals: This program intercepts calls made to DbgPrint by device drivers and OutputDebugString made by Win32 programs. It allows for viewing and recording of debug session output on your local machine or across the Internet without an active debugger.

• DiskExt

v1.0 (11/1/2006)
Display volume disk-mappings

• DiskView

v2.21 (11/1/2006)
Graphical disk sector utility

• Diskmon

v2.01 (11/1/2006)
This utility captures all hard disk activity or acts like a software disk activity light in your system tray.

• Du

v1.31 (11/1/2006)
View disk usage by directory

• EFSDump

v1.02 (11/1/2006)
View information for encrypted files

• Filemon

v7.04 (11/1/2006)
This monitoring tool lets you see all file system activity in real-time.

• Handle

v3.20 (11/1/2006)
This handy command-line utility will show you what files are open by which processes, and much more.

• Hex2dec

v1.0 (11/1/2006)
Convert hex numbers to decimal and vice versa.

• Junction

v1.04 (11/1/2006)
Create Win2K NTFS symbolic links

• LDMDump

v1.02 (11/1/2006)
Dump the contents of the Logical Disk Manager's on-disk database, which describes the partitioning of Windows 2000 Dynamic disks.

• ListDLLs

v2.25 (11/1/2006)
List all the DLLs that are currently loaded, including where they are loaded and their version numbers. Version 2.0 prints the full path names of loaded modules.

• LiveKd

v3.0 (11/1/2006)
Use Microsoft kernel debuggers to examine a live system.

• LoadOrder

v1.0 (11/1/2006)
See the order in which devices are loaded on your WinNT/2K system

• MoveFile

v1.0 (11/1/2006)
Allows you to schedule move and delete commands for the next reboot.

• LogonSessions

v1.1 (11/1/2006)
List the active logon sessions on a system.

• NewSID

v4.10 (11/1/2006)
Learn about the computer SID problem everybody has been talking about and get a free computer SID changer, NewSID, complete with full source code.

• NTFSInfo

v1.0 (11/1/2006)
Use NTFSInfo to see detailed information about NTFS volumes, including the size and location of the Master File Table (MFT) and MFT-zone, as well as the sizes of the NTFS meta-data files.

• PageDefrag

v2.32 (11/1/2006)
Defragment your paging files and Registry hives!

• PendMoves

v1.1 (11/1/2006)
Enumerate the list of file rename and delete commands that will be executed the next boot

• Portmon

v3.02 (11/1/2006)
Monitor serial and parallel port activity with this advanced monitoring tool. It knows about all standard serial and parallel IOCTLs and even shows you a portion of the data being sent and received. Version 3.x has powerful new UI enhancements and advanced filtering capabilities.

• Process Explorer

v10.21 (11/1/2006)
Find out what files, registry keys and other objects processes have open, which DLLs they have loaded, and more. This uniquely powerful utility will even show you who owns each process.

• Process Monitor

v1.01 (11/9/2006)
Monitor file system, Registry, process, thread and DLL activity in real-time.

• ProcFeatures

v1.10 (11/1/2006)
This applet reports processor and Windows support for Physical Address Extensions and No Execute buffer overflow protection.

• PsExec

v1.80 (2/12/2007)
Execute processes with limited-user rights.

• PsFile

v1.02 (12/4/2006)
See what files are opened remotely.

• PsGetSid

v1.43 (12/4/2006)
Displays the SID of a computer or a user.

• PsInfo

v1.74 (12/4/2006)
Obtain information about a system.

• PsKill

v1.12 (12/4/2006)
Terminate local or remote processes.

• PsList

v1.28 (12/4/2006)
Show information about processes and threads.

• PsLoggedOn

v1.33 (12/4/2006)
Show users logged on to a system

• PsLogList

v2.64 (12/4/2006)
Dump event log records.

• PsPasswd

v1.22 (12/4/2006)
Changes account passwords.

• PsService

v2.21 (12/4/2006)
View and control services.

• PsShutdown

v2.52 (12/4/2006)
Shuts down and optionally reboots a computer.

• PsSuspend

v1.06 (12/4/2006)
Suspend and resume processes.

• PsTools

v2.43 (2/12/2007)
The PsTools suite includes command-line utilities for listing the processes running on local or remote computers, running processes remotely, rebooting computers, dumping event logs, and more.

• RegDelNull

v1.10 (11/1/2006)
Scan for and delete Registry keys that contain embedded null-characters that are otherwise undeleteable by standard Registry-editing tools.

• RegHide

v1.0 (11/1/2006)
Creates a key called "HKEY_LOCAL_MACHINE\Software\Sysinternals\Can't touch me!\0" using the Native API, and inside this key it creates a value.

• Regjump

v1.01 (11/1/2006)
Jump to the registry path you specify in Regedit.

• Regmon

v7.04 (11/1/2006)
This monitoring tool lets you see all Registry activity in real-time.

• RootkitRevealer

v1.71 (11/1/2006)
Scan your system for rootkit-based malware

• SDelete

v1.51 (11/1/2006)
Securely overwrite your sensitive files and cleanse your free space of previously deleted files using this DoD-compliant secure delete program. Complete source code is included.

• ShareEnum

v1.6 (11/1/2006)
Scan file shares on your network and view their security settings to close security holes.

• Sigcheck

v1.30 (11/1/2006)
Dump file version information and verify that images on your system are digitally signed.

• Streams

v1.53 (11/1/2006)
Reveal NTFS alternate streams

• Strings

v2.30 (11/1/2006)
Search for ANSI and UNICODE strings in binaryimages.

• Sync

v2.0 (11/1/2006)
Flush cached data to disk

• TCPView

v2.40 (11/1/2006)
Active socket command-line viewer.

• VolumeId

v2.0 (11/1/2006)
Set Volume ID of FAT or NTFS drives

• Whois

v1.01 (11/1/2006)
See who owns an Internet address.

• Winobj

v2.15 (11/1/2006)
The ultimate Object Manager namespace viewer is here.

• ZoomIt

v1.21 (1/19/2007)
Presentation utility for zooming and drawing on the screen.

Microsoft .NET Micro Framework Is Now Available

Today at Embedded World 2007, Microsoft Corp. announced the availability of the software development kit (SDK) for the Microsoft® .NET Micro Framework. With its ability to work seamlessly with Visual Studio®, the .NET Micro Framework extends the power of Microsoft’s embedded offerings into the realm of smaller, less expensive and more resource-constrained devices.

“The .NET Micro Framework was built from the ground up as a .NET solution for small embedded devices,” said Colin Miller, director of the .NET Micro Framework at Microsoft. “It brings the reliability and efficiency of the .NET environment to a new set of applications such as home automation systems, industrial sensors, retail displays and healthcare monitors. Development on this platform works seamlessly with the same tools that are used throughout the Microsoft family of platforms. This decreases the distinction between embedded application development and other application development tasks and helps reduce the cost and risks of these projects.”

“The .NET Micro Framework is a proven platform that opens up a new area of embedded development and adds to the momentum of Microsoft embedded technologies,” said Pieter Knook, senior vice president of the Mobile and Embedded Devices Division at Microsoft.

The .NET Micro Framework SDK enables developers to take full advantage of the C# development language and the rich development and debugging experience that Visual Studio provides. In addition, the SDK offers user-extensible hardware emulation and seamless, graphical debugging of emulated and real hardware to deliver robust solutions in less time than ever before.

The .NET Micro Framework SDK not only works seamlessly with Visual Studio and offers an extensible emulator, but is also supported by a number of hardware platforms based on the ARM7 and ARM9 processor cores. The framework also enables device developers to connect these hardware platforms to virtually any peripheral hardware through industry-standard communication connections and custom-managed drivers.

Those interested in receiving a copy of the SDK for the .NET Micro Framework can visit http://msdn.microsoft.com/embedded/netmf. A minimum of 256 KB of RAM and 512 KB of flash ROM is required for development and deployment.

Partners Unveil Support and Offerings for .NET Micro Framework

Also at Embedded World, Digi International Inc. revealed plans for a preview release of the Digi Connect ME Development Kit for Microsoft .NET Micro Framework. The Digi Connect ME includes support for Ethernet networking, a serial port and general purpose input/output (GPIO) signals. It is the first solution available for .NET Micro Framework to support Ethernet networking. The kit, priced at $299 (U.S.), is available now from Digi’s online store (http://www.digistoreonline.com/gsastore.asp) and through its global network of distribution partners.

EmbeddedFusion, which delivers integrated hardware and software core solutions for embedded systems developers, announced the Meridian CPU, which is a core CPU module that incorporates a Freescale i.MXS processor, RAM, Flash and the .NET Micro Framework. To further assist developers in learning how the .NET Micro Framework is applicable in various embedded scenarios, EmbeddedFusion also created the Tahoe development platform, which enables experimentation and exploration of the .NET Micro Framework right out of the box.

Freescale also introduced a development kit for the .NET Micro Framework to allow customers to deliver differentiated solutions in the marketplace with ARM9 performance at very low power.

“Our expectations for .NET Micro Framework are high, so we continue to add features to enable OEMs, ODMs and others to create a new class of smaller, cost-efficient devices, or add Windows SideShow™ connected wirelessly to existing consumer devices,” said Brad Hale, manager of product management for Freescale’s multimedia applications division.

In addition, Rhode Consulting, a specialist in Microsoft Windows Embedded technologies, announced the availability of the FlexiDis Evaluation Kit with the .NET Micro Framework installed. The FlexiDis platform uses Atmel ARM7 and ARM9 processor cores with speeds of up to 180 MHz. The combination of these speeds, up to 16 MB of flash and SDRAM memory, and a 2.2-inch QVGA display makes the FlexiDis display a component of choice for various kinds of industrial applications in which an embedded HMI or visualization solution is required.

System Requirements for Developing on the .NET Micro Framework:

• Microsoft Windows® XP, Microsoft Windows Vista or Microsoft Windows Server® 2003

• Microsoft Visual Studio 2005 Standard Edition or greater



About .NET Micro Framework

The .NET Micro Framework grew out of the Smart Personal Objects Technology (SPOT) initiative at Microsoft. This framework is a natural extension of Microsoft’s offerings for creating embedded systems and provides an easy-to-use solution for this type of development. Though it is used on very small devices, the Microsoft .NET Micro Framework provides a managed code environment that brings a strong degree of efficiency and reliability to the realm of embedded software development. More information can be found at http://msdn.microsoft.com/embedded/netmf

Ten SQL Server Functions That You Hardly Use But Should

Below are 10 SQL Server functions that are hardly used but should be used a lot more
I will go in more detail later on but here is a list of the ten functions that I am talking about

I also cross posted this here: http://dotnetsamplechapters.blogspot.com/2007/09/ten-sql-server-functions-that-you.html


BINARY_CHECKSUM
SIGN
COLUMNPROPERTY
DATALENGTH
ASCII, UNICODE
NULLIF
PARSENAME
STUFF
REVERSE
GETUTCDATE


BINARY_CHECKSUM
BINARY_CHECKSUM is handy if you want to check for data differences between 2 rows of data

In order to see what rows are in table 1 and not in table 2 and vice versa you can do 2 left joins, 2 right joins or 1 left and 1 right join. To get the rows that are different you can use BINARY_CHECKSUM. You have to run this example o SQL Server 2000 to see it work, you can ofcourse use any tables just modify the queries
Let’s get started…

--let's copy over 20 rows to a table named authors2
SELECT TOP 20 * INTO tempdb..authors2
FROM pubs..authors

--update 5 records by appending X to the au_fname
SET ROWCOUNT 5


UPDATE tempdb..authors2
SET au_fname =au_fname +'X'


--Set rowcount back to 0
SET ROWCOUNT 0

--let's insert a row that doesn't exist in pubs
INSERT INTO tempdb..authors2
SELECT '666-66-6666', au_lname, au_fname, phone, address, city, state, zip, contract
FROM tempdb..authors2
WHERE au_id ='172-32-1176'

--*** The BIG SELECT QUERY --***

--Not in Pubs
SELECT 'Does Not Exist On Production',t2.au_id
FROM pubs..authors t1
RIGHT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
UNION ALL
--Not in Temp
SELECT 'Does Not Exist In Staging',t1.au_id
FROM pubs..authors t1
LEFT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t2.au_id IS NULL
UNION ALL
--Data Mismatch
SELECT 'Data Mismatch', t1.au_id
FROM( SELECT BINARY_CHECKSUM(*) AS CheckSum1 ,au_id FROM pubs..authors) t1
JOIN(SELECT BINARY_CHECKSUM(*) AS CheckSum2,au_id FROM tempdb..authors2) t2 ON t1.au_id =t2.au_id
WHERE CheckSum1 <> CheckSum2

--Clean up
DROP TABLE tempdb..authors2
GO




SIGN
Sometimes you are asked by the front-end/middle-tier developers to return a rowcount as well with the result set. However the developers want you to return 1 if there are rows and 0 if there are none. How do you do such a thing?
Well I am going to show you two ways. the first way is by using CASE and @@ROWCOUNT, the second way is by using the SIGN function

For CASE we will do this

RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END

So that's pretty simple, if @@ROWCOUNT is greater than 0 return 1 for everything else return 0

Using the SIGN function is even easier, all you have to do is this

RETURN SIGN(@@ROWCOUNT)

That's all, SIGN Returns the positive (+1), zero (0), or negative (-1) sign of the given expression. In this case -1 is not possible but the other two values are
So let's see this in action


USE pubs
GO

--Case Proc
CREATE PROCEDURE TestReturnValues
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id

RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
GO

--Sign Proc
CREATE PROCEDURE TestReturnValues2
@au_id VARCHAR(49) ='172-32-1176'
AS
SELECT *
FROM authors
WHERE au_id =@au_id

RETURN SIGN(@@ROWCOUNT)
GO


--Case Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues
SELECT @Rowcount
GO

--Case Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues 'ABC'
SELECT @Rowcount
GO

--Sign Proc, 1 will be returned; default value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2
SELECT @Rowcount
GO

--Sign Proc, 0 will be returned; dummy value is used
DECLARE @Rowcount int
EXEC @Rowcount = TestReturnValues2 'ABC'
SELECT @Rowcount
GO


--Help the environment by recycling ;-)
DROP PROCEDURE TestReturnValues2,TestReturnValues
GO


COLUMNPROPERTY
COLUMNPROPERTY is handy if you need to find scale, precision, if it is an identity column and more. I have listed all of them below

CREATE TABLE blah (ID DECIMAL(5,2) not null DEFAULT 99)
INSERT blah DEFAULT VALUES

SELECT * FROM blah
SELECT COLUMNPROPERTY( OBJECT_ID('blah'),'ID','AllowsNull') AS AllowsNull,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsComputed') AS IsComputed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsCursorType') AS IsCursorType,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsDeterministic') AS IsDeterministic,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsFulltextIndexed') AS IsFulltextIndexed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIdentity') AS IsFulltextIndexed,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIdNotForRepl') AS IsIdNotForRepl,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsIndexable') AS IsIndexable,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsOutParam') AS IsOutParam,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsPrecise') AS IsPrecise,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','IsRowGuidCol') AS IsRowGuidCol,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','Precision') AS 'Precision',
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','Scale') AS Scale,
COLUMNPROPERTY( OBJECT_ID('blah'),'ID','UsesAnsiTrim') AS UsesAnsiTrim
FROM Blah


So what does all that stuff mean?

AllowsNull
Allows null values. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsComputed
The column is a computed column. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsCursorType
The procedure parameter is of type CURSOR. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsDeterministic
The column is deterministic. This property applies only to computed columns and view columns. 1 = TRUE
0 = FALSE
NULL = Invalid input. Not a computed column or view column.

IsFulltextIndexed
The column has been registered for full-text indexing. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsIdentity
The column uses the IDENTITY property. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsIdNotForRepl
The column checks for the IDENTITY_INSERT setting. If IDENTITY NOT FOR REPLICATION is specified, the IDENTITY_INSERT setting is not checked. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsIndexable
The column can be indexed. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsOutParam
The procedure parameter is an output parameter. 1 = TRUE
0 = FALSE
NULL = Invalid input

IsPrecise
The column is precise. This property applies only to deterministic columns. 1 = TRUE
0 = FALSE
NULL = Invalid input. Not a deterministic column

IsRowGuidCol
The column has the uniqueidentifier data type and is defined with the ROWGUIDCOL property. 1 = TRUE
0 = FALSE
NULL = Invalid input

Precision
Precision for the data type of the column or parameter. The precision of the specified column data type
NULL = Invalid input

Scale
Scale for the data type of the column or parameter. The scale
NULL = Invalid input

UsesAnsiTrim
ANSI padding setting was ON when the table was initially created. 1= TRUE
0= FALSE
NULL = Invalid input




DATALENGTH
Okay so you know the LEN function but do you know the DATALENGTH function? There are two major difference between LEN and DATALENGTH.
The first one deals with trailing spaces, execute the following code and you will see that LEN returns 3 while DATALENGTH returns 4

DECLARE @V VARCHAR(50)
SELECT @V ='ABC '
SELECT LEN(@V),DATALENGTH(@V),@V

The second difference deals with unicode character data, as you know unicode uses 2 bytes to store 1 character
Run the following example and you will see that LEN returns 3 while DATALENGTH returns 6
DECLARE @V NVARCHAR(50)
SELECT @V ='ABC'
SELECT LEN(@V),DATALENGTH(@V),@V

If you do DATALENGTH(CONVERT(VARCHAR,@V)) you will get the same as LEN because LEN does a RTRIM and converts to VARCHAR before returning



ASCII, CHAR,UNICODE
ASCII will give you the ascii code for a character so for A you will get 65
CHAR does the reverse of ascii CHAR(65) returns A
UNICODE will give you the unicode value for a character
NCHAR will give you the character for a unicode or ascii value
let's see how this works

SELECT ASCII('A'),CHAR(65),CHAR(ASCII('A')),
UNICODE(N'Λ'),NCHAR(923),NCHAR(UNICODE(N'Λ'))




NULLIF
NULLIF Returns a null value if the two specified expressions are equivalent.

Syntax
NULLIF ( expression , expression )

DECLARE @v VARCHAR(20)
SELECT @v = ' '

SELECT NULLIF(@v,' ')

You can combine NULLIF with COALESCE if you want to test for NULLS and Blanks for example

DECLARE @v VARCHAR(20)
SELECT @v = ' '

SELECT COALESCE(NULLIF(@v,' '),'N/A')


Here is another NULLIF example:
CREATE TABLE Blah (SomeCol VARCHAR(33))

INSERT Blah VALUES(NULL)
INSERT Blah VALUES('')
INSERT Blah VALUES(' ')
INSERT Blah VALUES('A')
INSERT Blah VALUES('B B')

--Using COALESCE and NULLIF
SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
FROM Blah


--Using CASE
SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
ELSE SomeCol END SomeCol
FROM Blah


Output for both queries
-----------------------
N/A
N/A
N/A
A
B B


PARSENAME
PARSENAME retrieves parts of string delimited by dots. It is used to split DataBaseServer, DataBaseName, ObjectOwner and ObjectName but you can use it to split IP addresses, names etc

DECLARE @ParseString VARCHAR(100)
SELECT @ParseString = 'DataBaseServer.DataBaseName.ObjectOwner.ObjectName'

SELECT PARSENAME(@ParseString,4),
PARSENAME(@ParseString,3),
PARSENAME(@ParseString,2),
PARSENAME(@ParseString,1)


CREATE TABLE #Test (
SomeField VARCHAR(49))

INSERT INTO #Test
VALUES ('aaa-bbbbb')

INSERT INTO #Test
VALUES ('ppppp-bbbbb')

INSERT INTO #Test
VALUES ('zzzz-xxxxx')

--using PARSENAME
SELECT PARSENAME(REPLACE(SomeField,'-','.'),2)
FROM #Test



Another example:

CREATE TABLE BadData (FullName varchar(20) NOT NULL);
INSERT INTO BadData (FullName)
SELECT 'Clinton, Bill' UNION ALL
SELECT 'Johnson, Lyndon, B.' UNION ALL
SELECT 'Bush, George, H.W.';

Split the names into 3 columns

Your output should be this:
LastName FirstName MiddleInitial
Clinton Bill
Johnson Lyndon B.
Bush George H.W.

SELECT FullName,PARSENAME(FullName2,NameLen+1) AS LastName,
PARSENAME(FullName2,NameLen) AS FirstName,
COALESCE(REPLACE(PARSENAME(FullName2,NameLen-1),'~','.'),'') AS MiddleInitial
FROM(
SELECT LEN(FullName) -LEN(REPLACE(FullName,',','')) AS NameLen,
REPLACE(REPLACE(FullName,'.','~'),', ','.') AS FullName2,FullName
FROM BadData) x



STUFF
STUFF is another function that is hardly used, it is useful if you want to replace or add characters inside data
Take a look at the code below. the first STUFF will replace X with 98765, the second STUFF will place 98765 before the X and the third stuff will replace X- with 98765
DECLARE @v VARCHAR(11)
SELECT @v ='-X-'


SELECT STUFF(@v, 2, 1, '98765'),
STUFF(@v, 2, 0, '98765'),
STUFF(@v, 2, 2, '98765')


The STUFF function is very handy if you need to insert dashes in a social security. You can accomplish that by using the function STUFF twice instead of using substring,left and right

DECLARE @v VARCHAR(11)
SELECT @v ='123456789'

SELECT @v,STUFF(STUFF(@v,4,0,'-'),7,0,'-')



REVERSE
REVERSE just reverses the value, for example the code below returns CBA

SELECT REVERSE('ABC')

Reverse is handy if you need to split values, take a look at this example

CREATE TABLE #TestCityStateZip (csz CHAR(49))
INSERT INTO #TestCityStateZip VALUES ('city ,st 12223')
INSERT INTO #TestCityStateZip VALUES ('New York City,NY 10028')
INSERT INTO #TestCityStateZip VALUES ('Princeton , NJ 08536')
INSERT INTO #TestCityStateZip VALUES ('Princeton,NJ 08536 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City , NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City ,NY 10013 ')


SELECT LEFT(csz,CHARINDEX(',',csz)-1)AS City,
LEFT(LTRIM(SUBSTRING(csz,(CHARINDEX(',',csz)+1),4)),2) AS State,
RIGHT(RTRIM(csz),CHARINDEX(' ',REVERSE(RTRIM(csz)))-1) AS Zip
FROM #TestCityStateZip





GETUTCDATE
SELECT GETUTCDATE()

Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.



And that is all, those are the ten functions that you should be using but currently you are not using all of them. Look them up in Books On Line so that you can see some more examples

Clippy Is Not Dead, Clippy Is Alive.....On Linux

So we all know that Clippy hasn't made it to Office 2007 but guess what? Clippy is not dead, it's alive! DR. FrankGNUstein has dug up Clippy from the cemetery near Redmond, Clippy was resting (in peace) near MS Bob and now only an empty grave can be found.
Below is a picture of Clippy on Linux, kind of creepy and if you look closely you can still see the scars. Now you know why Seattle had a blackout recently because DR. FrankGNUstein used a tremendous amount of power to resurrect Clippy

Click on the image to see a bigger image. And yes I will post some real SQL later today, it will be about ten hardly used functions in T-SQL (STUFF, NULLIF, PARSENAME, REVERSE, UNICODE and more.....)

And if you want to see more Linux Clippy images and a video as well then visit this link: http://vigor.sourceforge.net/screenshots/

Wal-Mart Wine

Wal-Mart announced that, on January 1, 2007, it will begin offering
customers a new discount item - Wal-Mart's own brand of wine.

The world's largest retail chain is teaming up with Ernest & Julio
Gallo Winery of California to produce the spirits at an affordable
price, in the $2-$5 range.

Wine connoisseurs may not be inclined to throw a bottle of Wal-Mart
brand into their shopping carts, but "there is a market for
inexpensive wine," said Kathy Micken, professor of marketing at
University of Arkansas, Bentonville. She said: "But the right name
is important."

Customer surveys were conducted to determine the most attractive
name for the Wal-Mart wine brand. The top surveyed names in order of
popularity were:

10. Chateau Traileur Parc

9. White Trashfindel

8. Big Red Gulp

7. World Championship Riesling

6. NASCARbernet

5. Chef Boyardeaux

4. Peanut Noir

3. I Can't Believe It's Not Vinegar!

2. Grape Expectations

1. Nasti Spumante

The beauty of Wal-Mart wine is that it can be served with either
white meat (Possum) or red meat (Squirrel).

ASP.NET:DataFormatString Doesn't Work In A GridView If HtmlEncode Is True

So this took me most of my lunch hour to figure out the other day. If you have a gridview and you want to apply formatting to a column then you have to set HtmlEncode to false, by default HtmlEncode is set to true. Of course I thought initially that my super advanced formatting formula ({0:c}) was wrong. A quick Google search found the answer. So below where you see this part HtmlEncode="False" that is the culprit, initiall this will be HtmlEncode="True"

<Columns><asp:BoundField DataField="OrderCost" DataFormatString="{0:c}" HeaderText="OrderCost" HtmlEncode="False" SortExpression="Currency" /></Columns>

I must say that having been away from front-end programming for 5 years I am kind of glad to be back (back is a strong word, I am doing this mostly during lunch and at home because my plate is full with SQL SERVER tasks). But I still have a lot to learn, yep back to n00b status ;-(
Yesterday I was debugging a web service that I wrote in VS 2005. It's kind of cool to see that that works because after you spend time with the SQL Server debugger you would think that something like that would never work

How to Backup Your Blogger Blog

If you have a blog hosted on Blog*Spot (Blogger) and you've upgraded to the new version, there's an easy way to backup your blog. The Google Operating System blog shows you just the way to do this. The link to the article is below

http://googlesystem.blogspot.com/2007/02/how-to-backup-blogger-blog.html

Use A Combination OF NULLIF and COALESCE TO Display A Custom Value

Let's you inherit a table with a column that can contain blanks, empty strings or NULL values. I am saying inherit because surely you would have a constraint on the column that wouldn't allow those values to begin with right? For all the values that are '',' ' or NULL you want to display 'N/A'.
What is the best way to do this? You can Use CASE and test for the values or you can use COALESCE with NULLIF which is much shorter. A lot of people don't know that you can stack these functions so that it makes your life so much easier.
Let's get started

CREATE TABLE Blah (SomeCol VARCHAR(33))

INSERT Blah VALUES(NULL)
INSERT Blah VALUES('')
INSERT Blah VALUES(' ')
INSERT Blah VALUES('A')
INSERT Blah VALUES('B B')

--Using COALESCE and NULLIF
SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
FROM Blah


--Using CASE
SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
ELSE SomeCol END SomeCol
FROM Blah


Output for both queries
-----------------------
N/A
N/A
N/A
A
B B

SSIS Connectivity White Papers Available On Microsoft's Site

Bob Beauchemin's SSIS whitepapers are now available on Microsoft's website

Connectivity and SQL Server 2005 Integration Services
http://download.microsoft.com/download/2/7/c/27cd7357-2649-4035-84af-e9c47df4329c/ConnectivitySSIS.doc

Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/ImplementingScaleOut.doc

Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/InternalsTroubleshootingScaleOut.doc

Enjoy reading them

What Happens When You Let a Three Year Old Use Your Computer For 2 Minutes?

What Happens When You Let a Three Year Old Use Your Computer For 2 Minutes?

This (I Spy a bunch of trouble)

Shipping Method: Standard Shipping
Shipping Preference: Group my items into as few shipments as possible
Subtotal of Items: $234.62
Shipping & Handling: $32.70
Promotion Applied: -$19.95

------
Total for this Order: $247.37


Shipping estimate for these items: February 7, 2007
Delivery estimate: February 12, 2007 - February 14, 2007 1 "I Spy A Penguin (Level 1) (I Spy)"
JEAN MARZOLLO; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy Santa Claus (Scholastic Reader, Level 1)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy A Balloon (Scholastic Reader Level 1)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy Lightning In The Sky (level 1): I Spy Lightning In The Sky (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy A Dinosaur's Eye (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy A Dinosaur's Eye (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy A Pumpkin (Scholastic Reader Level 1)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy A Pumpkin (Scholastic Reader Level 1)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy: Year-round Challenger: Year-round Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy A School Bus (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy Little Animals (I Spy)"
Jean Marzollo; Board book; $6.99

Sold by: Amazon.com
1 "I Spy Fun House: A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16

Sold by: Amazon.com
1 "I Spy School Days (I Spy)"
Jean Marzollo; Hardcover; $11.16

Sold by: Amazon.com
1 "I Spy Fun House: A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16

Sold by: Amazon.com
1 "I Spy Extreme Challenger! A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16

Sold by: Amazon.com
1 "I Spy Funny Teeth (Scholastic Readers)"
Jean Marzollo; Paperback; $3.99

Sold by: Amazon.com
1 "I Spy Gold Challenger (I Spy)"
Jean Marzollo; Hardcover; $11.58

Sold by: Amazon.com
1 "I Spy Spooky Night (I Spy)"
Jean Marzollo; Hardcover; $11.16

Sold by: Amazon.com
1 "I Spy: A Book Of Picture Riddles: A Book Of Picture Riddles (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy Christmas: A Book of Picture Riddles"
Jean Marzollo; Hardcover; $11.16

Sold by: Amazon.com
1 "I Spy: Ultimate Challenger: Ultimate Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy: Ultimate Challenger: Ultimate Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy: Ultimate Challenger: Ultimate Challenger (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy Little Letters (I Spy)"
Jean Marzollo; Board book; $6.99

Sold by: Amazon.com
1 "I Spy Little Letters (I Spy)"
Jean Marzollo; Board book; $6.99

Sold by: Amazon.com
1 "I Spy: Mystery A Book Of Picture Riddles: Mystery A Book Of Picture Riddles (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy: Mystery A Book Of Picture Riddles: Mystery A Book Of Picture Riddles (I Spy)"
Jean Marzollo; Hardcover; $9.95

Sold by: Amazon.com
1 "I Spy Little Wheels (I Spy)"
Jean Marzollo; Board book; $6.99

Sold by: Amazon.com
1 "I Spy Little Book (I Spy)"
Jean Marzollo; Board book; $6.99

Sold by: Amazon.com
1 "Can You See What I See? Dream Machine"
Walter Wick; Hardcover; $11.58

Sold by: Amazon.com


And of course it already shipped, how come when I place an order it doesn't ship for 2 days.....

More Than 10 Tips To Help Your Virtual PC Performance.

Sahil Malik has posted on his blog 10 Tips To Help Your Virtual PC Performance. If you use Virtual PC a lot to test service pack 2 for SQL Server for example then you want to check these tips out. The URL is below
http://blah.winsmarts.com/2007-2-10_tips_to_help_your_Virtual_PC_performance.aspx

John Carpenter’s The Thing

I brought in John Carpenter’s The Thing to work only to discover that 80% of my co-workers never heard of this movie. This movie is probably one of the best in its genre. If you like Alien(s) then you will love this movie. The story is about a bunch of researchers on Antarctica, they discover a spaceship and take a dog in (bad idea) I won’t spoil the story but let’s just say that a lot of people die. I watched this movie at least 10 times since its release in 1982. even nowadays the special effects are still amazing.

IMDB lists this movie in the top 250 movies of all time with a rating of 8.0 and 29,153 votes. If you have problems picking out a movie at blockbuster with your significant other then consider this a favor because I know what it feels like spending 30 minutes in Blockbuster trying to agree on a movie. I even went home a couple of times without a movie because my wife and I couldn’t agree on a movie. So what are you waiting for it is Saturday afternoon (well it is in Princeton, NJ anyway)? Go to the movie rental store and watch this movie. Let me know what you thought of it after you watch it

If you are in doubt then just check out the comments on IMDB http://www.imdb.com/title/tt0084787/usercomments

And here is the trailer
http://srv12.movie-list.net/bendermac/thing_redux_h640_ml.mov

Give Me One Good Reason Why You Would Store Documents In The Database?

Why would you ever store PDF, Doc or Excel files in the database? What is the point? I don't see any advantages only disadvantages
Let's say you have 8 webservers and 1 big SQL monster. If you store these documents in the DB and you get hit by these 8 webservers for documents all the time your DB is going to slow down. A much better way is to have the files on the webservers itself, sending 9MB pdf files over the network is just wrong.
Also if you store all these files in the DB your backups will take much longer.
Updating BLOBs is another pain in the neck; UPDATETEXT and WRITETEXT are not my favorite SQL commands.

So here is the question:
Would you store images/documents/spreadsheets in the Database and why?

SQL Server 2005 High Availability Podcast On DotNet Rocks

.NET Rocks has made their latest podcast available, this one deals with SQL Server 2005 high availability
Allan Hirt discusses the details of providing high availability with SQL Server 2005, and the things developers need to know in order to make their applications compatible. He discusses clustering, transaction log shipping, mirroring, and more

The podcast is available in MP3, WMA,WMA L lo-Fi and IPOD (AAC) formats and the duration is one hour and 15 minutes (1:14:55)

Get the podcast here: http://dotnetrocks.com/default.aspx?showID=215

How To Restart A Remote Computer

Sometimes you have to login to your work PC from home over the VPN and after a while for some reason or another you want to restart your PC. How can you do that? You can't use the start menu because only the log off button is displayed
Well one way is to open a command window and executing shutdown -r
That will restart your computer

Here is the basic usage of the shutdown command

Usage: shutdown [-i -l -s -r -a] [-f] [-m \\computername] [-t xx] [-c "comment"] [-d up:xx:yy]

No args Display this message (same as -?)
-i Display GUI interface, must be the first option
-l Log off (cannot be used with -m option)
-s Shutdown the computer
-r Shutdown and restart the computer
-a Abort a system shutdown
-m \\computername Remote computer to shutdown/restart/abort
-t xx Set timeout for shutdown to xx seconds
-c "comment" Shutdown comment (maximum of 127 characters)
-f Forces running applications to close without warning
-d [u][p]:xx:yy The reason code for the shutdown
u is the user code
p is a planned shutdown code
xx is the major reason code (positive integer less than 256)
yy is the minor reason code (positive integer less than 65536)

Need Help With Picking Out An ASP.NET 2.0 Book

I decided to get back into some web development again, the last time I did this was in October 2001. I have developed in ASP, JSP and ColdFusion. Since I know some C# and have been using that on and off for the last 2 years with windows forms I decided to use ASP.NET 2.0. Another factor is that it is also a corporate standard at work.
So for someone like me which book would you recommend? Or maybe you have read an ASP.NET book that is just awesome either way let me know.

Don't worry I will never spend less than 51% of my time with SQL ;-)

ASP.NET AJAX PDF Cheat Sheets Available For Download

The ASP.NET AJAX PDF Cheat Sheets have been updated for the ASP.NET AJAX 1.0 final release. There are 6 sheets that you can download in PDF or XPS format. Get the sheets here: http://aspnetresources.com/blog/ms_ajax_cheat_sheets_batch2.aspx

Are You ready For Daylight Saving Time Changes In 2007

In August of 2005 the United States Congress passed the Energy Policy Act, which changes the dates of both the start and end of daylight saving time (DST). When this law goes into effect in 2007, DST will start three weeks earlier (2:00 A.M. on the second Sunday in March) and will end one week later (2:00 A.M. on the first Sunday in November) than what had traditionally occurred.

Read this article (preparing for daylight saving time changes in 2007) to see how you are affected

I hope that you are not running NT4

Here are some direct update links
Windows XP
Windows 2003 Server
Full List

Microsoft products affected by the DST legislation
Windows Client
Windows Server
Windows Mobile
Microsoft Windows SharePoint Services
Microsoft Exchange Server
Microsoft Office Outlook
Microsoft Dynamics CRM
Microsoft Biztalk Server
Microsoft Entourage

ASP.NET AJAX Version 1.0 Released

ASP.NET AJAX formerly known as Atlas has been released get it here http://ajax.asp.net/

Microsoft SQL Server Database Publishing Wizard 1.0,SharePoint Server 2007 SDK And Windows SharePoint Services v3 SDK Available For Download

Microsoft SQL Server Database Publishing Wizard 1.0
SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window. If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider.

Optionally, SQL Server Database Publishing Wizard can integrate directly into Visual Studio 2005 and/or Visual Web Developer 2005 allowing easy publishing of databases from within the development environment.



SharePoint Server 2007 SDK
The Microsoft Office SharePoint Server 2007 Software Development Kit (SDK) contains conceptual overviews, programming tasks, code samples, references, and an Enterprise Content Management (ECM) starter kit to guide you in developing solutions based on Microsoft Office SharePoint Server 2007.

Windows SharePoint Services v3 SDK
The Windows SharePoint Services 3.0 software development kit (SDK) contains conceptual overviews, programming tasks, samples, and references to guide you in developing solutions based on Microsoft Windows SharePoint Services 3.0.

Check If Auto Update Statistics Is Enabled With DATABASEPROPERTY

How do you check if auto update statistics is enabled on your database? It is pretty easy to check that, you can use the DATABASEPROPERTY function
Run the following line of code

SELECT DATABASEPROPERTY('pubs','IsAutoUpdateStatistics') AS IsAutoUpdateStatistics

If 1 is returned(true) it is enabled, if 0 is returned(false) then it is not enabled

Now to save me (and you) time I have pasted a code block below with all the properties, just change the database name from pubs to your database name and run the code


DECLARE @v VARCHAR(55)
SELECT @v = 'pubs'

SELECT
DATABASEPROPERTY(@v,'IsAnsiNullDefault') AS IsAnsiNullDefault,
DATABASEPROPERTY(@v,'IsAnsiNullsEnabled') AS IsAnsiNullsEnabled,
DATABASEPROPERTY(@v,'IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,
DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTY(@v,'IsAutoCreateStatistics') AS IsAutoCreateStatistics,
DATABASEPROPERTY(@v,'IsAutoShrink') AS IsAutoShrink,
DATABASEPROPERTY(@v,'IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,
DATABASEPROPERTY(@v,'IsBulkCopy') AS IsBulkCopy,
DATABASEPROPERTY(@v,'IsDboOnly') AS IsDboOnly,
DATABASEPROPERTY(@v,'IsDetached') AS IsDetached,
DATABASEPROPERTY(@v,'IsEmergencyMode') AS IsEmergencyMode,
DATABASEPROPERTY(@v,'IsInLoad') AS IsInLoad,
DATABASEPROPERTY(@v,'IsInRecovery') AS IsInRecovery,
DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTY(@v,'IsInStandBy') AS IsInStandBy,
DATABASEPROPERTY(@v,'IsLocalCursorsDefault') AS IsLocalCursorsDefault,
DATABASEPROPERTY(@v,'IsNotRecovered') AS IsNotRecovered,
DATABASEPROPERTY(@v,'IsNullConcat') AS IsNullConcat,
DATABASEPROPERTY(@v,'IsOffline') AS IsOffline,
DATABASEPROPERTY(@v,'IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,
DATABASEPROPERTY(@v,'IsReadOnly') AS IsReadOnly,
DATABASEPROPERTY(@v,'IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,
DATABASEPROPERTY(@v,'IsShutDown') AS IsShutDown,
DATABASEPROPERTY(@v,'IsSingleUser') AS IsSingleUser,
DATABASEPROPERTY(@v,'IsSuspect') AS IsSuspect,
DATABASEPROPERTY(@v,'IsTruncLog') AS IsTruncLog,
DATABASEPROPERTY(@v,'Version') AS Version

So what do all these values mean? Here is a list of all the properties

IsAnsiNullDefault
Database follows SQL-92 rules for allowing null values.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAnsiNullsEnabled
All comparisons to a null evaluate to unknown.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAnsiWarningsEnabled
Error or warning messages are issued when standard error conditions occur.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoClose
Database shuts down cleanly and frees resources after the last user exits.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoCreateStatistics
Existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoShrink
Database files are candidates for automatic periodic shrinking.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoUpdateStatistics
Auto update statistics database option is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsBulkCopy
Database allows nonlogged operations.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsCloseCursorsOnCommitEnabled
Cursors that are open when a transaction is committed are closed.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsDboOnly
Database is in DBO-only access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsDetached
Database was detached by a detach operation.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsEmergencyMode
Emergency mode is enabled to allow suspect database to be usable.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsFulltextEnabled
Database is full-text enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsInLoad
Database is loading.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsInRecovery
Database is recovering.
1 = TRUE
0 = FALSE
NULL1 = Invalid input

IsInStandBy
Database is online as read-only, with restore log allowed.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsLocalCursorsDefault
Cursor declarations default to LOCAL.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsNotRecovered
Database failed to recover.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsNullConcat
Null concatenation operand yields NULL.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsOffline
Database is offline.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsQuotedIdentifiersEnabled
Double quotation marks can be used on identifiers.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsReadOnly
Database is in a read-only access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsRecursiveTriggersEnabled
Recursive firing of triggers is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsShutDown
Database encountered a problem at startup.
1 = TRUE
0 = FALSE
NULL1 = Invalid input

IsSingleUser
Database is in single-user access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsSuspect
Database is suspect.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsTruncLog
Database truncates its logon checkpoints.
1 = TRUE
0 = FALSE
NULL = Invalid input

Version
Internal version number of the Microsoft® SQL Server™ code

SQL Server Doesn't Like Cheaters

I emailed a joke(see below) to some friends and one of them replied: "So what you're saying is that I should take a mistress?"

So that got me thinking, what would SQL say about this? Is mistress equal to mistrust. Well according to SQL server it is, run this in Query Analyzer

SELECT SOUNDEX('mistress'),SOUNDEX('mistrust'),DIFFERENCE('mistress','mistrust')

And here is the joke:

Two Ladies talking in heaven

1st woman: Hi! My name is Wanda.

2nd woman: Hi! I'm Sylvia. How'd you die?

1st woman: I Froze to Death.

2nd woman: How Horrible!

1st woman: It wasn't so bad. After I quit shaking from the cold, I
began to get warm & sleepy, and finally died a peaceful death. What
about you?

2nd woman: I died of a massive heart attack. I suspected that my
husband was cheating, so I came home early to catch him in the act.
But instead, I found him all by himself in the den watching TV.

1st woman: So, what happened?

2nd woman: I was so sure there was another woman there somewhere that I started running all over the house looking. I ran up into the attic and searched, and down into the basement. Then I went through every closet and checked under all the beds. I kept this up until I had looked everywhere,and finally I became so exhausted that I just keeled over with a heart attack and died.

1st woman: Too bad you didn't look in the freezer---we'd both
still be alive.


So what is the point of this all? Well it gives you a reason to run those barely used functions like SOUNDEX and DIFFERENCE ;>

A Witchbrew Of SQL Server News And Interesting Links

Since I have nothing interesting or fascinating to say I decided to put some links up to some posts of people who do have something interesting to say.
We will start with Hugo Kornelis who has two posts about why clustered index ordering is not guaranteed in SQL Server. The first article can be found here: http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx
And the second article that explains the behaviour can be found here: Beatles vs Stones Explanation

Developer.com has announced their Database Tool or Add-in winner and the winner is......SQL Server 2005. You can read the whole article here http://www.developer.com/db/article.php/10920_3653956_1

Euan Garden has a post telling us that SQL Server Compact Edition launches (finally)

Tim O'Reilly posted his latest State of the Computer Book Market article and SQL server is still going strong. You can fnd that article here: State of the Computer Book Market, Q4 06, Part 1, Overall Market Trend

And we will end with Internet Maverick Marc Cuban who wrote an interesting article titled Why I Don't Wear a Suit and Can't Figure Out Why Anyone Does !
I don't wear a suit either, I actually wear jeans and sneakers most of the time. When i used to work in Silicon Alley (Broadway and 21st street in New York City) I actually came to work in shorts in the summer but then again what do you expect when you have 2 lizards and a cat roaming around the office?

sp_refreshview

Did you know that when you create a view and then later change the table the view is not updated?
Don't believe me? Run the following block of code

CREATE TABLE TestTable (id INT,SomeCol VARCHAR(666))
GO

INSERT TestTable VALUES(1,'ABC')
GO

SELECT * FROM TestTable
GO

CREATE VIEW TestView
AS
SELECT * FROM TestTable
GO

SELECT * FROM TestView
GO


ALTER TABLE TestTable
ADD Col2 DATETIME DEFAULT CURRENT_TIMESTAMP
GO

INSERT TestTable(id,SomeCol) VALUES(2,'XYZ')
GO

SELECT * FROM TestTable
GO

SELECT * FROM TestView
GO

See what happened? The TestView does not include the Col2 column. So what can you do? There are at least two things that you can do. You can recreate the view with a create or alter statement or you can use sp_refreshview, run the code below to see how that works

sp_refreshview TestView
GO

--All good now
SELECT * FROM TestView
GO


--Clean up this mess--
DROP VIEW TestView
GO


DROP TABLE TestTable
GO


And yes I know 'real' SQL programmers never use SELECT * and 'real' SQL programmers name their defaults ;-)

Visual Studio Code Name "Orcas" - January Community Technology Preview (CTP) Released

Visual Studio Code Name "Orcas" - January Community Technology Preview (CTP) has been released.
This CTP is available as a Virtual PC image or as a self-extracting install. If you wish to use the Virtual PC image you will need Virtual PC or Virtual Server to run this image. If you wish to use the self extracting install, we advise that you do not install this on a production machine. Depending on your hardware the download files make take between 30-60 minutes to decompress.



The highlights of this CTP include:

• Extended, more powerful data APIs with the ADO.NET Entity Framework and LINQ to ADO.NET
  
  • With the ADO.NET Entity Framework developers will be able to model the view of the data that is appropriate for each one of the applications they are building, independently of the structure of the data in the underlying database. The use of the Entity Data Model (EDM) enables developers to design models that follow the concepts built into the application, instead of having to map them to constructs available in relational stores. Once the model is in place, the powerful ADO.NET Entity Framework API is used to access and manipulate the data as .NET classes or as rows and columns, whatever is appropriate for each application.
    
  
  
  • ADO.NET is fully integrated with LINQ and offers many options for using LINQ in various scenarios: LINQ to SQL provides direct access to database tables from the programming environment, LINQ to Entities enables developers to use LINQ over EDM models, and LINQ to DataSet allows the full expressivity of LINQ to be used over DataSets.
    
  
  

• C# 3.0 Language Support: This CTP implements all of the C#3.0 language features from the May LINQ CTP including:
  
  • Query Expressions
  
  
  • Object and Collection Initializers
  
  
  • Extension Methods
  
  
  • Local Variable Type Inference and Anonymous Types
  
  
  • Lambdas bound to Delegates and Expression trees
  
  

• VB 9.0 Language Support: This CTP implements all of the VB 9.0 language features from the May LINQ CTP including:
  
  • Query Expressions
  
  
  • Object Initializers
  
  
  • Extension Methods
  
  
  • Local Variable Type Inference
  
  
  • Anonymous Types
  
  

• LINQ to Objects API
  
  • The LINQ to Objects API supports queries over any .NET collection, such as arrays and Generic Lists. This API is defined in the System.Linq namespaces inside System.Core.dll. Click here for more details about LINQ.
    
  
  

• ClickOnce improvements
  
  • This CTP delivers ClickOnce improvements for the deployment of Windows Presentation Foundation applications, alternative browser support and ISV rebranding.
    
  
  

• Managed classes for Elliptic Curve Diffie Hellman and Elliptic Curve Digital Signature Algorithm cryptographic functionality
  
  • With the addition of these classes, cryptographic developers now have managed classes for Elliptic Curve Diffie Hellman secret agreement and Elliptic Curve Digital Signature Algorithm signing. These classes are built on the new CNG cryptographic libraries in Windows Vista, but still follow the familiar patterns of the cryptographic classes in .NET Framework 2.0.
    
  
  

• Runtime and design-time support for Office 2007 (including Outlook 2007)
  
  • Customers can build managed code add-ins with a consistent development experience, regardless of which version of Office they target, which Office application(s) they target, and which programming language they choose. Managed code add-ins enable developers to use strongly-typed class members, with the help of modern development tools, including intellisense and auto-complete. Additionally add-ins can potentially run in multiple versions of Office, enabled by abstracting version-specific code and supported by a version-resilient infrastructure.
    
  
  

• Support for advanced lifetime management of add-ins and their AppDomains
  
  • We’ve added the helper classes that manage the lifetime of add-ins, the objects passed between the host and add-ins, and even of the AppDomains the add-ins live in. By using the ContractBase and LifetimeToken handle, pipeline developer can let the hosts and add-ins act as if everything, including the AppDomain the add-in was activated in, was controlled by the garbage collector even though .Net Remoting would normally make that impossible.
    
  
  

• Client service support for Login/Logout, Role management and Profiles
  
  • ASP.NET 2.0 shipped with new application services for authentication, authorization and personalization. Most of these services are not tied to ASP.NET and can work in non-web applications. This CTP enables the use of these services in smart client applications for Logon/Logoff, Role management and profiles.
    
  
  

• A trace listener that logs event to ETW, event tracing for Windows in Vista
  
  • Event tracing for windows is greatly improved in Vista and the most performant loggings facility available in Windows. The System.Diagnostics.EventProviderTraceListener allows managed tracing to provide events to the Vista’s ETW infrastructure. This is a highly performant, thread-safe listener.
    
  
  

• Jscript Intellisense support
  
  • Jscript code formatting and Intellisense support provide developers with a richer editing experience. These improvements enable the IDE to provide statement completion, color syntax highlighting and in-place documentation to Jscript and associated script models such as ASP.NET AJAX.
    
  
  

• A new numeric type that provides support for very large numbers (Beyond the range of In64)
  
  • All existing numeric types in the Framework have a limited range. This is the first type that supports arbitrary range and will extend to accommodate any large number as needed. This type lives in the new System.Numeric namespace where all new numeric and arithmetic features are going to reside. It supports all the basic arithmetic operations including things like Pow, DivRem and GreatestCommonDivisor. It implements the following interfaces: IFormattable, IComparable, IComparable<BigInteger> and IEquatable<BigInteger>. It is serliazable and immutable. It has implicit casts from all basic integral types and explicit casts to/from all numeric type. To learn more about this type – please visit the BCL team blog.
    
  
  

• LINQ over XML (XLinq)
  
  • Enable further LINQ over XML feature support (in addition to the functionality available in the Oct 2006 CTP) such as the ability to apply XLST to transform into and out of XLinq trees, support for System.XML reader/writer interfaces for improved XML sharing with DOM applications and System.XML schema validation for XLinq nodes.
    
  
  

• SQL Server Compact Edition (SSCE)
  
  • SQL Server Compact Edition (SSCE) provides a local relational data store for occasionally connected client applications from desktops to devices. SSCE is light weight, embeddable and is easy to deploy with your client applications without requiring complex administration work from users. Timestamp (row version id) data type, improved table designer, Query processor enhancements and support for local transaction scope are some of the new features you find in this version of SSCE.
    
  
  

Thi CTP is available both as a regular install, and on a virtual machine.

Have fun

How To Check If Any, ALL Or No Parameters Have A NULL Value

Let's say you have a procedure that accepts 6 parameters. depending on if these parameters are all null, all not null, some null and some not null you want to do different things.
How can you test this the easiest? This depends what you are testing for but COALESCE and concatenation are the easiest (read least to type) to do.


So let's start with all nulls
You can do a bunch of ANDs
You can concatenate into a string but you have to first set CONCAT_NULL_YIELDS_NULL OFF because the default CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL
You can also union the parameters and do a NOT EXIST NOT NULL test
Finally you can use COALESCE which will return NULL if ALL the values are NULL


--ALL NULL
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT


IF @Param1 IS NULL
AND @Param2 IS NULL
AND @Param3 IS NULL
AND @Param4 IS NULL
AND @Param5 IS NULL
AND @Param6 IS NULL
PRINT 'ALL NULL'

SET CONCAT_NULL_YIELDS_NULL OFF
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NULL
PRINT 'ALL NULL'


IF NOT EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NOT NULL)
PRINT 'ALL NULL'



IF (SELECT COALESCE(@Param1,@Param2,@Param3,@Param4,@Param5,CONVERT(VARCHAR(10),@Param6))) IS NULL
PRINT 'ALL NULL'

Next up is to test that at least one value is not NULL
You can do a bunch of Ors
You can concatenate into a string but you have to first set CONCAT_NULL_YIELDS_NULL OFF because the default CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL
You can also union the parameters and do a EXIST NOT NULL test
Finally you can use COALESCE which will not return NULL if ANY of the values is not NULL

--Some Non Nulls
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT

SELECT @Param1 ='A'

SET CONCAT_NULL_YIELDS_NULL OFF
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NOT NULL
PRINT 'At least One Value is not NULL'



IF @Param1 IS NOT NULL
OR @Param2 IS NOT NULL
OR @Param3 IS NOT NULL
OR @Param4 IS NOT NULL
OR @Param5 IS NOT NULL
OR @Param6 IS NOT NULL
PRINT 'At least One Not NULL'


IF EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NOT NULL)
PRINT 'At least One Not NULL'

IF (SELECT COALESCE(@Param1,@Param2,@Param3,@Param4,@Param5,CONVERT(VARCHAR(10),@Param6))) IS NOT NULL
PRINT 'At least One Not NULL'


Next up is to test that there are no NULLs
You can do a bunch of ANDs
You can also union the parameters and do a NOT EXIST NULL test
Finally you can use COALESCE which will not return NULL if ANY of the values is not NULL

--No NULLS
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT

SELECT @Param1 ='A',
@Param2 ='A',
@Param3 ='A',
@Param4 ='A',
@Param5 ='A',
@Param6 =1


IF @Param1 IS NOT NULL
AND @Param2 IS NOT NULL
AND @Param3 IS NOT NULL
AND @Param4 IS NOT NULL
AND @Param5 IS NOT NULL
AND @Param6 IS NOT NULL
PRINT 'No NULLs'

IF NOT EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NULL)
PRINT 'No NULLs'


SET CONCAT_NULL_YIELDS_NULL ON
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NOT NULL
PRINT 'No NULLs'

The final test is to test that at least one value is NULL
You can do a bunch of Ors
You can concatenate into a string and leave the default CONCAT_NULL_YIELDS_NULL ON because CONCAT_NULL_YIELDS_NULL ON will return NULL if even one value is NULL
You can also union the parameters and do a EXIST NOT NULL test

--Some Nulls
DECLARE @Param1 VARCHAR(10),
@Param2 VARCHAR(10),
@Param3 VARCHAR(10),
@Param4 VARCHAR(10),
@Param5 VARCHAR(10),
@Param6 INT


SELECT @Param1 ='A',
@Param2 ='A',
@Param3 ='A',
@Param4 =null,
@Param5 ='A',
@Param6 =1

IF @Param1 IS NULL
OR @Param2 IS NULL
OR @Param3 IS NULL
OR @Param4 IS NULL
OR @Param5 IS NULL
OR @Param6 IS NULL
PRINT 'At least One NULL'

SET CONCAT_NULL_YIELDS_NULL ON
IF (SELECT @Param1+ @Param2+ @Param3+ @Param4+ @Param5+ CONVERT(VARCHAR(10),@Param6)) IS NULL
PRINT 'At least One NULL'
ELSE
PRINT 'No NULLs'

IF EXISTS (SELECT * FROM (SELECT @Param1 AS Param UNION ALL
SELECT @Param2 UNION ALL
SELECT @Param3 UNION ALL
SELECT @Param4 UNION ALL
SELECT @Param5 UNION ALL
SELECT CONVERT(VARCHAR(10),@Param6) ) X WHERE Param IS NULL)
PRINT 'At least One NULL'

SQL Server Integration Services 2005 Connectivity Whitepaper Available At ssis.wiki.com

Bob Beauchemin author of A Developer's Guide to SQL Server 2005 (Microsoft .NET Development Series) has written a SSIS Connectivity Whitepaper(still a draft)

This whitepaper enumerates the connectivity options for databases with SQL Server Integration Services 2005 and mentions special considerations that are required when using some database sources with some of the more complex transformations.

It is a word document and contains 27 pages. You can download it here: http://ssis.wiki.com/Connectivity_White_Paper

Enjoy