Solved

String problem "Arithmetic overflow error converting numeric to data type numeric."

Posted on 2007-11-26
10
381 Views
Last Modified: 2011-02-22
Hoi all
I am using SOL 2005 management studio to execute an sql statement..
I am having the following Error messages
"Arithmetic overflow error converting numeric to data type numeric."
The query is =
EXEC dbo.CPUIntensive @Table = 'MyServer01', @ApplicationName = '.Net SqlClient Data Provider'
GO



The problem I believe have something to do with @ApplicationName = '.Net SqlClient Data Provider'
How do I force Sql engine to take the value  '.Net SqlClient Data Provider'

Thanks in Advance
0
Comment
Question by:ZURINET
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20349453
Hello ZURINET,

can u post the definition of that sp



Aneesh R
0
 
LVL 4

Expert Comment

by:mysteriousguy
ID: 20349456
maybe there is a string concatenation in dbo.CPUIntensive
try to check this procedure.
0
 

Author Comment

by:ZURINET
ID: 20349479
Below is the query.. Note that the query works fine if there is no (.) at the start of the variable

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROC [dbo].[LongRunningProcs]
(
      @Table                  sysname,
      @Top                  int             = NULL,      
      @ApplicationName      nvarchar(256)       = NULL,
      @Debug                  bit            = 0
)
AS
BEGIN
      SET NOCOUNT ON
      
      DECLARE @SELECT nvarchar(25), @ColumnList nvarchar(250), @FROM nvarchar(150), @WHERE nvarchar(250), @ORDERBY nvarchar(250)

      SET @SELECT = 'SELECT ' + CASE WHEN @Top IS NULL OR @Top < 0 THEN 'TOP 150' ELSE 'TOP ' + LTRIM(CAST(@Top AS varchar)) END
      SET @ColumnList = ' TextData, CAST(Duration/1000. AS decimal(6, 2)) AS [Duration (in Seconds)],LoginName, NTUserName, HostName, ApplicationName, SPID, StartTime, EndTime'
      SET @FROM = ' FROM ' + QUOTENAME(COALESCE(@Owner, 'dbo')) + '.' + QUOTENAME(@Table)

      SET @WHERE = ' WHERE EventClass = '''      


      IF @ApplicationName IS NOT NULL
      BEGIN
            SET @WHERE = @WHERE + ' AND ApplicationName LIKE ' + QUOTENAME(@ApplicationName, '''')
      END      

      IF @NTUserName IS NOT NULL
      BEGIN
            SET @WHERE = @WHERE + ' AND NTUserName LIKE ' + QUOTENAME(@NTUserName, '''')
      END      


      SET @ORDERBY = 'ORDER BY Duration DESC'

      IF @Debug = 1
      BEGIN
            SELECT @SELECT + char(13) + @ColumnList + char(13) + @FROM + char(13) + @WHERE + char(13) + @ORDERBY
      END
      
      EXEC(@SELECT + @ColumnList + @FROM + @WHERE + @ORDERBY)
      
END

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:mysteriousguy
ID: 20350375
are you sure you posted the right sp?
the problem is with CPUIntensive  and your sp is LongRunningProcs.
0
 

Author Comment

by:ZURINET
ID: 20356226
Sorry about that

IF EXISTS
(
      SELECT 1
      FROM      INFORMATION_SCHEMA.ROUTINES
      WHERE      ROUTINE_NAME       = 'CPUIntensive'
      AND      ROUTINE_SCHEMA       = 'dbo'
      AND      ROUTINE_TYPE       = 'PROCEDURE'
)
DROP PROC dbo.CPUIntensive
GO

CREATE PROC dbo.CPUIntensive
(
      @Table                  sysname,
      @Top                  int             = NULL,
      @Owner                  sysname            = NULL,
      @LoginName            nvarchar(256)      = NULL,
      @HostName            nvarchar(256)       = NULL,
      @ApplicationName      nvarchar(256)       = NULL,
      @NTUserName             nvarchar(256)      = NULL,
      @StartTime            datetime      = NULL,
      @EndTime            datetime      = NULL,
      @MinCPU                  int            = NULL,
      @MaxCPU                  int            = NULL,
      @Debug                  bit            = 0
)
AS
BEGIN
      SET NOCOUNT ON
      
      DECLARE @SELECT nvarchar(25), @ColumnList nvarchar(250), @FROM nvarchar(150), @WHERE nvarchar(250), @ORDERBY nvarchar(250)

      SET @SELECT = 'SELECT ' + CASE WHEN @Top IS NULL OR @Top < 0 THEN 'TOP 150' ELSE 'TOP ' + LTRIM(CAST(@Top AS varchar)) END
      SET @ColumnList = ' TextData, CAST(Duration/1000. AS decimal(6, 2)) AS [Duration (in Seconds)], CAST(CPU/1000. AS decimal(6, 2)) AS [CPU time (in Seconds)], LoginName, NTUserName, HostName, ApplicationName, SPID, StartTime, EndTime'
      SET @FROM = ' FROM ' + QUOTENAME(COALESCE(@Owner, 'dbo')) + '.' + QUOTENAME(@Table)

      SET @WHERE = ' WHERE EventClass IN (41, 45)'

      IF @LoginName IS NOT NULL
      BEGIN
            SET @WHERE = @WHERE + ' AND LoginName LIKE ' + QUOTENAME(@LoginName, '''')
      END

      IF @HostName IS NOT NULL
      BEGIN
            SET @WHERE = @WHERE + ' AND HostName LIKE ' + QUOTENAME(@HostName, '''')
      END

      IF @ApplicationName IS NOT NULL
      BEGIN
            SET @WHERE = @WHERE + ' AND ApplicationName LIKE ' + QUOTENAME(@ApplicationName, '''')
      END      

      IF @NTUserName IS NOT NULL
      BEGIN
            SET @WHERE = @WHERE + ' AND NTUserName LIKE ' + QUOTENAME(@NTUserName, '''')
      END      

      IF (@StartTime IS NOT NULL) AND (@EndTime IS NOT NULL)
      BEGIN
            SET @WHERE = @WHERE + ' AND StartTime BETWEEN ' + QUOTENAME(CONVERT(varchar, @StartTime, 109), '''') + ' AND ' + QUOTENAME(CONVERT(varchar, @EndTime, 109), '''')
      END
      ELSE IF @StartTime IS NOT NULL
      BEGIN
            SET @WHERE = @WHERE + ' AND StartTime >= ' + QUOTENAME(CONVERT(varchar, @StartTime, 109), '''')
      END
      ELSE IF @EndTime IS NOT NULL
      BEGIN
            SET @WHERE = @WHERE + ' AND StartTime <= ' + QUOTENAME(CONVERT(varchar, @EndTime, 109), '''')
      END

      IF (@MinCPU IS NOT NULL) AND (@MaxCPU IS NOT NULL)
      BEGIN
            SET @WHERE = @WHERE + ' AND CPU BETWEEN ' + CAST(@MinCPU * 1000 AS varchar) + ' AND ' + CAST(@MaxCPU * 1000 as varchar)
      END
      ELSE IF @MinCPU IS NOT NULL
      BEGIN
            SET @WHERE = @WHERE + ' AND CPU >= ' + CAST(@MinCPU * 1000 AS varchar)
      END
      ELSE IF @MaxCPU IS NOT NULL
      BEGIN
            SET @WHERE = @WHERE + ' AND CPU <= ' + CAST(@MaxCPU * 1000 AS varchar)
      END

      SET @ORDERBY = 'ORDER BY CPU DESC'

      IF @Debug = 1
      BEGIN
            SELECT @SELECT + char(13) + @ColumnList + char(13) + @FROM + char(13) + @WHERE + char(13) + @ORDERBY
      END
      
      EXEC(@SELECT + @ColumnList + @FROM + @WHERE + @ORDERBY)
      
END
0
 

Accepted Solution

by:
ZURINET earned 0 total points
ID: 20356372
Problem solved by me.. :-)

The error typically occurs when you are trying to pass a numeric or decimal value that is too large or larger than define value
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20356378
i didnt find any issues there,it is working fine for me...
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20491692
Closed, 250 points refunded.
Vee_Mod
Community Support Moderator
0
 

Expert Comment

by:siskinds
ID: 34955555
ZURINET:

I would appreciate knowing what you changed with that stored procedure to solve the problem.  I'm having exactly the same problem, using exactly the same stored procedure - and would love to know what was changed to resolve.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Some sers suddenly getting error popup msg 28 89
SQL date incremented 11 31
Abstract Express Replacement Software 12 28
This query failed in sql 2014 5 31
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question