ColdFusion Muse

DTS' Other Uses

One of the nicest things about MS SQL is DTS. If you are a reader who has a visceral reaction to anything nice being said about Microsoft you should grab a paper bag so that you don't hyperventilate as we go forward. Shallow breaths... shallow breaths.... ok - ready? Here we go. I suppose that most developers are exposed to DTS (Data Transformation Services) as an import-export mechanism for Microsoft SQL Server. The most common use is during deployment, rehosting or setting up a development environment. There are a host of other things for which you can use DTS that perhaps you hadn't thought of. For example:

  • Moving Data from one DB platform or format to another - For example, with 2 ODBC connections to 2 Oracle servers you could import and export data from one Oracle server to another. I know that those of you with experience in Oracle will gasp with horror at this though - but migrating data around in Oracle is unnecessarily difficult (as is just about everything else about Oracle). You could transfer data from a DBase file to an access file. You could move information from a proprietary platform running on Cobol (as long as you had a driver) into a flat file or into MSSQL. It's a very nifty mechanism with all sorts of possibilities.
  • File Drop Import - We use DTS to "pickup" a file who's name may not be consistent. It is even possible to FTP a file for import.
  • Data Column Validation - With a minimal amount of scripting you can check values prior to importing them. So, for example, you can verify if a field is a number or is parsable into a date, or is populated or not.
  • Complex SQL Tasks - You can add SQL task and even COM tasks to a package extending it well beyond just transferring data.

For example, we have a task that imports stock data. It is set to run every weekday. On days that are holidays it would run and sometimes create problems with the Bid and Ask. We needed a way to check and see if it was a holiday. If it was a holiday we wanted to terminate the process. Here is what our DTS Guru came up with. Using the package designer, at the beginning of the package add an ActiveX task that looks like this (this is the generic version).

Function Main()

Dim cn
Dim rs
Dim Flag

Set cn = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")
cn.ActiveConnection = "Provider=sqloledb;Data Source=database;Initial Catalog=database;User Id=username;Password=password;"
cn.CommandType = 1 'adCmdText
cn.ActiveConnection.CursorLocation = 3 'adUseClient
cn.CommandTimeout = 60 'set to 1 min
cn.CommandText = "select * from [table]"

Set rs = cn.Execute()

If rs.RecordCount >
0 Then
Flag = "F"
Flag = "S"
End If

Set rs = Nothing
Set cn = Nothing
'msgbox "Result=" & Flag
If Flag = "S" Then
Main = DTSTaskExecResult_Success
Main = DTSTaskExecResult_Failure
End If

End Function
Make sure and set the task to "continue on success". The last few lines tell the story. If "Main" is set to the constant DTSTaskExecResult_Failure then the task will not continue. The result is a DTS task that is "smarter". We are able to put the execution of the task in the hands of the stakeholder of the site who only needs to be sure and maintain his table of trading holidays.

JDBC Drivers, Data Binding and Implicit Conversion

You may know that MSSQL allows for "implicit conversion" between data types. For example if you have a character column and you pass it a number without single quotes (as in myCharCol = 1), MSSQL will automatically convert the value 1 into a character for the purpose of the query. On a Coldfusion 5 server this behavior carries through seamlessly even when you are binding data using cfqueryparam. However, if you are using cfqueryparam in a query on a CF 5 server and also relying on implicit conversion within the same query you should know that you may have a problem getting that cfquery to work in Coldfusion MX. The issue is that JDBC "prepares" the statement by validating against the schema. Here's an example:


Cfqueryparam Fails When Using "WHERE EXISTS"

I have always been an advocate of Cfqueryparam. Binding your variables innoculates you against SQL injection attack, often results in speed improvements and lessens the load on your database server. It may even help with the laundry (that's the word on the street). This morning I ran across an error that is produced by the correct use of cfquery param. It has to do with using the clause "WHERE EXISTS" in your query. Here's the query in question.


Eliminating Duplicates In a Query When Distinct is not an Option

Here's a problem perhaps you have had. You want to select unique email addresses, first names and last names out of a database for a newsletter or to sell them a new mortgage or whatever. Being the nice guy that you are you don't want to send them multiple messages, so you want to eliminate duplicates, right? the problem is that SELECT DISTINCT... doesn't always work in this instance. For example, John Doe put his information in as John Doe in one case and John H. Doe in another. Selecting distinct for name and email will give you a duplicate name with the same email. Now obviously you could solve this problem in your Coldfusion code - but wouldn't it be nice to fix up the query?


Farcry Indexing Script for MS SQL

One of the downsides of a complex CMS is performance. I do not mean that a CMS can't perform well - it certainly can. I only mean that there is less margin for error when so many things are happening with each request. The more complex the application, the more the developer should pay attention to scalability issues. Probably nothing impacts your performance more than your database. I often tell customers who are prepared to spend money on hardward to buy the best server you can afford for your database server. In fact, a cheap web server and a solid Database server beat a great web server with a pokey db server every time. Anyway, I have been working with Farcry for some time now. I have used the information from Daemon on DB Indexing Strategies to make decisions about my Microsoft SQL Server indexing.

The suggestions they make are a bit generic. I have compiled my suggestions for indexing into an indexing script. I like using primary keys where it makes sense (where I need a unique index). Here's a tip. If you are in the habit of setting your primary keys in Enterprise Manager and you want a column other than the primary key column (or columns) to have a clustered index, make sure you create the cluster index first then set your primary key. Why? Because EM will automatically create a PK as a clustered index - which means you will need to drop it to create a clustered index anywhere else. Any way, here's the indexing script. Keep in mind that it will probably throw errors when you run it (null columns and the like) - so you may need to use it as a guide instead of simply running it in EM. If you have a pristine installation and you want the whole ting (constraints and defaults as well as just indexes and keys) give me a shout.

MS SQL's Casting Causes Consternation

If you are using MSSQL Server an Cfqueryparam, you should be aware of the old implicit datatype conversion. This helpful feature uses an order of precedence to accommodate code that is written to pass in a sql_variant data type. To put it another way, lets say you are comparing a column of the type "varchar" to the the number 1 or 2. If it was me, I would write "WHERE myCol = '1' OR myCol = '2'" - or perhaps use an "IN" clause. This code will always work. It is saying "compare a character '2' to the character column myCol". Since they are both of the same type there is no conflict - but what happens if you mismatch the type?


Limiting the Databases Seen in MS SQL Enterprise Manager

Did you ever connect to a an SQL server using Enterprise Manager where the server in question had a hundred or so databases installed? If you have you will know there is a significant delay when you expand the tree to find your database. Then you will have to sort through all those other databases to find yours. The other databases are listed there even though you do not have permissions to access them. Not only is this a security risk, it is annoying and time consuming. There is a solution....

This tip comes from Russ (a.k.a. Snake) on the CF Guru list. Russ pointed us to this KB Article from Microsoft includes a stored procedure that modifies the process of creating that browse list and shows the connecting EM only the databases they are entitled to see. This speeds up the process of using EM. Plus, you really don't want everyone in the world to see that you named your database "Pookey". In my own test I found that it worked exactly as advertised - but make sure you read the whole article. There's an easy roll-back plan, just make sure (as always) that you understand how it is supposed to work.

More CF and DTS Troubleshooting

For those of you that have made use of my blog post on DTS and Coldfusion and the subsequent blog on Troubleshooting DTS and Coldfusion, I have a 2 more useful tips. One has to do with a way to trap errors on the server and get information about what's going on on the client - the second tip has to do with protocol selection and conflict.


Top and the UNION Query

A "UNION" query can be very useful. Take sorting for example. If I want to select 2 types of data from the same table using different conditions and order by my conditions union is a great tool. See my previous post on Using UNION in your queries. If you also like using TOP you may run into an unexpected behavior when using UNION. Let's say you had 2 tables, portalUsers and intranetUsers and you wanted to get the most recent entries in each that were unapproved. If you try something like this:

<cfquery name="myquery" datasource="mydsn">
      SELECT    TOP 1 fname, lname,
            'Portal' AS sourceTable,
      FROM    portalUsers
      WHERE   unApproved = 1
      SELECT TOP 1 fname, lname,
            'Intranet' AS sourceTable,
      FROM   intranetUsers
      WHERE   Approved = 0
   ORDER BY dateAdded      
You might expect to get 2 records equal to the most recently added portal user and the most recently added intranet user - right? You could take the first record and you would be good to go. Actually, unless the "portalUsers" table has a clustered index ordered by dateAdded, this is not the case.


DTS Over Coldfusion - Revisited

If you stumbled onto my Coldfusion and DTS and tried to make use of it - you may have stumbled onto a particularly ticklish error that's difficult to troubleshoot. First however, I want to rectify an omission that may have caused you trouble from the outset. This DTS package execution does not automatically throw an error if it fails. It requires that you set a property - "FailOnError" - true. Here's the revised code:

// create dts package object
pkg = createObject("COM","DTS.Package");
// load package
pkg.FailOnError = "true";
// execute
Setting it to true will cause a failure to throw an error to the calling page. In other words, without it, you get nothing and you think it has succeeded. Now for that tricky bug....


Coldfusion and DTS - Easy Exporting

What do you do when you are required to provide a CSV export of a large dataset from MS SQL Server? You could use query2csv and export it in Coldfusion - but don't be surprised if you end up taking a long time to complete that request. Coldfusion, for all it's advantages, it is not suited to to this sort of thing. We had a process that exported 30,000+ records (just a few fields) for the purpose of sales calculations. In Coldfusion this resulted in a 6 meg file - that doesn't sound like much, but the process could take 5 minutes or more. We thought of DTS, but one of our requirements was to make the file accessible via FTP. Fortunately there was an easy way.


Handling Unicode Data types in MS SQL

We have a customer who wants to support Asian languages. They have a lightweight CMS tool that they use to update portions of their website. Our first task was to duplicate this functionality for each language supported. Our first hurdle was the Chinese character set. We could update the DB directly through cut and paste, but the CF code we were using resulted in inscrutable question marks. We were using CFQUERYPARAM and none of the character types we tried worked. It looked as if we were up against a great wall.


Inserting and Updating in a Single Query Statement

This is a tip about something I do regularly. No, I'm not talking about forgetting to put down the seat. I'm talking about coding. Let's say you have an application that collects user data in a wizard-like format using a few steps. You allow folks to go back and forth between steps and edit what they've done. How do you handle the insert on that first step without creating duplicates when users return to that step? There are several approaches to this problem:


Great Tip - Capturing "PRINT" output from T-SQL

When I write stored procedures I often throw in a few print statements to help me understand what is going on. For example, if I have a routine that loops through table A and updates table B based on some conditions, I might include a print statement that said something like PRINT 'Condition Met' or PRINT 'Condition Not Met'. I could also output the values of a column or 2 to indicate why a condition was or was not met. That's a neat trick in Query analyzer and it makes debugging stored procedures easier. When you move the procedure to CF the PRINT output is lost or ignored. That is usually exactly what you want. But what if you did want to see the PRINT output? Is there a Coldfusion way to do id? Thanks to this great tip from Shlomy Gantz you can!


Using LIKE with special Characters (like the percent sign)

CF Muse Reader Asks:
How do you select a record containing a "%" using the LIKE keyword in sql

Ah... I love the easy ones. If you have a character column that contains the phrase "50% over gross" and "5% over gross" how could you construct a "LIKE" clause that would capture JUST the "5%" over gross if all you had to go on was "5%"? You see? If you did the following...


More Entries

Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.