Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

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

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
ZURINET
Asked:
ZURINET
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello ZURINET,

can u post the definition of that sp



Aneesh R
0
 
mysteriousguyCommented:
maybe there is a string concatenation in dbo.CPUIntensive
try to check this procedure.
0
 
ZURINETAuthor Commented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
mysteriousguyCommented:
are you sure you posted the right sp?
the problem is with CPUIntensive  and your sp is LongRunningProcs.
0
 
ZURINETAuthor Commented:
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
 
ZURINETAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
i didnt find any issues there,it is working fine for me...
0
 
Vee_ModCommented:
Closed, 250 points refunded.
Vee_Mod
Community Support Moderator
0
 
siskindsCommented:
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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now