SELECT * FROM [Musings]

Database matters.

September 2006

In SQL Server 2005, MS is changing the way it supports ODBC and OLE DB clients.

Here is a link to an FAQ on the topic. This appears to be a single DLL replacement for MDAC. MDAC is a very big download that steps on all manner of other files when installed. A single DLL option seems like a better alternative, though I would not likely re-deploy a current application to replace use of MDAC.

Posted by Douglas Reilly at 04:03 PM  Permalink |

One of the tasks that is often overlooked because it is so difficult to do correctly is database documentation.

Database Dictionary Creator (here) is a tool that makes it easier to do than using the standard Microsoft tools. You can add additional properties to track, giving you greater flexibility than the standard documentation tools...

Posted by Douglas Reilly at 03:30 PM  Permalink |

Yikes! Access 2007 will, in about the ugliest way possible, allow multivalued fields to be created in the user interface, as described here.

Deep down, the multivalued fields are actually internally handled in a relatively clean way, however there is not a way for even a developer to actually get to the proper representation in this version. Maybe next time.

This is the fundamental flaw I have often seen in MS Access is how Access lets you get to 80% of the solution, in no time, while the remaining 20% of the solution, so that the application actually is properly constructed, is almost impossible to reach.

Posted by Douglas Reilly at 11:37 AM  Permalink |

Aviran's Place has a blog entry covering the top five causes of data compromise. These are based upon the Chamber of Commerce's article, PDF file here.

One of the scariest causes discussed is using POS or other software with password or other defaults in place. I wondered how prominent this was, but have recently come across a number of ISV supplied systems where the default password was not only encouraged, but required if you wanted to use remote support, as well as folks using a blank sa password on SQL Server, even in the case where using a blank password was made difficult by the installer.

Are you distributing a system that makes use of a default or easily defeated password mandatory? What can you do to make sure that the system is changed?

Posted by Douglas Reilly at 11:19 AM  Permalink |

Peter W. Debetta blogs about what can happen when mixing T-SQL with a case insensitive coallation sequence vs. code and data created using a CLR language such as T-SQL.

This is an interesting issue. The idea of anything in T-SQL being case sensitive is not anything I had thought about, but of course when calling a C# object, it makes complete sense that it is case sensitive.

I have not tested, but I wonder if in a case insensitive language such as VB.NET, whether you have to enter the property/method name with the same exact casing as in the VB.NET code?

Posted by Douglas Reilly at 05:21 PM  Permalink |

I first met Mike Gunderloy way back when, when we were both doing a fair amount of MS Access programming. At the time, it was a reasonable option for many smaller systems. We were both in a group that was then known as the Access Insiders. I still have a somewhat funky looking Access Insiders vest from those days.

Here Mike talks about the time when he was hired by a wealthy person to "fix" MS Access, including things like changing the table designer so that it would know that CustomerID was ment as an identity field (presumably by seeing the "ID" right after the table name).

Posted by Douglas Reilly at 05:37 PM  Permalink |

In addition to this blog, I do some writing for the Simple-Talk.com Web site. One of the things I do there is a series of articles entitled "Database Geek of the Week."

The latest Database Geek of the Week article is here. I interview Kirk Haselden, who is a development manager for SQL Server Integration Services (SSIS) at Microsoft.

After I did this interview, I actually got a copy of Kirk's book. SSIS is the replacement for DTS. Unlike some other changed features in SQL Server 2005, SSIS is a pretty great departure from DTS. Kirk indicates in the book that there is very little DTS code in SSIS. I can believe that, and having Kirk's book has helped me dig into SSIS a little more than I have previously. Things in SSIS that exist in DTS often have different (and generally better) names than they had in DTS.

Posted by Douglas Reilly at 11:31 PM  Permalink |

One of the major issues when working within T-SQL is the lack of support for arrays. Enter XML, as this article discusses.

XML is one area inside SQL Server that I have not worked with to any great extent, however I do believe that it is a very powerful tool.

My alternative has been to pass in a comma delimited string and then use a user defined function to split that delimited string and place the results in a temporary table. The XML route seems to be a cleaner solution.

Posted by Douglas Reilly at 05:06 PM  Permalink |