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.LoadfromSQLServer
("NameOfSQLServer",
"SQL_Username",
"sql_password",
0,
"",
"",
"",
"nameOfTheDtsPackage",
"");
pkg.FailOnError = "true";
// execute
pkg.Execute();
</cfscript>
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.
Read MoreHandling 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.
Read MoreInserting 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:
Read MoreGreat 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!
Read MoreUsing 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...
Read MoreHandling NULLs in MS SQL
The blog on Waterswing.com has a great entry on handling nulls in MS SQL. If you ever wondered when to use "= NULL" (if ever) and when to use "IS NULL" you should check it out. Also note the excellent tip by Barney in the comments on using the <=> operator in conjunction with CFQUERYPARAM allowing you to avoid a cumbersome CFIF statement.
Using LIKE Wildcards in Your Queries
I often comment that learning the ins and outs of your database platform can do just as much for your productivity as learning advanced Coldfusion coding techniques. Here's another example. Suppose you have a "LIKE" expression in your query that checks against a stored character value. You use the expression to allow a user to do a search against a character column in the database. If the column in question contains an underscore you might end up scratching your head at the results.
Read MoreSites to Watch
Aggregators
RSS
