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
Solved

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

Posted on 2007-11-26
10
382 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
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

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

809 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