jump to navigation

NOTE: The spam filter is being unusually aggressive. If you comment does not immediately appear, it has simply been placed in moderation and I will approve it as quickly as possible. Thank you for your patience.

"Murphy was an optimist!"

Calling SQL gurus – Is there a way to programmaticly prove data accuracy? January 10, 2014 4:02 pm

Posted by Doug McCaughan in : MS SQL, Programming, Technology
, add a comment

Say I have an existing MS SQL database with many tables. Now I’m going to import many more records. From a quality assurance standpoint, I’d like to be able to confirm that the existing data prior to the import was not inadvertently altered during the import of the new data (since the import includes some manipulation of the new data).

Is there a magical way to do this. I’m stuck on comparing record counts but that does really tell me if the existing data was altered during the import process.

Conceptually, I’d like to be able to essentially do something like a hash of the complete dataset before the import. Then after the import, query for all the old records, rehash and compare the two hashes.


add a comment

SQL Query Conundrum June 17, 2013 9:43 am

Posted by Doug McCaughan in : MS SQL, Programming, Technology
, add a comment

Here’s a interesting SQL challenge:

Say you have a query returning a result set that in some cases one row cancels out another. For example, let’s say there are 5 columns: A B C D E. One result possibility is: row 1- A B C 4 E with row 2- Z Y X 9 E Naturally those are completely separate data elements. But when row 1 is A B C 4 E and row 2 is A B C -4 E row two is a data correction to row 1 (think double entry accounting) and row 1 and row 2 need to be excluded from the result set. So, right now if the result set returned 20 rows and had the situation above, we really only want to be returning 18 rows. What’s a good approach to this?

Here’s a better representation of the problem:

Row Name Account Rank Value Status

  1. G H I 5 J
  2. A B C 4 E
  3. A B C -4 E
  4. Z Y C 4 E
  5. A B C 7 E
  6. Z Y C 22 E

We really want to return rows 1, 4, 5, and 6.

add a comment

Seeking Next Project March 19, 2009 8:26 am

Posted by Doug McCaughan in : Announcements, ColdFusion, CSS, HTML, JavaScript, jQuery, MS SQL, MySQL, Of Interest, PHP, Programming, Technology
, add a comment

I am coming to the end of a project and have an opening in my schedule. My strengths are programming in PHP, ColdFusion, JavaScript and jQuery, CSS, and HTML. I most frequently work with MySQL or MS SQL but can work on most database platforms. Code I write validates and is W3C compliant. I can also write Section 508 compliant code. I carry a project from concept to production as project manager, independent developer, and/or project team member. In addition to custom original code, I work well with open source projects and APIs. I usually telecommute but am not adverse to working onsite or traveling. I am not an artist and typically hire a graphic artist to provide the design for each project. I enjoy WordPress customizations (but have not done custom work to either Reality Me or Domestic Psychology). Please send referrals or inquiries to jugger@gmail.com or call +1-865-898-7189. Thank you!

add a comment

Do you store images in a database? July 11, 2008 11:15 am

Posted by Doug McCaughan in : .NET, ASP, c#, MS SQL, MySQL, Programming, Technology
, 4comments

As I have built PHP and ColdFusion applications over the years, I have had to deal with image and file uploading from the application. Browsers were not designed for moving files from a client to a server; specifically, browsers were designed to receive files from a server to a client. To move files from a client to a server, an FTP program is used. (My favorite FTP client is WinSCP.) Now, a form can be written and used to upload a picture, movie, or other file from the client (you) to the server. You probably do this quite frequently with Flickr, Youtube and so forth. It is convenient from the enduser point of view because it removes the need to learn new software (the ftp client) and prevents having to get an administrator to create a username and password for each user on the server. Plus, by managing the files through the web application instead of the FTP program, you can programmatically control what is being allowed onto the server so that your user doesn’t maliciously upload an executable designed to damage the server rather than that family vacation photograph.

As a programmer, I have to make decisions about where to store these uploaded files. A common approach is to have a directory on the webserver which cannot be accessed by a browser (that is, the only way to get to the file is through the programming). The filename is then stored in the database. As an independent consultant, I work with different programmers/designers with varied skillsets and differing philosophies on coding. Some coders, like Eric Wise, actually prefer to store the files in the database itself. Eric claims:

…this is pretty sloppy and difficult to manage especially if for some reason you want to reorganize the data… [Source, Eric Wise, Images, Thumbnails, SQL Server, and ASP .NET – Level 200]

Eric describes a process of storing images in SQL Server 2005, automatically compressing the images, and generating thumbnails using ASP .NET. THis is interesting to me as I am wanting to learn C# to add to my skillsets.

I am concerned about database size and performance decreased caused by storing images/files in a database rather than storing the file in a directory and only storing the filename in the database. Jeffrey Palermo, a commenter on Eric Wise’s post, expresses the same concern:

Jeffrey Palermo said:

I’ve considered doing a photo album this way, but the main drawback is the size it makes the database. I have about 4GB of images. Do I really want that much in my database just for images. That also makes the bandwidth between web and db server highly used. Perhaps it’s no big deal, but to date I’ve kept my photos on the web server. Please post if you find no issues with the size of the data in the database when you have _lots_ of pictures there.

And Eric responds:

Eric Wise said:


With the compression tool I referenced, the original image shown was over 2MB, now it’s 165KB.

Having many large files would definately cause a performance issue, but with compression and the fact that it will probably be rare to have more than 2-3 pictures of an asset I’m not all that concerned about it.

What is your approach to storing images from a web application? See also.


Collation? May 28, 2008 5:19 am

Posted by Doug McCaughan in : MS SQL, Programming, Technology
, add a comment

You mean like getting the papers from the copier in order?

You know, I have probably exported and imported data from hundreds of databases at this point in my life. And never, I say never, have I ever had a collation problem. Collation has to deal with how a database handles issues of sort order, case sensitivity, and sensitivity to accents. It becomes important when your database is not US centric or is going to serve an International audience. Last night it got me.

I tried directly updating data on a server that is probably located in Amsterdam and ended up with:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation


add a comment