VMS and MSSQL

[MJ26]

Does anyone know if you can connect OpenVMS to a Microsoft SQL server? (Don't Laugh =) ) If it can be done, what updates will need to be installed, and also what versions will it run with? Thanks!

[Hoff]

Can you connect into Microsoft SQL Server box from a remote host? Sure. What sort of software is used to do that? Well, that's usually a widget cryptically called an ODBC client or a JDBC client for most stuff and for Java stuff, respectively. And there are various ODBC and JDBC client pieces around, and for various platforms. Some OpenVMS databases include these clients, and there are some open source options around as well. UnixODBC has the pieces necessary for OpenVMS use integrated into the source pool, for instance.

Some details and options:

http://labs.hoffmanlabs.com/node/668

[Craig A Berry · Accepted solution]

I'm not laughing. I do it every day and have various important processes that depend on it. If you are going to need a lot of help, you'll likely want a commercial vendor (such as Attunity, Easysoft, or CONNX). What I use is FreeTDS (http://www.freetds.org). FreeTDS is a library that speaks the wire-level protocol used by Microsoft and Sybase database servers, a protocol known as Tabular Data Stream (TDS). There are various APIs in FreeTDS, including an ODBC driver. There are some rudimetary utilities for connecting, running queries, and bulking data in and out. It will work with any database version and most versions of VMS from the last decade or so, with a few limitations and exceptions depending on the specifics.

[John Harney]

Thanks to the original post, Hoff's site, and Craig's willingness to share what he knows, I'm not crying either!!

Craig's post (and his notes in the FreeTDS bits) only reference Alpha (and VAX as a possibility) but there was no mention of I64. Was that a warning sign?

Nope! I fetched the kit on a PC, un-gzipped it with WinZip, copied the unzipped tar file to VMS, used vmstar (with the /ODS2 switch as they say!) to unpack the sources, ran the configure, and built it on my I64 v8.3 system.

$ vmstar /ods2 freetds-stable.tar
$ @[.vms]configure
$ mms /macro=("MSDBLIB"=1,"TDSVER"="TDS80")

I did bump into a compile quirk, but it was documented in the source, and the required change was very simple; as well, Craig reviewed the latest sources (not yet "stable") and notes this has been fixed.

The samples are quite complete, and some are even easy to read. (Look at the BSQLDB.C file, for example...)

Now instead of doing all sorts of hokey message transfer and relaying to get the data from our VMS box into an MS-SQL database, we just... log in, connect up, and do the insert directly. A think of beauty.

Kudos to Craig and Hoff, and everybody else who documents their struggles and successes.

Much obliged!
\john

[Craig A Berry]

John said:

>Craig's post (and his notes in the FreeTDS bits) only reference Alpha
>(and VAX as a possibility) but there
>was no mention of I64. Was that a warning sign?

It's just a side effect of the fact that when I wrote those notes five years ago, Itanium was (for me) still just a rumor on the horizon. As of a month ago, I'm using FreeTDS on Itanium in production. Thanks for the success report. Looks like I need to update those notes.

[EdgarZamora_1]

We use FreeTDS (on Alpha) here too. Works like a charm. Thanks Craig!