A. This is probably because the datatypes returned by sp_datatype_info to the application are not understood. Hopefully Microsoft will provide an official fix with SP2 for SQL 7, but for now here is a modification to the stored-procedure concerned. Note that changing Microsoft supplied stored-procs is not recommended or supported and if something goes wrong with the new SP you are completely on your own. It is supplied as is, with no warranty!
It is based on the SQL 7 SP1 version, so apply SP1 before making this change.
Look for ** to see where the changes are
--
-- Script to update Microsoft supplied version of sp_datatype_info to work properly with
-- ODBC v2 applications and to fix a gpf it causes in vbdb300.dll
--
-- Note the SP modified here is the one from SP1 for SQL 7.
-- Changes made are :-
--
-- 1. Do not return any types below -7. These are nchar, ntext etc. that are SQL 7 only
-- and not understood by ODBC2 applications
--
-- 2. For varchar/char do not return 8000 as the length, return 255
--
--
USE master
go
DROP proc sp_datatype_info
go
SET QUOTED_IDENTIFIER ON
go
--
-- Object will be created with MSShipped flag
--
EXEC sp_MS_upd_sysobj_category 1
go
/* Procedure for 7.0 server */
CREATE proc sp_datatype_info
(@data_type int = 0, @ODBCVer tinyint = 2)
as
declare @mintype int
declare @maxtype int
if @ODBCVer <> 3
select @ODBCVer = 2
if @data_type = 0
begin
select @mintype = -32768
select @maxtype = 32767
-- ** Change started **
-- For ODBC version 2 apps don't let them see new types
if @ODBCVer = 2
begin
select @mintype = -7
end
-- ** Change ended **
end
else
begin
select @mintype = @data_type
select @maxtype = @data_type
end
select
convert(sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME
end) TYPE_NAME,
d.DATA_TYPE,
convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
when type_name(d.ss_dtype) IN ('numeric','decimal') and
t.xusertype <= 255 then @@max_precision /* DECIMAL/NUMERIC */
-- ** Change Started **
-- Reduce max length returned for char/varchar for ODBC 2 apps
when @ODBCVer = 2 AND OdbcPrec(t.xtype, t.length, t.xprec) = 8000 then 255
-- ** Change ended **
else OdbcPrec(t.xtype, t.length, t.xprec)
end) "PRECISION",
d.LITERAL_PREFIX,
d.LITERAL_SUFFIX,
e.CREATE_PARAMS,
convert(smallint,case
when d.AUTO_INCREMENT = 1 then 0 /* IDENTITY*/
else TypeProperty (t.name, 'AllowsNull')
end) NULLABLE,
d.CASE_SENSITIVE,
d.SEARCHABLE,
d.UNSIGNED_ATTRIBUTE,
d.MONEY,
d.AUTO_INCREMENT,
convert(sysname,case
when t.xusertype > 255 then t.name
else d.LOCAL_TYPE_NAME
end) LOCAL_TYPE_NAME,
convert(smallint,case
when type_name(d.ss_dtype) IN ('numeric','decimal') and t.xusertype > 255 then TypeProperty (t.name, 'Scale')
else d.numeric_scale
end) MINIMUM_SCALE,
convert(smallint,case
when type_name(d.ss_dtype) IN ('numeric','decimal') and d.AUTO_INCREMENT = 0 and t.xusertype <= 255 then @@max_precision /* DECIMAL/NUMERIC */
when type_name(d.ss_dtype) IN ('numeric','decimal') and d.AUTO_INCREMENT = 1 then 0 /* DECIMAL/NUMERIC IDENTITY*/
else TypeProperty (t.name, 'Scale')
end) MAXIMUM_SCALE,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
NUM_PREC_RADIX = convert(int,d.RADIX),
INTERVAL_PRECISION = convert(smallint,NULL),
USERTYPE = t.usertype
from master.dbo.spt_datatype_info d
INNER JOIN systypes t on d.ss_dtype = t.xtype
LEFT OUTER JOIN master.dbo.spt_datatype_info_ext e on
t.xusertype = e.user_type
and isnull(d.AUTO_INCREMENT,0) = e.AUTO_INCREMENT
where
d.DATA_TYPE between @mintype and @maxtype
and (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
and (t.xusertype <= 255 or
isnull(d.AUTO_INCREMENT,0) = 0)
order by 2, 12, 11,
case
when t.usertype=18 then 255
else t.usertype
end
go
GRANT EXEC ON sp_datatype_info TO public
go
--
-- Turn off MSShipped flag for any other SP's (not that we're creating any)
--
EXEC sp_MS_upd_sysobj_category 2
go
--
-- Allow updates and hack sysobjects directly. Make sure sp is marked as with
-- quoted identifiers and MSShipped
--
exec sp_configure 'allow updates', 1
reconfigure with override
go
update
sysobjects
set
status = 0x80000009
where
name = 'sp_datatype_info'
go
exec sp_configure 'allow updates', 0
reconfigure with override
go
