Win32::SqlServer

(and MSSQL::DBlib and MSSQL::Sqllib)

Accessing MS SQL Server from Perl through OLE DB
(or DB-Library)

Introduction

On this page you find three different modules to accessing Microsoft SQL Server^® from Perl. The new and fresh module Win32::SqlServer (which I originally released as MSSQL::OlleDB, but that name was overly cute) uses OLE DB to connect. The two older modules MSSQL::DBlib and MSSQL::Sqllib use DB-Library. Unless you have special reasons (see discussion below), you should use Win32::SqlServer. This very much applies to existing users of MSSQL::Sqllib and MSSQL::DBlib as well! You should migrate your code to Win32::SqlServer, which retains the high-level interface of MSSQL::Sqllib. I will not make any further changes to the old modules, but they will be continue to be available here for the foreseeable future.

Win32::SqlServer is designed to be the prime alternative when you need to access SQL Server from Perl on Windows, and you have no interest to access any other RDBMS. That is, it does not run on Unix, and there is no support for DBI.

To see Win32::SqlServer in action, take at look the quick examples in the Win32::SqlServer manual.

Win32::SqlServer - Downloads and Documentation

To download this module here is the file: Win32-SqlServer-2.005.zip (787 KB). The zip file includes a binaries for x86 and AMD64 for ActivePerl builds 8xx and 10xx, as well as source code, documentation and test scripts. Note that Win32::SqlServer requires Perl 5.8.3 (ActivePerl build 809) or later.

Quick install instruction for the binary version.

Unzip.
In the top directory where you unzipped say perl activeperl-copy.pl

For more detailed instructions, including the full set of prerequisites, see the README (also included in the zip file).

The manual is included in the zip file, but you can also read it here:

Manual for Win32::SqlServer.

The manual is long, over 90 pages when printed, but there are plenty of examples in it.

You can also view the revision history.

MSSQL::Sqllib and MSSQL::DBlib - Downloads and Documentation

This is the file to download these two modules: mssql-1.009.zip (263 KB). This file includes binaries (x86 only) for ActivePerl versions 5xx (Perl 5.005), 6xx (Perl 5.6) and 8xx (Perl 5.8), as well as source code so you can build from sources if you like.

Quick installation instruction for the binary versions:

Unzip.
In the top directory where you unzipped say perl activeperl-copy.pl

For more detailed instructions see the README (also included in the zip file).

The manuals are included in the zip files, but you can also read them here:

Manuals for MSSQL::DBlib
Manuals for MSSQL::Sqllib

Note that if you are planing to use these modules with SQL 2005, beware that SQL 2005 does not ship with NTWDBLIB.DLL. You will need to find this module from an installation of an earlier version of SQL Server.

When Use Win32::SqlServer, and When Use the Other Two?

In very most cases, Win32::SqlServer should be your choice, but there are a few situations where you may have to use MSSQL::DBlib:

You are using the BCP routines. Win32::SqlServer has no bulk-copy support. (This may come in the future).
You are using the dbread/write/updatetext routines and are working with large objects. Win32::SqlServer supports the text/image/ntext data types, and you can retrieve or update large objects (LOBs) that are of any size. However, Win32::SqlServer has no special functions for them, but the LOBs passes the same needle's eye as any other data - leading to poor performance when the size goes over 1 MB. MSSQL::DBlib may be better here. Then again, DBlib can handle only text and image, not ntext.
You are have legacy code which uses not only MSSQL::Sqllib, but have many calls to MSSQL::DBlib. In this case the migration path will be longer.
You are stuck with Perl version earlier than 5.8.3.

The manual for Win32::SqlServer includes a section for migration from MSSQL::Sqllib.

Keep in mind that Microsoft has announced that the version following SQL 2008, will not accept connections from DB-Library at all!

Assorted side information

Accessing SQL Server from Unix. No, you can't run any of these modules on Unix. They rely on libraries only available on Windows. See here what options you have for talking to MS SQL Server from a Unix machine.

Alternatives on Windows. There are plentiful of alternatives to connect to SQL Server on Windows. I've tried to summarize those I know about.

Restrictions with DB-Library. If you insist on using MSSQL::Sqllib and MSSQL::DBlib, you should be aware of which restrictions you face when working with SQL 7 and later. See here for a summary of these restrictions.

DBI/DBD driver. No I don't have a DBD::SqlServer, neither do I plan to write one.

Credits

MSSQL::DBlib would not have been possible without the work of two men: Michael Peppler who wrote the original Sybperl, and Christian Mallwitz of Intershop Gmbh who took the effort to port Sybperl 2.03 to NT. And without MSSQL::DBlib as an entrace point into the XS world, I would not have been able to write Win32::SqlServer.

Contact

Any comments on these modules are welcome, please mail me at esquel@sommarskog.se.

Last updated 09-06-21 19:34