The Ashdar Partners Blog

And now, the final boot in the teeth... As I detailed in my last post, it was decided to segregate access to Visual FoxPro data by running OPENQUERY statements on a 32 bit SQL Server 2005 instance. I did all of my initial tests on my laptop, using a local SQL Server instance to access the FoxPro files stored on network file shares. Before I deployed this to a server, my initial thinking was that this wouldn't be too bad once we put support for delegation into place. Aside: For the uninitiated, delegation allows a SQL Server to use the Active Directory credentials (AKA "domain logins") of the currently-connected user to perform tasks. Essentially, you need to create an Active Directory object called a Service Principal Name or "SPN". This is not hard to set up. For me, the bigger chore was getting over the heebie-jeebies caused by the idea of having a service impersonate users. In our case, one server would be doing remote queries to another SQL Server. By putti...

This post continues my five part saga on connecting to legacy data formats. This third data source type (of four types) is Visual FoxPro . I haven't seen very much Fox since 1992, when I was a C programmer and worked with dBase, FoxPro and Clipper for a few months. I thought that everyone left FoxPro and dBase behind in the mid-1990's. Not so. Visual FoxPro was dead-ended by Microsoft a while back. Microsoft states that the ODBC drivers are depricated and that the 32 bit OLEDB drivers should be used. I can pretty reliably bring down an instance with the ODBC drivers and I am very interested in using something besides these drivers. There are 32 bit OLEDB drivers freely available from Microsoft but it looks like there won't ever be any 64 bit drivers. All of the projects which I found on Sourceforge looked too shaky to consider running "for real", and I'm not in a position to start writing or supporting open-source OLEDB drivers. No third-party has stepped...

The first hint of real trouble came when we started looking at the existing DB/2 connectivity. The second data source type (of four types) is DB/2. Currently, the application uses 32 bit ODBC drivers dating from the late 1990s. They were written by Wall Data, which was acquired by NetManage in 1999. NetManage's web site doesn't seem to acknowledge that these drivers existed or that NetManage supplies any sort of database connectivity software. One hint as to the age of the drivers: The drivers refer to AS/400, which was renamed to " iSeries " about, oh, forever ago. It turns out that there are a number of 64 bit OLEDB drivers for DB/2 available, including Microsoft's own DB/2 driver, which used to be part of it's "Host Integration Server 2004" product, but is now freely downloadable . IBM's got drivers too. My initial problem wasn't finding drivers, it was getting the linked server to talk to the DB2 instance. No one involved understood...

To continue from the last posting, a project that I am associated with needs to deal with four non- SQL Server data sources. I'll start with the easy one first... The first data source type is text files. We can get the data in these files into SQL Server with BULK INSERT (which seems deprecated in SQL Server 2005, but seems to still be in SQL Server 2008) or OPENQUERY using the bulk option. This is all native to SQL Server, so I'm not counting this as a major issue. Exporting to text is a problem, but there are only a few very simple examples of that in the codebase . We are trying to avoid use of xp _ cmdshell for all of the usual reasons. For most of our issues, we can probably use SSIS or even a custom vbscript in a job. The worst-case scenario is a small CLR procedure. (Why-o-why doesn't SQL Server have a BULK EXPORT?) I'd prefer to keep these OPENQUERY statements in place because they are mostly in views and procedures stored in user databases. It's...

One of my projects involves moving some very old applications running against a 32 bit SQL Server 2000 instance to a 64 bit SQL Server 2005 instance. When I the project was initiated, I thought my largest problems would be: Differences in behavior between 2000 and 2005 Porting a couple of hundred DTS packages to SSIS Wrong. It turns out that my biggest problem is a lack of 64 bit OLEDB drivers. I have had reason to spend a good deal of time researching the availability of 64 bit solutions for certain legacy data formats. This is a summary of that research and I hope that it saves someone some pain. Anyone could do what I have done here by spending time searching with google. I don't know anything unique, nor do I have any insider knowledge. Still, I was surprised by the situation on the ground. An early version of this originally appeared on Swynk email lists , specifically the sql 2k5 list. It's been edited to tighten up some of my informal writing style. I have also ...

Two quick ones for this post, before the US 4 th of July holiday. Both functions have one thing in common: use of environment variables. In order to see the environment variables that are available, just type: dir env : (This listing could be extensive. ( dir env :).Count returns 51 on my laptop.) Environment variables aren't as popular as they used to be, with settings located in registry entries, . ini files and . xml files. For a few things, it's still easier to find their values through an environment variable than to figure out where they hide in the registry or google for the proper .net calls. When I set up a new machine, I add a path to the environment that points to my local copy of my scripts. This can be very handy since I still have some . vbs and . wsf scripts lurking around. whoami simply displays the name of the currently logged in user. It's very simple, but it is also a good, simple example of how to use environment variables in a script. Here i...