Creating custom sort rules for SQL Server

Sometimes you'll want to sort rows in your SQL queries in a way other than the default available sorting methods. That's just the situation I found myself in the other day. I didn't want to create a new column. Take the following SQL:

view plain print about
2FROM user_ranks
3ORDER BY user_rank


SQL Prettifier (Lint) Tool

I have spent countless hours of my life staring at SQL code. While I made every effort to keep my own SQL neat, it's not always easy to decipher errors in complex queries - especially those in legacy code or written by developers who don't share my passion for code formatting.

Today I was fortunate to find an excellent SQL prettifier that analyzes code (like a lint-style tool) and formats it with proper capitalization, indentation, and so forth.

Here's the link; I hope it saves somebody time in debugging and managing SQL code as it has just done for me.

MSSQL Job Error: The owner () of job <Job Name> does not have server access

The other week I noticed some of the Jobs that we have scheduled in our MSSQL setup had started repeatedly failing. They were giving the message, "MSSQL: The owner () of job <Job Name> does not have server access".

I thought this was weird, especially since other jobs were running fine. I investigated a little further and found this forum that discussed this issue.

What I gathered is that if using the DOMAIN/Username (Windows Login) syntax for the Owner field when setting up a job, sometimes after a server restart it has trouble verifying the admin privileges to allow the job to actually run.

The ways to fix this are:


Help Wanted: Web Application Developer and Mobile Application Developer

These positions were filled in November 2010. Please check our Employment Openings page for the latest information about jobs at Ravenglass Technologies.

Ravenglass is seeking two application developers: one Web Application Developer and one Mobile Application Developer. Will consider contract, contract-to-hire, or full-time with salary plus full benefits. Flexible commuting arrangements considered

Web Application Developer Degree in Computer Science or related field required. Must have demonstrable experience with HTML, XML, AJAX, and SQL. Web application development experience in ColdFusion, JSP, ASP.NET, or PHP is also required. Mobile application development experience is a plus.

Mobile Application Developer Degree in Computer Science or related field required. Must have professional mobile application development experience with iOS or Android OS. Web application development (ColdFusion, PHP, ASP.NET) and HTML5 knowledge are a strong plus.

Professionalism and strong communications skills are a must. Apply with resume and cover letter to

Forcing indexes to run in SQL

Did you know you can actually force an index to run if you'd like? SQL table hints are the key here. Using these hints you can indicate that you'd like the query to give preference to once index or another. This can sometimes be beneficial if you'd think that one index may be better than one the SQL query tools automatically select.


Error: Value can not be converted to requested type

This was an interesting error that I encountered today. About two months ago, I added a bunch of cfqueryparams to the page that began returning this error today, "Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type.". I assumed the value being passed to the cfqueryparam was actually invalid.


Another Simple Tip: Selecting the last inserted ID

I know that when I'm using SQL I find myself frequently having to get my ID that I just inserted into a table, so today I'm going to sharesome of the tools I use to get the last ID I inserted into a database.  Sometimes when beginning to learn SQL you may feel the need to create a transaction and do a select for the greatest value of your primary key field to get the identity.  Although this sounds great, firstly, it requires a transaction, and secondly, it doesn't take into account non-int or non-incremented keys. 


Reading an XLS file using CFQUERY

I haven't really seen a lot of documentation on how to read an XLS file into Coldfusion. Luckily, one of my colleagues was able to point me in the right direction. I am now here to write up a blog post for future reference.

Once you set up the new datasource in the CF Administrator (see comments below), it's really simple (at least more simple than parsing through the other file formats that I've had to do far). Basically, we just read it in as a query:


SELECT COUNT(*), The Basics

In SQL, COUNT() is an aggregate function that we can use to count the number of rows in a query. We can also use this function to figure out if there are duplicate records in a table.


More tips for SQL efficiency: boolean values in SQL

I'm always looking for ways to speed up my SQL Server queries.  Here's a tip that I used just today to speed up a search query, and I thought maybe it would be useful for all of you.

First up, make sure to include an execution plan by right-clicking on your query window and selecting "Include Actual Execution Plan"

Let's take a look at the following query:

view plain print about
1SELECT DISTINCT a.author_id, a.author_name, b.book_id, b.book_title
2FROM authors a
3INNER JOIN books b ON a.author_id = b.author_id
4WHERE a.author_id = 27
5AND b.book_published = 1
6AND b.best_seller = 1

As you can see, we have two tables, books and authors. Each of these tables are joined to show the author names for each book, and we filter by books that are published and are best sellers. This is where we'll be concentrating.


Previous Entries / More Entries