For the most part, migrating your ColdFusion site from Microsoft's SQL 2000 server to SQL 2005 is a snap. Import the databases from 2000 to 2005, re-point your data sources to the new instance using the ColdFusion Administrator and you are done. No muss, no fuss. There is very little query code that you will need to change. Sometimes you needn't change anything in your ColdFusion code at all. Here is one that I found recently however that you may run across - especially if you are a fan of UNION queries. Here's the skinny.
Let's say you have a UNION query that merges 2 queries and orders the results. In each query you are selecting the same number of columns and the same columns and column orders. The tables may or may not be the same, but the aliases you use are the same in each query. Here's an example.
SELECT P.product, 'Active' AS prodStatus,
O.ord_no, O.cost, O.shipping,
O.status, O.OrdDt
FROM Orders O JOIN Products P
ON O.product_id = P.Product_Id
WHERE OrdDt Between '2005-07-18' AND '2005-07-19'
UNION
SELECT P.product, 'Active' AS prodStatus,
O.ord_no, O.cost, O.shipping,
O.status, O.OrdDt
FROM Orders O JOIN Prod_archive P
ON O.product_id = P.Product_Id
WHERE OrdDt Between '2005-07-18' AND '2005-07-19'
ORDER BY prodStatus, P.product
In this example I have chosen a union with 2 separate tables - product and product_archive. The ordering occurs by prodStatus (a derived column) and product, a column coming from prod_archive in one instance and products in the other. This code works find in MS SQL 2000. In MS SQL 2005 however, this code throws an error that says The multi-part identifier "P.product" could not be bound. Apparently, MS SQL 2005 does not treat the table alias "P" the same way as 2000. It looks at it and tries to figure out the table rather than just tying it to columns already selected - hence the reference to "multi-part identifier" and binding. To fix it however, you can simply remove the Alias like so:
SELECT P.product, 'Active' AS prodStatus,
O.ord_no, O.cost, O.shipping,
O.status, O.OrdDt
FROM Orders O JOIN Products P
ON O.product_id = P.Product_Id
WHERE OrdDt Between '2005-07-18' AND '2005-07-19'
UNION
SELECT P.product, 'Active' AS prodStatus,
O.ord_no, O.cost, O.shipping,
O.status, O.OrdDt
FROM Orders O JOIN Prod_archive P
ON O.product_id = P.Product_Id
WHERE OrdDt Between '2005-07-18' AND '2005-07-19'
ORDER BY prodStatus,product
This Causes MSSQL 2005 to only deal with ordering the column. Semantically I like this change (I'm never anti-semantic). It makes more sense to me since the alias actually refers to different tables in some cases. In hind-site I think perhaps that different aliases should be used altogether in a union query just for clarity. But when I do that it destroys the symmetry of the query blocks - it's not as obvious that the select portion is identical - so it results in more difficult reading and possibly debugging.
Related Blog Entries