What happens if a statement fails in a SQL Server stored procedure? [Digg.com This!]

Answer: it carries on regardless! In other words: if you want a stored proc to specifically stop upon error, test for it and return.

Take this example:

CREATE TABLE [dbo].[PaulTest](
[Number] [int] NOT NULL
) ON [PRIMARY]

CREATE PROCEDURE uspPaulTest AS
BEGIN
INSERT INTO PaulTest (Number) VALUES (NULL) -- fails as NULL is invalid
INSERT INTO PaulTest (Number) VALUES(1) -- still gets processed as the query doesn't return
END

-- Let's test it:

SELECT * FROM PaulTest

EXEC uspPaulTest

SELECT * FROM PaulTest

-- Output:

Number
-----------
(0 row(s) affected)

Msg 515, Level 16, State 2, Procedure uspPaulTest, Line 10
Cannot insert the value NULL into column 'Number', table 'Test.dbo.PaulTest'; column does not allow nulls. INSERT fails.
The statement has been terminated.(1 row(s) affected)

Number
-----------
1
(1 row(s) affected)

Visual Studio 2008 Errors [Digg.com This!]

Recently I had my VS 2008 RTM/RC installation go pear shaped. Overnight I stopped being able to create VC++ smartdevice projects with the error

“Creating Project '<ProjectName>'... project creation failed”

‘No problem’, I thought, ‘I’ll just run setup and do a repair’.  Unfortunately, I got an error with the setup of

“A problem has been encountered while loading the setup components. Canceling setup.”

Luckily I came across a post on StackOverflow (which is running turning out to be a useful resource from the author of CodingHorror.com) pointing to a Microsoft tool to ‘force’ uninstall various Microsoft products, including Visual Studio:

http://msdn.microsoft.com/en-us/vstudio/bb968856.aspx

SQL Server 2008 Truncating Log File [Digg.com This!]

First find the filename labels for your database:

select name from <DatabaseName>.dbo.sysfiles

Next truncate the log:

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

Finally shrink the database based on the log file in step 1:

DBCC SHRINKFILE('NameAbove_log',2048)

2048 is the resultant file size in MB.

Screen Real Estate Wastage [Digg.com This!]

Usually I’m a fan of Microsoft UI design, however having installed the latest version of Windows Live Messenger (or more accurately had it forced on me) I’m astounded by the amount of screen real estate squandered.  Clearly Microsoft have been buying too many 24” monitors.  After turning off as much of the unneeded fluff as possible I was still left with a window far larger than necessary, with no real benefit.  See below!

SQL Quiz of the Day [Digg.com This!]

Let’s say you have raw data as so:

Id          Counter     Ident
----------- ----------- -----
1           1           A
2           2           A
3           1           A
4           3           B
5           3           B
6           2           A
7           1           A
8           4           C
9           2           C
10          1           B
11          1           B
12          4           B

And you want to group by the Ident field, but
only contiguous blocks.  So you want to display
the following:

Counter     Ident
----------- -----
4           A
6           B
3           A
6           C
6           B

Naturally one can’t use GROUP BY, because this would
aggregate multiple A’s and B’s.

Answers on a postcard!

====

CREATE TABLE [dbo].[Test](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Counter] [int] NOT NULL,
        [Ident] [varchar](3)
         COLLATE Latin1_General_CI_AS NOT NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
        [Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

MaddogTCP network TCP/IP fuzzer [Digg.com This!]

A while ago - almost three years in fact - I wrote a fuzzer to test network security of TCP/IP connections. Due to one reason and another I never really released it, and despite being published on the net it was hidden from Google, and hence remained undiscovered.

Looking back, it was a reasonably succinct little program that deserves the light of day, so here it is:

http://www.calcaria.net/MaddogTCP/

Generic type parser using Generics C# .NET [Digg.com This!]

Recently I had the need to safely parse multiple types from text ignoring exceptions. Microsoft did not implement an IParsable interface (despite requests) so I thought a solution was crying out for using generics.

The concept was simple: pass in an object of type T and call T.Parse.  The .NET compiler can check at compile time that the type supports Parse, right?  Unfortunately not.  Although generics allows constraints such as new(), it does not support arbitrary method constraints.

Lack of method constraints means attempting to call T.Parse won't work.  The result is having to use reflection to get around this.  Of course passing in a type that does not support Parse will  throw an exception, but by virtue of the design this will be handled and the type will return the default.

private T SafeParseAndAssign<T>(string val)
  where T : new()
{
try
{
  T ValOut = new T();

  MethodInfo MI = ValOut.GetType().
    GetMethod("Parse", new Type[] { val.GetType() });

  return (T)MI.Invoke(ValOut, new object[] { val });
}
catch
{
  // swallow exception
}
return default(T);
}