ASP returning null sized field from MS SQL database

We currently have an SQL db running on a web server.
One of these fields is a large(ish) amount of text data up to 400
characters and has been cast variously as varchar, nchar and text
type to overcome a problem. The problem appears to be in retrieving
the data via ASP. I understand that ASP can handle string data of this
size so I am okay there.
When the records are retrieved from the db, the data string length =0.
I know the data is there because I have written a Delphi data manager
which interrogates the db and shows all records and their contents.
So if ASP can handle strings this size and the data is there, why do I
get a data length of zero bytes returned when I interrogate the record
set?
Whichever way I cast this field I get the same result.

I know the code is sound as it works locally through a MS SQL server
on my PS.
Anyone have this problem or know what's causing it? I have logged a
support call with my hosting company, but they haven't replied as yet
and I am stuck on an urgent project.

Any suggestions?

Cheers
GrantGrant Davis (grant@.gbdavis.com) writes:
> We currently have an SQL db running on a web server.
> One of these fields is a large(ish) amount of text data up to 400
> characters and has been cast variously as varchar, nchar and text
> type to overcome a problem. The problem appears to be in retrieving
> the data via ASP. I understand that ASP can handle string data of this
> size so I am okay there.
> When the records are retrieved from the db, the data string length =0.
> I know the data is there because I have written a Delphi data manager
> which interrogates the db and shows all records and their contents.
> So if ASP can handle strings this size and the data is there, why do I
> get a data length of zero bytes returned when I interrogate the record
> set?
> Whichever way I cast this field I get the same result.
> I know the code is sound as it works locally through a MS SQL server
> on my PS.
> Anyone have this problem or know what's causing it? I have logged a
> support call with my hosting company, but they haven't replied as yet
> and I am stuck on an urgent project.
> Any suggestions?

Yes one: Post the relevant pieces of your code. Today it is a beautiful
day here in Stockholm, the sun is shining from blue skies. Nevertheless,
I am completely in the dark about what you are doing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Arithmetic Overflow Error

I'm getting an overflow error (see ERROR below) when trying to calculate
myPercent (see MY CODE below). I've tried using CAST (see MY CODE below) but
can't get a successful execution. I posted my DDL (see DDL below).
Can someone please help me get over this overflow? I've lost a day trying to
solve this data type problem. I'm sure the proper CAST or CONVERT will solve
the issue, but I'm out of luck and out-of-work until this gets fixed.
Any help would be GREATLY appreciated.
****************
ERROR: Arithmetic overflow error converting numeric to data type numeric.
****************
MY CODE:
SELECT pDate, SUM(pGross) AS SumpGross, SUM(pLoss) AS SumpLoss,
CAST(SUM(CONVERT(decimal(10, 6), p1grade)) /
SUM(CONVERT(decimal(10, 6), pgross - pLoss)) AS DECIMAL(4, 3)) AS myPercent
FROM pPercent
GROUP BY pDate
****************
DDL:
CREATE TABLE [pPercent] (
[pID] [int] IDENTITY (1, 1) NOT NULL ,
[pDate] [datetime] NULL ,
[pShift] [int] NULL ,
[pGross] [int] NULL ,
[pLoss] [int] NULL ,
[p1Grade] [int] NULL
) ON [PRIMARY]
GO
insert into pPercent (pDate, pShift, pGross, pLoss, p1Grade) values
('20051121', '1', '241711', '18525', '24989')
insert into pPercent (pDate, pShift, pGross, pLoss, p1Grade) values
('20051121', '2', '172161', '8268', '22690')
insert into pPercent (pDate, pShift, pGross, pLoss, p1Grade) values
('20051120', '1', '210979', '11487', '21737')decimal(10,6) means 10 digits total, 6 of which are to the right of the
decimal.
e.g., 1234.567890
which only leaves 4 to the left. most of your int values are larger than
this.
change the decimal definition to something large enough - I tend to go
with 10 to the left of the decimal - so decimal(16,6)
Scott wrote:
> I'm getting an overflow error (see ERROR below) when trying to calculate
> myPercent (see MY CODE below). I've tried using CAST (see MY CODE below) b
ut
> can't get a successful execution. I posted my DDL (see DDL below).
> Can someone please help me get over this overflow? I've lost a day trying
to
> solve this data type problem. I'm sure the proper CAST or CONVERT will sol
ve
> the issue, but I'm out of luck and out-of-work until this gets fixed.
> Any help would be GREATLY appreciated.
>
> ****************
> ERROR: Arithmetic overflow error converting numeric to data type numeric.
> ****************
> MY CODE:
> SELECT pDate, SUM(pGross) AS SumpGross, SUM(pLoss) AS SumpLoss,
> CAST(SUM(CONVERT(decimal(10, 6), p1grade)) /
> SUM(CONVERT(decimal(10, 6), pgross - pLoss)) AS DECIMAL(4, 3)) AS myPercen
t
> FROM pPercent
> GROUP BY pDate
> ****************
> DDL:
> CREATE TABLE [pPercent] (
> [pID] [int] IDENTITY (1, 1) NOT NULL ,
> [pDate] [datetime] NULL ,
> [pShift] [int] NULL ,
> [pGross] [int] NULL ,
> [pLoss] [int] NULL ,
> [p1Grade] [int] NULL
> ) ON [PRIMARY]
> GO
>
> insert into pPercent (pDate, pShift, pGross, pLoss, p1Grade) values
> ('20051121', '1', '241711', '18525', '24989')
> insert into pPercent (pDate, pShift, pGross, pLoss, p1Grade) values
> ('20051121', '2', '172161', '8268', '22690')
> insert into pPercent (pDate, pShift, pGross, pLoss, p1Grade) values
> ('20051120', '1', '210979', '11487', '21737')
>
>

Arithmetic Overflow

I'm getting an "Arithmetic Overflow" error in the "LAST STEP" section in the
DDL below. It's caused when I try to "CAST" a decimal and varchar field into
decimal type.
The reason behind these strange conversions is that within the production
environment, the data that is being imported into the #tmpTempTable is
coming from a legacy FoxPro database that was poorly designed.
If someone can just help me make the "LAST STEP" section work and "CAST" the
fields without error, I will be able to build an access front-end for this
data.
DDL **********
IF object_id('tempdb..#tmpTempTable') IS NOT NULL
BEGIN
DROP TABLE #tmpTempTable
END
CREATE TABLE #tmpTempTable (
tll_grpfk char(5) NOT NULL,
tll_meanpeak decimal(5, 1) NULL,
tll_i_meanpeak varchar(15) NULL,
tll_npieces int NULL
)
GO
insert into #tmpTempTable (tll_grpfk, tll_meanpeak, tll_i_meanpeak,
tll_npieces) values
('00001', '26.2', '1188855', '45435')
insert into #tmpTempTable (tll_grpfk, tll_meanpeak, tll_i_meanpeak,
tll_npieces) values
('00002', '15.5', '9703464', '625518')
IF object_id('tblData') IS NOT NULL
BEGIN
DROP TABLE tblData
END
CREATE TABLE tblData (
tll_grpfk int NOT NULL,
tll_meanpeak decimal(5, 1) NULL,
tll_i_meanpeak decimal(8, 1) NULL,
tll_npieces int NULL
)
GO
-- LAST STEP
insert into tblData (tll_grpfk, tll_meanpeak, tll_i_meanpeak, tll_npieces)
select CAST(tll_grpfk AS INT), CAST(tll_meanpeak as decimal(5, 1)),
CAST(tll_i_meanpeak as decimal(5, 1)), tll_npieces
from #tmpTempTableYou can't cast 1188855 or 9703464 as decimal(8,1).
Looks like you typed a 5 where you meant to type 8
in CAST(tll_i_meanpeak as decimal(5, 1))
Steve Kass
Drew University
scott wrote:

>I'm getting an "Arithmetic Overflow" error in the "LAST STEP" section in th
e
>DDL below. It's caused when I try to "CAST" a decimal and varchar field int
o
>decimal type.
>The reason behind these strange conversions is that within the production
>environment, the data that is being imported into the #tmpTempTable is
>coming from a legacy FoxPro database that was poorly designed.
>If someone can just help me make the "LAST STEP" section work and "CAST" th
e
>fields without error, I will be able to build an access front-end for this
>data.
>
>DDL **********
>IF object_id('tempdb..#tmpTempTable') IS NOT NULL
>BEGIN
> DROP TABLE #tmpTempTable
>END
>CREATE TABLE #tmpTempTable (
> tll_grpfk char(5) NOT NULL,
> tll_meanpeak decimal(5, 1) NULL,
> tll_i_meanpeak varchar(15) NULL,
> tll_npieces int NULL
> )
>GO
>insert into #tmpTempTable (tll_grpfk, tll_meanpeak, tll_i_meanpeak,
>tll_npieces) values
> ('00001', '26.2', '1188855', '45435')
>insert into #tmpTempTable (tll_grpfk, tll_meanpeak, tll_i_meanpeak,
>tll_npieces) values
> ('00002', '15.5', '9703464', '625518')
>
>IF object_id('tblData') IS NOT NULL
>BEGIN
> DROP TABLE tblData
>END
>CREATE TABLE tblData (
> tll_grpfk int NOT NULL,
> tll_meanpeak decimal(5, 1) NULL,
> tll_i_meanpeak decimal(8, 1) NULL,
> tll_npieces int NULL
> )
>GO
>-- LAST STEP
>insert into tblData (tll_grpfk, tll_meanpeak, tll_i_meanpeak, tll_npieces)
>select CAST(tll_grpfk AS INT), CAST(tll_meanpeak as decimal(5, 1)),
> CAST(tll_i_meanpeak as decimal(5, 1)), tll_npieces
>from #tmpTempTable
>
>
>|||what can i cast or convert it to in order to be decimal type?
"Steve Kass" <skass@.drew.edu> wrote in message
news:e5kMajCZGHA.1220@.TK2MSFTNGP02.phx.gbl...
> You can't cast 1188855 or 9703464 as decimal(8,1).
> Looks like you typed a 5 where you meant to type 8
> in CAST(tll_i_meanpeak as decimal(5, 1))
> Steve Kass
> Drew University
> scott wrote:
>