ColdFusion Muse

DTS Over Coldfusion - Revisited

Mark Kruger August 17, 2006 1:29 PM MS SQL Server Comments (3)

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:

<cfscript>
// 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>
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....

Read More
  • Share:

Coldfusion and DTS - Easy Exporting

Mark Kruger July 26, 2006 4:43 PM MS SQL Server, Coldfusion Tips and Techniques Comments (26)

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 More
  • Share:

Handling Unicode Data types in MS SQL

Mark Kruger July 6, 2006 10:28 PM MS SQL Server Comments (8)

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 More
  • Share:

Inserting and Updating in a Single Query Statement

Mark Kruger June 23, 2006 10:20 AM MS SQL Server, Coldfusion & Databases Comments (8)

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 More
  • Share:

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!

Read More
  • Share:

Using LIKE with special Characters (like the percent sign)

Mark Kruger December 27, 2005 6:29 PM MS SQL Server Comments (8)

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 More
  • Share:

Handling NULLs in MS SQL

Mark Kruger October 28, 2005 12:05 PM MS SQL Server Comments (0)

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.

  • Share:

Using LIKE Wildcards in Your Queries

Mark Kruger October 27, 2005 10:32 AM MS SQL Server, Coldfusion & Databases Comments (0)

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 More
  • Share: