Solved

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

Posted on 2007-11-26
10
380 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
Comment Utility
Hello ZURINET,

can u post the definition of that sp



Aneesh R
0
 
LVL 4

Expert Comment

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

Author Comment

by:ZURINET
Comment Utility
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
 
LVL 4

Expert Comment

by:mysteriousguy
Comment Utility
are you sure you posted the right sp?
the problem is with CPUIntensive  and your sp is LongRunningProcs.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:ZURINET
Comment Utility
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
Comment Utility
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
Comment Utility
i didnt find any issues there,it is working fine for me...
0
 
LVL 1

Expert Comment

by:Vee_Mod
Comment Utility
Closed, 250 points refunded.
Vee_Mod
Community Support Moderator
0
 

Expert Comment

by:siskinds
Comment Utility
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now