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).
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"
Else
Flag = "S"
End If
Set rs = Nothing
Set cn = Nothing
'msgbox "Result=" & Flag
If Flag = "S" Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
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:
Read MoreCfqueryparam 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.
Read MoreEliminating 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?
Read MoreFarcry 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?
Read MoreLimiting 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.
Read MoreSites to Watch
Aggregators
RSS
