Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DAMN PUZZLING ... Convert to Numeric Issue !!!

Status
Not open for further replies.

JayKusch

MIS
Joined
Oct 30, 2001
Messages
3,199
Location
US
Have an INTERESTING issue !!! I have a stored procedure (SP) that returns a record set of database growth activity. I am trying to capture the output to a table with an INSERT INTO. When I execute the SP as follows

exec usp_Monitor_DB_Growth

The message returned is as follows:

Server: Msg 8115, Level 16, State 8, Procedure usp_Monitor_DB_Growth, Line 17 Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.


Here is the code for the SP …


CREATE PROC usp_Monitor_DB_Growth
( @dbnameParam sysname = NULL )

AS

BEGIN

DECLARE @dbname sysname

/* Work with current database if a database name is not specified */

SET @dbname = COALESCE(@dbnameParam, DB_NAME())

TRUNCATE TABLE Monitor_DB_Growth

INSERT INTO Monitor_DB_Growth

SELECT
@dbname AS [Database Name],
'DB' AS [DB_or_TLog],
CONVERT(char, backup_start_date, 111) AS [Date],
CONVERT(char, backup_start_date, 108) AS [Time],
CONVERT(numeric(15,2),file_size/1048576) AS [File Size(MB)],
Growth AS [Growth Percentage (%)],
GetDate() AS [Date_Stats_Pulled]
FROM
(
SELECT b.backup_start_date,
a.backup_set_id,
a.file_size,
a.logical_name,
a.[filegroup_name],
a.physical_name,
(
SELECT CONVERT(numeric(15,2),((a.file_size * 100.00)/bf.file_size)-100)
FROM MSDB.dbo.backupfile bf
WHERE bf.backup_set_id =
(
SELECT MAX(bf2.backup_set_id)
FROM MSDB.dbo.backupfile AS bf2
JOIN MSDB.dbo.backupset AS bs
ON bf2.backup_set_id = bs.backup_set_id
WHERE bf2.backup_set_id < a.backup_set_id AND
bf2.file_type ='D' AND
bs.database_name = @dbname AND
bf2.logical_name = a.logical_name AND
bf2.logical_name = bf.logical_name AND
bs.type = 'D'
) AND
bf.file_type = 'D'
) AS Growth
FROM MSDB.dbo.backupfile AS a
JOIN MSDB.dbo.backupset AS b
ON a.backup_set_id = b.backup_set_id
WHERE b.database_name = @dbname AND
a.file_type = 'D' AND
b.type = 'D'

) AS Derived
WHERE (Growth <> 0.0) OR
(Growth IS NULL)
ORDER BY logical_name, [Date]

END
GO


As you can see, I have the statement of INSERT INTO Monitor_DB_Growth.

If you comment out that line of the SP, it runs fine and returns the values as expected.

Here is a copy of the table named Monitor_DB_Growth.

CREATE TABLE [dbo].[Monitor_DB_Growth] (
[Server_Name] [varchar] (50) NULL ,
[DB_or_TLog] [char] (4) NULL ,
[Date_Last_Extended] [datetime] NULL ,
[Time_Last_Extended] [datetime] NULL ,
[File_Size] [numeric](28, 0) NULL ,
[Growth_Percent] [decimal](5, 2) NULL ,
[Date_Stats_Pulled] [datetime] NULL
) ON [PRIMARY]


AS well as a copy of the MSDB.dbo.backupfile (of course you cannot script out sys tables like the example above, so here is a brief description) …

backup_set_id int 4 0
first_family_number tinyint 1 1
first_media_number smallint 2 1
filegroup_name nvarchar 128 1
page_size int 4 1
file_number numeric 9 0
backed_up_page_count numeric 9 1
file_type char 1 1
source_file_block_size numeric 9 1
file_size numeric 13 1
logical_name nvarchar 128 1
physical_drive varchar 260 1
physical_name varchar 260 1


The field in question, I believe, is the file_size. If you view this field in design mode, it shows it has precision set to 20. I am only allowed to set my value with a precision of 18.

IS THIS THE ISSUE … and can I resolve it in order to load my table? Any and all help is always appreciated !!!

PS. This SP is being run predominately from SS7. Have yet to try it out in SS2K though I believe it should fly.

Thanks

J. Kusch
 
No takers on this one??? The gurus must have the day off - lol
Thanks

J. Kusch
 
Perhaps if you simplified your request. Strip it down to a small amount of code that exhibits the problem. With what you posted, it would take way too much time for one of us to set up a test bench to duplicate the symptoms.
 
Top of the post explains everything. the rest of the post is for those who wish to see all the facts. better to post all info than to repost continuously for those who wish to see it. Bottom line is a numeric conversion error



Server: Msg 8115, Level 16, State 8, Procedure usp_Monitor_DB_Growth, Line 17 Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.




Thanks

J. Kusch
 
Took a quick look at your code, and my WAG is that this might be the problem:

[tt]CONVERT(numeric(15,2),((a.file_size * 100.00)/bf.file_size)-100)[/tt]

The numeric(15,2) might not be sufficient. --------------
 
Iv'e bumped it up to the max 18,2 with no luck. If you take a look at the system table in the initial BIG post, you will see that the field named file_size is set to a numeric with a lenght of 13 and percision OF 20. You can see the percision when you go into design mode on the table in the Master DB. Very intersting ... thanks for the eyes on this issue!!!
Thanks

J. Kusch
 
Just out of curiosity, I created a test table with a numeric field and assigned it a precision of 20. It took it and let me put in values of that length, so maybe 18 isn't the limit after all. In fact I found the max precision my server is set for is 38 by using the @@MAX_PRECISION system variable. Suggest you try setting your field to a higher precision. Hey learn something new everyday, I didn't know you could go past the 18 precision either.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top