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.

[More]

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. 

[More]

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:

[More]

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]

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:

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.

[More]

Good afternoon everyone, today were going to talk about meta-data.  Specifically data relating to tables in SQL Server.  Have you ever wanted to grab all of the column names of a table for some operation?  Well, here is a break down of a technique that can be used to do it.

The first table that we'll be working with is the "sysobjects" table.  This table contains all objects within a database.  Objects are things such as views, tables, and stored procedures.  If you want to see a full collection of this you can query this table and see it first hand:

To filter only tables you can add an xtype of "U"

Now on to column names, we can get the column names for a specific table by joining this on the "syscolumns" table. 

There you have it, working with these metadata tables is incredible useful, good luck on using it!

 Good morning folks,

Here's a couple new tips today to make life easier in SQL.  When analyzing queries there are two tools which are great for examining resource usage.  The first is the STATISTICS IO.  You can turn this on by setting the results to text, and prepending your query with the following:

This will allow you to view the table read and read-ahead data related to the query.  Once it's on it will stay on within the query window you use it in.

Here is a simple example of how it''s used:

Whenever I use this functioanlity I use it in tandem with an execution plan.  To activate your execution plan, right click on the query window in the query analyzer, and select "Include Actual Execution Plan".  You then run your query and a new tab will appear next to the results pane called "Execution Plan".  This will show the "path" that SQL takes in deciding how a query is processed.  When you hover over icons in this window you'll find that you can see the data pertaining to each specific element of the query as well.  Keep an eye out for "CONVERT" functions and "CAST" functions in this window, as preventing these by typing your data correct can save a lot of headaches.  Additionally you can see which indexes SQL uses to grab it's data.

One thing I discovered recently was the power of the SQL CASE statement. I had to do multiple joins on multiple tables and then proceed to select data from a certain field that can actually be a different field depending on certain circumstances, as one unified field. Sounds fun, right?

One way you can figure out which field to use is to return them all and take care of the display logic on the Coldfusion side of things. The problem I had with doing this was the query I was building was probably going to be used in multiple places and I didn't want to tote that Coldfusion logic everywhere when I could just handle it in the query itself.

The first thing I learned was that apparently you cannot use SQL CASE statements to decide which tables to join on. What I have concluded you do is join all the tables you need to, then use the CASE statements to figure out which fields to select or filter on.

This leads me into why I am now a big fan of CASE statements in SQL - you now have unified data to present across the pages.

[More]

Hello everyone!  Let's talk SQL.  My names Jonathan and today I have a useful tip for a new view on queries.  Generally when someone queries two database tables that share an identity column, they join the data and end up with a collection of rows reflecting each result, essentially creating a "combined key".  To show this example more simply, take a look at the example below:

Fig-1

This query outputs a "client sales" (See Fig-1) result set that has 7 rows.  In this example such a small result set is hardly a problems, but let us imagaine that we have hundreds of clients, each with thousands of sales under their belt.  This result set would suddenly be very daunting.  Post-processing the result in your choice web service would be a performance headache, as to output it someone would have to either do a query of a query to show which clients have which sales, or this would have to be broken into a single cleint table query, and then individual queries  in a loop on the web server end.

Wouldn't it be great if there was a way to greatly reduce the row count, the number of queries, and the amount of processing on the web server.  The solution below does exactly that. 

Fig-2

The query above casts an nodeless XML result set into a varchar field and delimits it with comma's, creating a list (See Fig-2).  The last character is removed in the main select clause otherwise the last character would be a comma.

That's it!  You'll now be able to utilize new new field (in this case sale_list) in any list functions you may have.