This last week I have had my head buried in a project to merge several databases into a few common ones. One of my clients is finding that a lot of smaller companies are combining their presence for various reasons and with it comes the need to merge what were previously very separate installations. Blah, blah, that is the boring stuff. What it means is that I have to do a bunch of things to make it work. These programs have a long history and as you can imagine with the “evolving” nature of vertical market software they also have a lot of baggage in their implementation. From what I have seen this is just a fact of life. It sure does make for some interesting jobs though!

If I get a chance I might try to blog about some of the other techniques I learnt while working on this project. Lots of SQL manipulations that are pretty cool, lots of mistakes and back tracks and lots of things I just wish there was a tool that could do the job for me. First though, this is something that I hacked together using powershell to rename a directory full of word documents that had a unique identifier embedded in the name.

The Situation

Two databases were being combined, Client records in database A would remain as they were. All Clients from Database B were copied into database A and received a newly generated SysID (Identity column) when they were inserted. The original SysID was stored in a column of the Clients table (ReferralSysID) for later reference. It could have been a temp table but this old ID was going to be needed for other things so it was decided to put it in the table itself.

This is a topic that comes up on the newsgroups from time to time. A while back, more than 6 years back IIRC, I spent some time researching and testing the different scenarios for a project I was involved in. I will attempt to summarise the finding here with a few additional comments picked up in NG threads along the way.

Update 21 Sept 2012: I have just tested with Clarion 8.0.9304 and it looks like it uses the new Native Client just fine. This is what is shown in the trace log:

Some terms:

Clarion MSSQL Accelerator driver – C60MSS.DLL or more recently, ClaMSS.dll  \

ODBC SQL Server Driver – This is what Trace.exe reports when you have no SQL Native Client installed. There is likely a more technical explanation for the name but it doesn’t really matter. This is what Clarion will do when there is not other choice. The default…

SQL Native Client – “…contains the SQL Server ODBC driver and the SQL Server OLE DB provider in one native dynamic link library (DLL) supporting applications using native-code APIs (ODBC, OLE DB and ADO) to Microsoft SQL Server.” – source

I have not personally taken any serious benchmarks of using the ODBC driver or the Native client. My general impression is that it seems you get a faster experience with the Native Client and others have reported significant differences, especially when connecting to a remote server. Also, if you want your application to use MARS support introduced in Clarion6 (6.3 I think) then you must have the Native Client installed or you will likely see BUSYHANDLING errors very soon.

Getting it right

For an application compiled with Clarion6 you must