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.
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.
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.
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
- 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.
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