More DTS Portability Tips
Thanks for Shane Dovers' great article about seven tips for building portable Data Transformation Services (DTS) packages ("DTS on the Move," June 2003, InstantDoc ID 38658). I wish I could have read this article 12 months ago. Another take on the topic could be, "How do I build in portability—and control the transfer from test to live data?" Our company has one development box and two live environments at opposite ends of the United Kingdom. We put a lot of work into importing data from flat files into various databases. We needed a test location for flat files and a test database, but when we sent the package to the live server, the locations needed to be live. We solved the problem by using Universal Data Link (UDL) data sources, which we always stored on the S:\ drive. By mapping the S:\ drive appropriately for each machine, we ensured that all sources and destinations were either live or test. (Unfortunately, this didn't work with the AS/400 queries, where the library name needed to be in the SQL explicitly.)
Another portability issue we uncovered was that when calling a child package, if the parent had been moved from test to live data, the references to the child package would still be pointing to the test child. We solved this problem by having the first task for each package delete the reference to the PackageID for each DTS Execute Package task.
Selling OLAP's Benefits
I was thrilled with Russ Whitney's article "Data Discovery" (June 2003, InstantDoc ID 38719), which explains how OLAP helps with the human parts of decision making. The article says exactly what I've been trying to convince company leaders of for the past year, hoping to persuade them to let me implement an OLAP solution. We're working with a 12-million-record sales-order database and a 5-million-record customer database in Advanced Revelation (AREV), a DOS-based multivalued database. All reports are also DOS based. I can export from AREV to .csv or XML files that I then load into SQL Server and Analysis Services cubes, using Data Analyzer as a front end. I'm going to show this article to our higher-ups to see whether it will make a difference. I first worked with OLAP 4 years ago using Cognos, and I believe OLAP is a great tool for getting more information from your data. The first thing my boss said when I originally mentioned the idea of OLAP was, "Can it duplicate what we're already able to do in AREV?"—just like Whitney's article mentions. Thanks again!
Unique Constraint for Set Relationships Query?
I enjoy Itzik Ben-Gan's T-SQL Black Belt column and have a question about the example in "Set Members and Relationships" (June 2003, InstantDoc ID 38515) that queries for orderid:
SELECT orderid FROM orderdetails WHERE productid IN(SELECT productid FROM #prodlist) GROUP BY orderid HAVING COUNT(*) = (SELECT COUNT(*) FROM #prodlist)
For this query to return accurate results, don't you need a unique constraint on the productid column in the Order Details table? The assumption is that quantity is always used to indicate where multiple quantities of the same item are to be included in an order. But without the constraint, you could possibly have an order record with order-details rows that represent more than one entry of a given productid for that order. This situation could result in a productid count for an order that equals the count of #ProdList, but the actual distinct number of items ordered wouldn't correspond to the number of distinct items in the #ProdList table. Wouldn't the following code be the safest approach in the absence of such a constraint?
SELECT sub.orderid FROM (SELECT DISTINCT orderid,productid FROM orderdetails) sub WHERE sub.productid IN(SELECT productid FROM #prodlist) GROUP BY orderid HAVING COUNT(*) = (SELECT COUNT(*) FROM #prodlist
Thanks for writing, and I'm glad you enjoy the column. The example query assumes uniqueness of productid, but within an order, because the results are grouped by orderid. Because the primary key in Order Details is created on the columns orderid and productid, such uniqueness is guaranteed. By knowing which columns comprise the primary key, you can safely use the query I provided without needing to apply DISTINCT.
Complaining's Easy; Solving Problems Takes Time
Brian Moran delivered another outstanding article in his SQL Server Magazine UPDATE commentary "Solve Problems, Not Symptoms" (May 29, 2003, InstantDoc ID 39143). I've preached on this concept for years myself. Too many people want 2-minute cures nowadays. The real measure of a DBA, in my opinion, is not his ability to recognize the problem but to come up with a solution for it. I once had an ongoing discussion with someone on the forums who was degrading SQL Server's performance and functionality as compared to Oracle. I asked, "OK, then what would you do to solve this?" I never heard back from him. Many people just want to complain; investigating possible solutions and fixing the problem requires too much thinking and legwork.
The IDENTITY Property and Partitioned Views
I read Itzik Ben-Gan's "Partitioning Tips" (April 2003, InstantDoc ID 37889) with interest, and I have a related question. In the tables Ben-Gan created, the orderid column is an integer column that doesn't use the IDENTITY property. I use the IDENTITY (1,1) property for my orderid column, so I copied the article's creation script for the Orders partitioned table and added IDENTITY (1,1) to the declaration, then tried to insert a record into the table. However, when I ran the script, I received the following error message:
Server: Msg 4433, Level 16, State 4, Line 1 Cannot INSERT into partitioned view 'Orders' because table '\[Orders2000\]' has an IDENTITY constraint.
Can I issue inserts through a partitioned view if I use the IDENTITY property?
Thanks for your question. For a complete answer, you can check out the Modification Rules section under the Partitioned Views topic in SQL Server Books Online (BOL). Unfortunately, one of the rules for performing INSERT operations through a partitioned view is that you can't have an IDENTITY property defined on any columns in the view's member tables. You can perform other activities through the view if you use an IDENTITY property, but you won't be able to issue inserts. If you still want to perform inserts through the view, you can write an INSTEAD OF INSERT trigger, as "Partitioning Tips" describes.
Archive Gem: Isolation Levels Kalen Delaney's "Transaction Isolation Levels" (June 1999, InstantDoc ID 5336) does an excellent job of clearly and completely explaining the critical concept of isolation levels when handling transactions. Thanks for a Web archive of such valuable articles.
MSDE Lacks Full-Text Indexing
Adding to Michael Otey's list of differences between Microsoft SQL Server Desktop Engine (MSDE) and SQL Server 2000 ("MSDE and SQL Server," April 2003, InstantDoc ID 38067), MSDE also lacks support for full-text indexing.
Reader Challenge Hits Spot
The solution to your January online Reader Challenge, "Dumping the Duplicates" (InstantDoc ID 23703), was exactly what I needed. Your site will be my No. 1 online reference tool from now on!
