Solved

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

Posted on 2007-11-26
10
385 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

630 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