MSSQL::DBlib and SQL Server 7 and beyond

The sad story is that when Microsoft developed SQL Server 7, they decided to abandon further development of DB-Library. It's still supported and documented, but new features in SQL Server 7 and beyond that require client support are not available. SQL Server 7 and later versions regards DB-Library applications as a legacy client and switch the older version of the TDS protocol.

And the end is getting near. Microsoft announces in the list of deprecated features for SQL 2008, that the version following SQL 2008 will not support connections from DB-Library. How much I like DB-Library as an interface, I cannot but strongly recommend to move away from it.

SQL7

As the MSSQL modules are based on DB-Library, this means that these restrictions apply to DB-Library when using SQL Server 7 apply to MSSQL::Sqllib and MSSQL::DBlib as well:

If the server returns a character column longer than 255 characters, you will only get the first 255. Neither can you send strings longer than 255 characters as parameters. Same goes for binary columns.
If a column of the new datatypes nchar or nvarchar contains characters with ordinal value are > 255, those characters are replaced with a character in the 0-255.
You cannot use the new ntext datatype at all.
You cannot retrieve nullable bit columns properly.
varchar values of the empty string are returned as one blank.
The new datatype uniqueidentifier is actually retrieveable, although you get the data in a funky way. MSSQL::DBlib provides a helper procedure to reconstruct the actual value.

SQL 2000

Note that you need MSSQL::Sqllib 1.007 for full compatilbility with SQL 2000.

Officially, named instances are not supported with DB-Library and thus not with MSSQL. Nevertheless it appears to work most of the time.
You can use the new datatype bigint, but from the point of view of MSSQL::, it will be the same as a 64-bit float value. Thus you get exact bigint for a wider range than regular int values, but the entire bigint range.
Values of the new datatype sql_variant are returned as varchar(255) to DB-Library, and this how sql_sp will pass them. sql_insert will always insert values into sql_variant as character values.
You cannot use the FOR XML clause at all with DB-Library, according to Books Online.

SQL 2005

I have not made very much testing with the new features in SQL 2005, so the following is more my assumptions:

You be able to retrieve data that are of CLR user-defined types, since this appears as binary data for all non-.Net clients. However, you will not be able to pass data to parameters of CLR UDTs.
I would expect that varchar(MAX) and varbinary(MAX) appears as text and image respectively. And that nvarchar(MAX) data is not accesible at all.
I would not expect DB-Library clients to be able to access the xml data type.
You cannot use query notification.

Note also that SQL 2005 does not come with any files needed for DB-Library. This includes NTWDBLIB.DLL which has the run-time for DB-Library. You will need to find that file from an earlier version of SQL Server.

SQL 2008

I will have to admit that I have not considered if there is anything new in SQL 2008 that will work with DB-Library. Well, the new date and time data types will work if you consider them strings. But forget about table parameters and FILESTREAM. And keep in mind that this is the last version that will accept connections from DB-Library at all.

Back to the MSSQL home page.

Last updated 08-05-05 0:21