SQL Server Error from only Crystal Reports

[DavidCoulter]

I have a stored procedure that works in query analyser and when run from a pass thru query in Access using the same ODBC Connection as I use in Crystal Reports but it does not work when run from a crystal report.

The error message I get is:

ODBC Error: [Microsoft][ODBC SQL Server Driver] Numeric value out of range

Any help would be appreciated

Thanks
David Coulter

CREATE PROCEDURE sp_GetUnitsOrAllocationsNotMatch AS
SELECT TC_PROJECT.PROJ_ID, TC_PROJECT.STATUS,AVG(TC_PROJECT.NO_UNITS) AS PROJ_NO_UNITS, AVG(TC_PROJECT.LI_UNITS) AS PROJ_LI_UNITS,
Round(AVG(TC_PROJECT.TOT_ALLOCA),0) AS PROJ_TOT_ALLOC, SUM(TC_BUILDING.TOT_UNITS) AS BLD_TOT_UNITS, SUM(TC_BUILDING.LI_UNITS)
AS BLD_LI_UNITS, ROUND(SUM(TC_BUILDING.ALLAMT),0) AS BLD_ALLAMT
FROM dbo.TC_PROJECT TC_PROJECT INNER JOIN
(SELECT TC_BUILDING.[PROJ_ID], TC_BUILDING.BIN, MAX(TC_BUILDING.[TOT_UNITS]) AS TOT_UNITS, MAX(TC_BUILDING.[LI_UNITS])
AS LI_UNITS, MAX(TC_BUILDING.[ALLAMT]) AS ALLAMT
FROM [dbo].[TC_BUILDING]
GROUP BY TC_BUILDING.[PROJ_ID], TC_BUILDING.[BIN]) TC_BUILDING ON TC_PROJECT.PROJ_ID = TC_BUILDING.PROJ_ID
WHERE TC_Project.Status = 'AW' Or TC_Project.Status = 'TP'
GROUP BY TC_PROJECT.STATUS,TC_PROJECT.PROJ_ID
HAVING (TC_PROJECT.PROJ_ID > 'TC0001') AND (SUM(TC_BUILDING.TOT_UNITS) <> AVG(TC_PROJECT.NO_UNITS) OR
SUM(TC_BUILDING.LI_UNITS) <> AVG(TC_PROJECT.LI_UNITS) OR
ROUND(SUM(TC_BUILDING.ALLAMT),0) <> ROUND(AVG(TC_PROJECT.TOT_ALLOCA),0))
ORDER BY TC_PROJECT.STATUS,TC_PROJECT.PROJ_ID


TABLE STRUCTURES

CREATE TABLE [dbo].[TC_BUILDING] (
[PROJ_ID] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BIN] [int] NULL ,
[DEV_NO] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREDIT_T] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TOT_UNITS] [int] NULL ,
[LI_UNITS] [int] NULL ,
[AFR_ESTP] [decimal](18, 6) NULL ,
[CENSUST] [decimal](18, 3) NULL ,
[QCOST] [money] NULL ,
[CREDIT_REQ] [money] NULL ,
[ALLAMT] [money] NULL ,
[CREDIT_RES] [money] NULL ,
[PLACEDIS] [datetime] NULL ,
[RESERVAD] [datetime] NULL ,
[F8609PRD] [datetime] NULL ,
[LAST_TIME] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LSTUPDATE] [datetime] NULL ,
[USER_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SQFT] [int] NULL ,
[YEAR] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TC_PROJECT] (
[APPL_NO] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROJ_ID] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HTF_NO] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[APPLDATE] [datetime] NULL ,
[EVALNO] [int] NULL ,
[PAYEE] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USER_ID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LSTUPDATE] [datetime] NULL ,
[LAST_TIME] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATUS] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RUN_8609] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ALL_YEAR] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDR1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDR2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COUNTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[P_CENSUS] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPONSOR] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEV_NO] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRIORITY] [int] NULL ,
[PLACEDIS] [datetime] NULL ,
[OWNERTYP] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROJTYPE] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BLDCRED] [varchar] (23) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NO_UNITS] [int] NULL ,
[LI_UNITS] [int] NULL ,
[NO_BLDGS] [int] NULL ,
[TARG_PER] [int] NULL ,
[M_PERIOD] [int] NULL ,
[EXT_AGRE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEV_COST] [money] NULL ,
[QCOST] [money] NULL ,
[AFR_PERC] [decimal](18, 5) NULL ,
[P_LANDCT] [money] NULL ,
[FED_SUBS] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TENT_ASST] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROJ_ASST] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNIT_SUBS] [int] NULL ,
[ESTAPPFEE] [money] NULL ,
[NET_APPFPD] [money] NULL ,
[APPFEEREFD] [money] NULL ,
[NETREFAMT] [money] NULL ,
[CRRYFWDAGR] [datetime] NULL ,
[TOT_ALLOCA] [money] NULL ,
[SITEA] [decimal](18, 2) NULL ,
[SITESF] [int] NULL ,
[PESTDATE] [datetime] NULL ,
[PERUNITEXP] [money] NULL ,
[EQUIP_LI] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EQUIP_MKT] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTLI_WRENT] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTMKTWRENT] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HEAT] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOT_WATER] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AC] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MANAGEMNT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMMENTS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NETMONFEE] [money] NULL ,
[CENSUSTRAC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RESERVAD] [datetime] NULL ,
[HUDTABLE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRRYFWDPRD] [datetime] NULL ,
[SUPPLEMENT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTIFIED] [datetime] NULL ,
[NETTRNAGY] [money] NULL ,
[ED_RES] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AFR_PERC4] [decimal](18, 4) NULL ,
[QCOST4] [money] NULL ,
[QCOST9] [money] NULL ,
[OWNDATE] [datetime] NULL ,
[CRRYSIGN] [datetime] NULL ,
[ELECTION] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MNOFFICER] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TEPROJ] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Update] [datetime] NULL
) ON [PRIMARY]

 

[synapsevampire]

Try doing a verify database (Database->Verify Database).

-k

 

[DavidCoulter]

Thanks, That worked. I had another report that was asking for login info over and over and over.... I did a verify on that one and it asked me for the DSN. I guess it somehow forgot its DSN.

Thanks again
David Coulter