Paul Maddox

What happens if a statement fails in a SQL Server stored procedure?

2009-05-15T01:10:00.000-07:00

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

2009-04-22T10:19:00.001-07:00

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

2009-04-21T12:44:00.001-07:00

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

2009-04-21T12:39:00.001-07:00

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

2009-04-21T05:07:00.000-07:00



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

2009-04-20T13:27:00.000-07:00

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

2009-04-08T13:16:00.000-07:00

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);
}

TinyHomepage - icon based favourites web app

2008-12-30T07:55:00.000-08:00

My latest venture - an icon based favourites web app for mobile phones. Feedback gratefully received.

TinyHomepage.com

.NET GetTempFileName throws System.IO.IOException: The file exists

2008-12-23T04:30:00.000-08:00

Getting an exception System.IO.IOException: The file exists when calling GetTempFileName? I had this problem with a legacy app, despite the call working fine on nearly all of our systems. It turns out the temp directory had been filling with temp files and eventually went bang. From the MSDN:

The GetTempFileName method will raise an IOException if it is used to create more than 65535 files without deleting previous temporary files.
The GetTempFileName method will raise an IOException if no unique temporary file name is available. To resolve this error, delete all unneeded temporary files.

Simple really.

C# .NET Image Manipulation Resize + Grayscale + TIFF

2008-11-17T13:21:00.000-08:00

Code from the last three posts can be downloaded formatted properly here:

http://paulmaddox.net/BlogFiles/ImageManipulation_Program.cs.txt

Note you need to add the System.Drawing reference in order to get it to work with a new project.

Convert C# .NET Bitmap to grayscale

2008-11-17T13:10:00.000-08:00

Here's some code to convert a Bitmap into grayscale using luminance. Note, this does not change the color depth, rather it sets the pixels to grey within a 24bit color space.

private static Bitmap ToGrayscale(Bitmap bmIn)
{
Bitmap bmOut = new Bitmap(bmIn.Width, bmIn.Height, PixelFormat.Format24bppRgb);

for (int x = 0; x < bmIn.Width; x++)
{
  for (int y = 0; y < bmIn.Height; y++)
  {
   Color px = bmIn.GetPixel(x, y);

   int luminance = (int)((0.3f * px.R) + (0.59 * px.G) + (0.11 * px.B));

   bmOut.SetPixel(x, y, Color.FromArgb(luminance, luminance, luminance));
  }
}

return bmOut;
}

C# .NET reading a bitmap, resizing / rescaling and saving as a TIFF

2008-11-17T13:04:00.000-08:00

Here's some code to read in a bitmap and save a new bitmap at a quarter of the size. Note. a boilerplate method is needed to get the encoder for the write format. This makes it easy to change from TIFF to JPEG, PNG, etc.

// Load the bitmap from first argument filename
Bitmap bmIn = new Bitmap(inputFilename);

// Create a new bitmap from the first, scaling down as we go
Bitmap bmOut = new Bitmap(bmIn, new Size(bmIn.Width/2, bmIn.Height/2));

// Create an parameter set that we use to save the file
EncoderParameters encParams = new EncoderParameters(3);

// Set some parameters
encParams.Param[0] = new EncoderParameter(Encoder.ColorDepth, 24L);
encParams.Param[1] = new EncoderParameter(Encoder.Compression, (long)EncoderValue.CompressionNone);
encParams.Param[2] = new EncoderParameter(Encoder.SaveFlag, (long)EncoderValue.MultiFrame);

// Save bitmap to second argument filename
bmOut2.Save(outputFilename, GetEncoder(ImageFormat.Tiff), encParams);
private static ImageCodecInfo GetEncoder(ImageFormat format)
{
ImageCodecInfo[] codecs = ImageCodecInfo.GetImageDecoders();

foreach (ImageCodecInfo codec in codecs)
{
if (codec.FormatID == format.Guid)
{
return codec;
}
}
return null;
}

Out of Memory / OutOfMemoryException on Graphics.FromImage with PixelFormat.Format16bppArgb1555 or PixelFormat.Format16bppGrayScale

2008-11-17T12:52:00.000-08:00

If you have some code like this:

Bitmap bmp = new Bitmap(100,100, PixelFormat.Format16bppArgb1555);
Graphics gfx = Graphics.FromImage(bmp);

You will find you get an out of memory exception upon executing the code. The reason? .NET does not actually support Format16bppArgb1555 or PixelFormat.Format16bppGrayScale.

Visual Studio Errors

2008-11-05T04:45:00.000-08:00

Thanks to Matt at Siemens for this one.

"If you get similar errors to below then deleting everything in the C:\Documents and Settings\\Local Settings\Application Data\Microsoft\VisualStudio\\ProjectAssemblies will solve this. Do not delete anything above this directory as you will lose all your toolbars and settings."

Error 4 The "ResolveAssemblyReference" task failed unexpectedly.

System.IO.IOException: Incorrect function.

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)

at System.IO.Directory.GetDirectories(String path, String searchPattern, SearchOption searchOption)

at System.IO.Directory.GetDirectories(String path, String searchPattern)

at Microsoft.Build.Tasks.SystemState.GetDirectories(String path, String pattern)

at Microsoft.Build.Tasks.ReferenceTable.FindSatellites(Reference reference)

at Microsoft.Build.Tasks.ReferenceTable.FindAssociatedFiles()

at Microsoft.Build.Tasks.ReferenceTable.ComputeClosure()

at Microsoft.Build.Tasks.ReferenceTable.ComputeClosure(DependentAssembly[] remappedAssembliesValue, ITaskItem[] referenceAssemblyFiles, ITaskItem[] referenceAssemblyNames, ArrayList exceptions)

at Microsoft.Build.Tasks.ResolveAssemblyReference.Execute(FileExists fileExists, DirectoryExists directoryExists, GetDirectories getDirectories, GetAssemblyName getAssemblyName, GetAssemblyMetadata getAssemblyMetadata, GetRegistrySubKeyNames getRegistrySubKeyNames, GetRegistrySubKeyDefaultValue getRegistrySubKeyDefaultValue, GetLastWriteTime getLastWriteTime)

at Microsoft.Build.Tasks.ResolveAssemblyReference.Execute()

at Microsoft.Build.BuildEngine.TaskEngine.ExecuteInstantiatedTask(EngineProxy engineProxy, ItemBucket bucket, TaskExecutionMode howToExecuteTask, ITask task, Boolean& taskResult) MyProject

Encrypting ASP.NET web.config impersonation data

2008-08-28T12:13:00.000-07:00

It always irked me that using impersonate for my ASP.NET web apps meant I had to expose the username and password in plain text. Eg./

<identity impersonate="true" username="BOB" password="NOTSOSECRET">

Luckily it irked someone at Microsoft too and they came out with a hotfix allowing the credentials to be stored in encrypted form in the registry.

http://support.microsoft.com/kb/329290

Microsoft Source Analysis

2008-05-30T07:01:00.001-07:00

Microsoft have released their previously internal tool Source Analysis (aka StyleCop). This allows project- and file- level source code analysis checking it meets a whole raft of style rules developed by Microsoft. Some you will agree with, some you won't. The good news is you can turn off rules you don't like, the bad news is you cannot develop your own.

blogs.msdn.com

Visual Studio 2008 IDE C# - UK Launch Notes

2008-03-27T05:33:00.000-07:00

VS08 allows seamless (re)targeting of apps to .NET 2.0, 3.0 and 3.5. This provides graceful upgrading of existing applications. Applications initially targeted for 3.x can also be targeted to .NET 2.0 as long as features of 3.x have not been used.
Properties have now been improved so the implementation of the get and set methods do not need to be provided if the default implementation is all that is required. Eg./
```
public string FirstName { get; set; }
```
This creates a hidden private member variable.

It is now possible to use braces to initialise multiple object properties:

Thread myThread = new Thread(MyThreadMethod)
{
Name="my thread",
IsBackground=true
};

Anonymous types can be created using the 'var' keyword. This is NOT weak typing - C# knows what the type is and it cannot change. Anonymous types are used in instances where the developer does not know the type, the primary instance of this being LINQ. Eg./
```
var anonobject = new {Name = "Bob", Age = "40"};
```
Anonymous methods are best described in detail here:

http://msdn.microsoft.com/msdnmag/issues/04/05/C20/#S5

Partial methods can be used as a lightweight event system in cases where a large number of objects are held in memory (IE. LINQ) or where the method may be optionally implemented. Eg./

partial class MyClass
{
MyClass()
{
 DisplayMessage(); // Only called if definition exists
}

static partial DisplayMessage(); // Not implemented
}

partial class MyClass
{
static partial DisplayMessage() // Optional
{
 Console.WriteLine("MyClass created");
}
}

Extension methods allow you to 'tack on' functionality to classes that you would otherwise not be able to due to inheritance being prevented (IE. .NET classes). Eg./

public static class MyExtensions
{
public static bool
  IsValidEmailAddress(this string s) // "this" keyword
{
 Regex regex =
  new Regex(@"^[\w-\.]+@([\w-]+\.)+[\w-]{2,4}$");
 return regex.IsMatch(s);
}
}

public class MyClass
{
public void MyMethod()
{
 string email = "paul@here.com";

 // Extension method used like a standard string method
 if (email.IsValidEmailAddress())
 {
  Console.WriteLine("Valid!");
 }
}
}

C# Programming - Part 2 - Conditions

2008-03-27T01:12:00.001-07:00

Scope of work

The work is split into two parts: the first is a training part where you are encouraged to use books and the Internet to help you complete the questions; the second is a test part where you should complete the questions with no external help. The pass score is 80%.

1. if, else if, else

2. Nested conditions

3. && and

4. switch statement

Training Questions

Create console applications to:

1. Read a number from the console (use ReadLine and Int32.Parse) and using if, else if and else output one of the following

“The number is negative”

“The number is lower than 100”

“The number is 100 or above”

2. Read two numbers from the console (one after the other) and by nesting if, else if and else output one of the following:

“The numbers are the same”

“The numbers are not the same”

– the first is lower than the second”

“The numbers are not the same

– the second is lower than the first”

3. Read two numbers from the console (one after the other) and using if, else if and else along with && output one of the following:

“The numbers are the same and are negative”

“The numbers are the same and are positive”

“The numbers are not the same”

4. Read a number from the console (use ReadLine and Int32.Parse) and using a switch statement with cascading, and depending on an input 1-10 output one of the following:

“You entered an even number” or

“You entered an odd number” or

“You entered a number that is not 1-10”

5. Read a number from the console (use ReadLine and Int32.Parse) and by nesting if, else if and else output one of the following depending on what range the number is in, and whether it is divisible by 5:

“The number is 0-25 and is divisible by 5” or

“The number is 0-25 and is not divisible by 5” or

“The number is 26-50 and is divisible by 5” or

“The number is 26-50 and is not divisible by 5” or

“The number is 51-75 and is divisible by 5” or

“The number is 51-75 and is not divisible by 5” or

“The number is above 75”

* Note. To check if a number is divisible by 5 you can use:

(number % 5 == 0)

Test Questions

Create console applications to:

1. Read two numbers from the console (one after the other) and using if, else if and else output one of the following:

“The first number is lower than the second”

“The second number is lower than the first”

“The numbers are the same”

2. Read two numbers from the console (one after the other) and by nesting if, else if and else output one of the following:

“Both numbers are negative”

”Both numbers are positive”

“The first number is positive, the second is negative”

“The second number is positive, the first is negative”

3. Read a letter from the console and by if, else if and else with (or) output one of the following:

“1” (if the letter is a, b or c)

“2” (if the letter is d, e or f)

“3” (if the letter is g, h or i)

“4” (if the letter is j, k or l)

4. Read a number from the console (use ReadLine and Int32.Parse) and using a switch statement with cascading, and depending on an input 1-9 output one of the following:

“You entered the number one, two or three” or

“You entered the number four, five or six” or

“You entered the number seven, eight or nine” or

“You entered a number that is not 1-9”

5. Read three numbers from the console (one after the other) and by nesting if, else if and else sort the three numbers and output them in order:

Example: input numbers: 2 5 and 1

Output: 1, 2, 5

Example: input numbers 100, 45, 30

Output: 30, 45, 100

C# Programming - Part 1 - Strings

2008-03-22T14:16:00.000-07:00

As languages become more high-level it seems training material focuses more on dragging and dropping components and interacting with the IDE than teaching fundamentals like problem solving and creating logical procedural code. From this deficit I created a set of C# Programming tasks that concentrate on problem solving and creating logical code. This work is made available by kind approval of Siemens AG Industry.

The tasks are provided free of charge but may NOT be copied or redistributed. The work is copyright and all rights are reserved.

Scope of work

The work is split into two parts: the first is a training part where you are encouraged to use books and the Internet to help you complete the questions; the second is a test part where you should complete the questions with no external help. The pass score is 80%.

1. Manipulating strings

2. Substrings

3. Upper/lower case

4. Concatenation

5. Locating a character in a string

6. Replacing characters in a string

Training Questions

Create console applications to:

1. Store your name as a string literal and output it to screen.

2. Read a string from the console and output it in upper case.

3. Read a name from the console (for example "Bob") and output the following:

"Hello, Bob!"

4. Use the following string:

string telephone = "0800 123 4567";

and replace the space with a - so that the output is:

0800-123-4567

5. Use the following string:

string alphabet = "abcdefghijklmnopqrstuvwxyz";

and output six letters starting from e, so that the output is:

efghij

6. Create a simple CSV (comma separated values) parser capable of dealing with a string read in from the console. The string will always be in the format:

firstvalue,secondvalue,thirdvalue

Some example strings:

"1,2,3"

"aa,bb,cc"

"apple,banana,pear"

The output (using the third string as an example) should be output to screen as follows:

First value: apple

Second value: banana

Third value: pear

Test Questions

Create console applications to:

1. Store your name in a string and output it to the screen in upper case.

2. Use the following strings:

string countryCode = “44”;

string areaCode = “1260”;

string number = “123456”

along with string literals to output:

+44-1260-123456

to screen

3. Use a string as follows:

string test = "siemens automation and drives";

Replace every i character with a 1 and then output the string in upper case as so:

S1EMENS AUTOMAT1ON AND DR1VES

4. Parse the following string:

string surnameforename = "Siemens, Werner";

so the forename appears before the surname as follows:

"Werner Siemens"

store it in a string variable and output it to screen.

5. Read in a string from the console that will always be in the following format:

+

This should be done using the substring function.

For example:

12+4

Where the example would output:

First number: 12

Second number: 4

Resizing a logical volume without unmounting in Linux

2007-11-23T03:00:00.000-08:00

I had a problem where the logical volume our kickstart image created was too small, despite the disk having enough space. As you see it only allocated 4GB:

[root@example sbin]# df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       4128448   3716732    202004  95% /
/dev/sda1               101086     18598     77269  20% /boot
none                   8203424         0   8203424   0% /dev/shm

The logical volume can be resized by running lvm (as root) at the command prompt and the following command (For 50GB):

lvm> lvresize -L 50G VolGroup00/LogVol00

The issue we then had is that this isn't committed! Exit lvm, and then type the following:

ext2online /dev/mapper/VolGroup00-LogVol00

After a few minutes of processing you will now find you have a larger volume:

[root@example sbin]# df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      51606140   3721676  45263616   8% /
/dev/sda1               101086     18598     77269  20% /boot
none                   8203424         0   8203424   0% /dev/shm

Syntax highlighting in colour in HTML

2007-08-07T14:55:00.001-07:00

I recently found a fantastic feature in GVIM that allows me to syntax highlight my code and turns it into HTML. I'm sure there are tools out there specifically for this job, but given the convenience of having this built into my editor I couldn't help but mention it.

You can get GVIM from their website. When you've loaded your code up (and practically every language is supported), click the Syntax -> Convert to HTML menu option.

Here's a sample:


<?php

if (isset($_GET['album']))
{
        //echo 'gallery: ' . $_GET['album'];
        $d = dir('Photos/' . $_GET['album']);

        $bFirst = true;
        while ( ($file = $d->read()) !== false)
        {
                if (strpos($file, 'tb.jpg') !== false)
                {

Search Engine Effectiveness - Practical Test

2007-08-02T15:02:00.000-07:00

I've been wondering recently how effective alternate search engines are compared to Google. As a small test I decided to take 25 searches I had performed in the last week and search on Google, Yahoo and MSN Live Search to compare results.

The following table shows the results. A score of zero means the target info was found on the search page itself (I didn't have to click through to the site). A number 1-10 is the position of the search result with the target info. A blank score means the target info was not found on the first page of links.

The score is a sum of each search position negated from 11. As so:

Target info on search page: 11-0 = 11
Target info on page result 4: 11-4 = 7
Target info on page result 10: 11-10 = 1
Target info not found: 0

Conclusions:

I was pretty suprised Google was so far ahead compared to Yahoo and MSN. Google won because it was better at finding more niche searches, whereas Yahoo and MSN failed completely. For more obvious searches all three performed flawlessly and may do for day-to-day searches.

Google tended to either have the result first, or not at all. Yahoo had a few useful results in the top five. MSN had some results nearer the bottom of the top 10. This is significant for MSN because much lower results seriously increases the time taken to find what you want.

Some oddities included Yahoo having numerous entries that were highly irrelevant and MSN having a curious slant to open source results, particularly source code.

Data set Pagination in MSSQL not MySQL!

2007-06-27T01:04:00.001-07:00

A poster by the name of Stripe-man has come up with a really simple way of paginating data in SQL Server. This has always been easy with MySQL using the LIMIT keyword, which is missing from SQL Server 2000. (SQL Server 2005 added a row count similar to Oracle's, I believe.)

Here is the template:

SELECT TOP <PAGESIZE> * FROM task_log WHERE id NOT IN (SELECT TOP <PAGEOFFSET> id FROM task_log ORDER BY ID) ORDER BY ID

So, for example, to display 10 items from 51-60 we would do:

SELECT TOP 10 * FROM task_log WHERE id NOT IN (SELECT TOP 50 id FROM task_log ORDER BY ID) ORDER BY ID

What this essentially says is: show me the top ten results that do not appear in the top fifty results.

PHPBuilder.com - View Single Post - Pagination with PHP / MsSQl not MySQL!

Google adding new business customers at a rate of 1000 per day since it launched in February

2007-06-26T07:40:00.001-07:00

Google is making a pretty good progress getting business customers on to its mail platform, at a rate of 1000 per day. However Rishi Chandra, product manager for Google Apps, is perhaps being a bit optimistic in terms of product development, which in the case of GMail we have seen relatively little of since its beta inception.

"At the end of the day, Google Apps is about innovation," said Chandra. "You don't have to wait for a major release to get these features. It happens in real time. ... It's taking the speed and innovation of the consumer world and applying it to the business world."

The problem is, it seems we do have to wait. Whilst Windows Live Mail and Yahoo Mail have seen dramatic improvements in the user interface, Google can be seen lagging behind with what cynics might even call a dated user experience.

As Information Week identifies:

"Such relentless change has the potential to leave users lost, but Chandra insists Google's focus on the user experience means that new features are easy to understand and don't require new training."

Unfortunately to produce a high level of innovation along with consistency of user experience is not an easy task, despite Chandra's insistence, but only time will tell what emphasis Google chooses, or whether they will attempt the impossible.

Google Apps Opens Door To Migrating E-Mail Users -- Google Apps -- InformationWeek

Arithmetic overflow error converting expression to data type int

2007-06-17T14:59:00.000-07:00

In SQL Server I get the following error when I try to COUNT(*) rows in very large tables:

"Arithmetic overflow error converting expression to data type int."

Googling and sifting through pages about the same problem for SUM( ), I found you can use this function:

COUNT_BIG(*)

This uses a bigint rather than int for storage.