August 27, 2010 at 9:22 am
I hope this is in the right section...........
This is for build 9.0.4053.
I tried to put the following value into a column with a MONEY data type: 999996.
I got the proverbial "Arithmetic Overflow Error..............trying to convert numeric value to character".
I can put any number into this field EXCEPT those between 999990 and 999999. I am not trying to convert or do anything else.
Uh..... what gives?
I've tried to put it into another column in the same table with the same data type and the same results.
I've tried to put it into another column of the same table with the same data type on a different server (built the same day with same specs as the original server) and it works.
There is only 1 record of the 7,800 that are getting updated that has a value between these 2 numbers. Other values greater and smaller in other records don't have this issue.
I'm baffled, to say the least.
Here's my update statement:
UPDATE MBL
SET MBL.Total_Loan_Amount = MBLD.Total_Loan_Amount
FROM dbo.MBL_Daily MBLD INNER JOIN dbo.Master_Bidder_Listing MBL ON MBL.Cert = MBLD.Cert
WHERE MBL.Total_Loan_Amount <> MBLD.Total_Loan_Amount
August 27, 2010 at 9:28 am
Let's see the DDL for the table, including any triggers that might be firing. The error message says you're trying to convert a numeric to a character. That seems an odd message if the target data type is MONEY.
August 27, 2010 at 9:38 am
I just ran this on a server running 9.0.4053, and it works just fine. Sure sounds like a trigger to me.
if object_id('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
CREATE TABLE #temp (Col1 MONEY);
declare @start int;
set @start = 999979;
WITH CTE (N) AS
(
SELECT TOP (30) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.objects
)
insert into #temp (col1)
select @Start + N
FROM CTE;
select * from #temp;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 27, 2010 at 9:47 am
Here's the abbreviated DDL for the tables:
1.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Master_Bidder_Listing](
[Cert] [int] NOT NULL,
[Total_Deposit_Amount] [money] NULL,
[Total_Loan_Amount] [money] NULL,
[Consolidated_Asset_Amount] [money] NULL,
[Total_Asset_Amount] [money] NULL,
CONSTRAINT [Master_Bidder_Listing$Cert] PRIMARY KEY CLUSTERED
(
[Cert] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]
2.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MBL_Daily](
[Cert] [int] NOT NULL,
[Total_Deposit_Amount] [money] NULL,
[Total_Loan_Amount] [money] NULL,
[Consolidated_Asset_Amount] [money] NULL,
[Total_Asset_Amount] [money] NULL,
CONSTRAINT [MBL_Daily$Cert] PRIMARY KEY CLUSTERED
(
[Cert] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
*Only an audit trigger that writes a record to an audit table for Update, Delete, Insert that writes to a field with VARCHAR(1000).
**Only on one specific record does the error occur, it is only record with this value. There are numbers greater and smaller that update in this field without an issue.
August 27, 2010 at 9:50 am
I agree the issue is going to be the trigger....show us your audit trigger code; if both fields are money or decimal types, the error raised cannot be from the update statement itself, it's gotta be the trigger.
Lowell
August 27, 2010 at 10:00 am
Update:
1. I disabled the trigger then ran the UPDATE statement, same error.
2. I also ran a SELECT CONVERT(VARCHAR(1000), Total_Loan_Amount) FROM Master_Bidder_Listing for that record and didn't get an error.
3. I can run a manual update for just that record for 999949 and there isn't an issue. (with trigger on).
August 27, 2010 at 10:36 am
The error message only makes sense if some process is trying to put a numeric data type into a character typed column. The raw update doesn't do that. If you still get the error message after the audit trigger is turned off, then there has to be something we're not seeing that is attempting that conversion. Or there is a bad value in the source table, but you succeeded in converting that to a varchar(1000). So unless there is another trigger firing somewhere, I'm at a loss to explain this. It also seems strange that both larger and smaller values succeed in updating.
Will the data in the offending record let you do math on it?
select source_value + 10.00
August 27, 2010 at 10:41 am
maybe there is a database trigger that is causuing the issue, and not one on the table itself?
--db level triggers
select * from sys.triggers where parent_class_desc = 'DATABASE'
Lowell
August 27, 2010 at 10:57 am
What do you get when you run this?
SELECT MBLD.Total_Loan_Amount, MBL.Total_Loan_Amount
FROM dbo.MBL_Daily MBLD INNER JOIN dbo.Master_Bidder_Listing MBL ON MBL.Cert = MBLD.Cert
WHERE MBL.Total_Loan_Amount <> MBLD.Total_Loan_Amount
If this works okay, try with those numbers that won't be accepted.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 27, 2010 at 11:24 am
.5. WayneS, love the avatar. LOL!!!
1. Yes, I can do math on it in a SELECT statement. But, if I try to do an update using math putting the value between 999990 and 1000000 it's a no-go. Same error.
2. No, there are no database triggers.
3. The old value for this record is 1175872. Not an issue there. The record was updated to this value from 1017011 on 6/9/2010.
This is the weirdest thing I've ever seen. Should I run dbcc checktable and see what I get?
August 27, 2010 at 11:54 am
Those old values should be money data types, right? So they should have 4 decimals. The values you've listed don't have any. Did you just drop them because they were .0000?
So, any value that is in the offending range, whether it comes from the source table or from a math operation on the existing value, fails with the same error message?
August 27, 2010 at 11:54 am
Do you have another server available? a test server?
Please go there, create table, populate a couple of rows and run update.
Log whole session and post.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 27, 2010 at 12:30 pm
David, yes I just dropped the .0000 for convenience sake. Both columns (source and destination) are MONEY data types. All values above and below do not throw an error.
Oh, and btw, it's from 999950 to 999999, not just 999990 to 999999.
PaulB, I tried it on another server, built on the same day with the same specs, and I didn't have any issues. So you can imagine my confusion.
August 27, 2010 at 12:50 pm
SQLWannabe (8/27/2010)
PaulB, I tried it on another server, built on the same day with the same specs, and I didn't have any issues. So you can imagine my confusion.
Nice. I would start checking for differences in between the two platforms, something has to be different.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 27, 2010 at 12:57 pm
OK, reduced to stabbing in the dark. Are there computed columns in the table? You indicated the DDL was abbreviated, otherwise, I wouldn't ask...
Thanks for the interesting problem, BTW. It has certainly stimulated the little grey cells.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply