BUG: SQL Server Error "Specified Column Precision 38 Is Greater Than the Maximum Precision of 28. (2750)" When Running Parameterized Query (239725)

The information in this article applies to:

Microsoft SQL Server 7.0
Microsoft ODBC Driver for SQL Server 3.7
Microsoft Data Access Components 2.1
Microsoft Data Access Components 2.5
Microsoft Data Access Components 2.6

This article was previously published under Q239725

BUG #: 55723 (SQLBUG_70)

SYMPTOMS

When you run a parameterized query, with a parameter of type decimal and a precision of 38, against a SQL Server 7.0 database using the 3.70 driver, you may receive the following error message:

Server: Msg 2750, Level 16, State 1, Procedure #odbc#efwadm19151b, Line 0 Column or parameter #2: Specified column precision 38 is greater than the maximum precision of 28. Parameter '@P2' has an invalid data type.

CAUSE

The SQL ODBC driver uses a default value of 38 for the precision value if the application specifies SQL_DEFAULT for the precision when binding a decimal parameter. However, the default decimal precision on the SQL Server is 28.

WORKAROUND

To work around this problem, start SQL Server with a -p38 parameter to set the default server precision to 38.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.

MORE INFORMATION

Steps to Reproduce Problem

Cut and paste the following code into SQL Query Analyzer:

  CREATE TABLE [dbo].[abcd] (
	[a] [int] NULL ,
	[b] [datetime] NULL ,
	[c] [decimal](18, 5) NULL ,
	[d] [int] NULL 
) ON [PRIMARY]
GO

Attempt to execute the following:

create proc #odbc#efwadm19151b(@P1 int,@P2 decimal(38,5),@P3 int) as  INSERT INTO abcd( a, b, c, d) VALUES (@P1, getdate() ,@P2,@P3)

Modification Type:	Major	Last Reviewed:	12/5/2003
Keywords:	kbBug kbpending KB239725