issue with the conversion..no clue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

issue with the conversion..no clue

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2007-01-23 : 08:03:57

Hi all

when i try to execute the following query, i am getting the error
Server: Msg 248, Level 16, State 1, Line 1
The conversion of the nvarchar value '10000000003' overflowed an int column. Maximum integer value exceeded.

Query is:

SELECT  REPORT_SCHEDULER.USER_DEFINED_REPORT_NAME,
        convert(varchar(20), REPORT_SCHEDULER.INITIAL_REPORT_UPDATE_DATE, 113) AS INITIAL_REPORT_UPDATE_DATE_AL,
        convert(varchar(20), REPORT_SCHEDULER.INITIAL_REPORT_UPDATE_DATE, 113) AS INITIAL_REPORT_UPDATE_DATE,
        REPORT_SCHEDULER.FREQUENCY,
        REPORT_SCHEDULER.DELIVERY_ADDR,
        REPORT_SCHEDULER.STATUS,
        REPORT_SCHEDULER.OUTPUT_TYPE,
        REPORT_SCHEDULER.SITE_DESCRIPTION,
        REPORT_SCHEDULER.LANGUAGE_CODE,
        REPORT_SCHEDULER.USER_ID,
        REPORT_SCHEDULER.REPORT_SCHEDULER_ID,
        REPORT_SCHEDULER.SHOW_PARTY_DETAIL,
        convert(varchar(20), REPORT_SCHEDULER.LAST_UPDATE_DATE, 113) AS LAST_UPDATE_DATE_AL,
        convert(varchar(20), REPORT_SCHEDULER.LAST_UPDATE_DATE, 113) AS LAST_UPDATE_DATE,
        convert(varchar(20), REPORT_SCHEDULER.NEXT_UPDATE_DATE, 113) AS NEXT_UPDATE_DATE_AL,
        convert(varchar(20), REPORT_SCHEDULER.NEXT_UPDATE_DATE, 113) AS NEXT_UPDATE_DATE,
        REPORT_SCHEDULER.ROW_COUNT,
        REPORT_SCHEDULER.REPORT_INFO_ID,
        convert(varchar(20), REPORT_SCHEDULER.UPD_DATE, 113) AS UPD_DATE_AL,
        convert(varchar(20), REPORT_SCHEDULER.UPD_DATE, 113) AS UPD_DATE,
        REPORT_SCHEDULER.UPD_VERSION,
        REPORT_SCHEDULER.UPD_USER
FROM            
        REPORT_SCHEDULER
 WHERE REPORT_SCHEDULER_ID=1121

Table structure is

CREATE TABLE [dbo].[REPORT_SCHEDULER] (
	[REPORT_SCHEDULER_ID] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ROW_COUNT] [numeric](18, 0) NULL ,
	[STATUS] [numeric](18, 0) NULL ,
	[DELIVERY_TYPE] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DELIVERY_ADDR] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LAST_UPDATE_DATE] [datetime] NULL ,
	[NEXT_UPDATE_DATE] [datetime] NULL ,
	[FREQUENCY] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[USER_ID] [numeric](18, 0) NULL ,
	[UPD_VERSION] [numeric](18, 0) NULL ,
	[UPD_USER] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UPD_DATE] [datetime] NULL ,
	[SHOW_PARTY_DETAIL] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[USER_DEFINED_REPORT_NAME] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[INITIAL_REPORT_UPDATE_DATE] [datetime] NULL ,
	[SITE_DESCRIPTION] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OUTPUT_TYPE] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[REPORT_INFO_ID] [numeric](18, 0) NULL ,
	[LANGUAGE_CODE] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

I have no clue why i am getting the above error as there is no column defined as datatype of int

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-23 : 08:08:40

REPORT_SCHEDULER_ID is nvarchar and you are comparing with an integer. SQL Server will try to convert to integer before comparing and one of the rows value exceeded the max int value.

specify 1121 as string


WHERE REPORT_SCHEDULER_ID='1121'

venkath
Posting Yak Master

202 Posts

Posted - 2007-01-23 : 08:22:45

but the below query is returning a row..How come?

SELECT  REPORT_SCHEDULER.USER_DEFINED_REPORT_NAME,
        convert(varchar(20), REPORT_SCHEDULER.INITIAL_REPORT_UPDATE_DATE, 113) AS INITIAL_REPORT_UPDATE_DATE_AL,
        convert(varchar(20), REPORT_SCHEDULER.INITIAL_REPORT_UPDATE_DATE, 113) AS INITIAL_REPORT_UPDATE_DATE,
        REPORT_SCHEDULER.FREQUENCY,
        REPORT_SCHEDULER.DELIVERY_ADDR,
        REPORT_SCHEDULER.STATUS,
        REPORT_SCHEDULER.OUTPUT_TYPE,
        REPORT_SCHEDULER.SITE_DESCRIPTION,
        REPORT_SCHEDULER.LANGUAGE_CODE,
        REPORT_SCHEDULER.USER_ID,
        REPORT_SCHEDULER.REPORT_SCHEDULER_ID,
        REPORT_SCHEDULER.SHOW_PARTY_DETAIL,
        convert(varchar(20), REPORT_SCHEDULER.LAST_UPDATE_DATE, 113) AS LAST_UPDATE_DATE_AL,
        convert(varchar(20), REPORT_SCHEDULER.LAST_UPDATE_DATE, 113) AS LAST_UPDATE_DATE,
        convert(varchar(20), REPORT_SCHEDULER.NEXT_UPDATE_DATE, 113) AS NEXT_UPDATE_DATE_AL,
        convert(varchar(20), REPORT_SCHEDULER.NEXT_UPDATE_DATE, 113) AS NEXT_UPDATE_DATE,
        REPORT_SCHEDULER.ROW_COUNT,
        REPORT_SCHEDULER.REPORT_INFO_ID,
        convert(varchar(20), REPORT_SCHEDULER.UPD_DATE, 113) AS UPD_DATE_AL,
        convert(varchar(20), REPORT_SCHEDULER.UPD_DATE, 113) AS UPD_DATE,
        REPORT_SCHEDULER.UPD_VERSION,
        REPORT_SCHEDULER.UPD_USER
FROM            
        REPORT_SCHEDULER
 WHERE REPORT_SCHEDULER_ID=10000000003

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-23 : 08:26:59

when you specify 1121, SQL Server treats it as an integer. But 10000000003 it is bigint. So converting REPORT_SCHEDULER_ID to bigint does not result in overflow

try


WHERE REPORT_SCHEDULER_ID = convert(bigint, 1121)

venkath
Posting Yak Master

202 Posts

Posted - 2007-01-23 : 08:30:06

i got your point

Thanks a lot..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 08:38:20

Now you have forced the compiler to treat 10000000003 as BIGINT.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 08:39:05

Peter Larsson
Helsingborg, Sweden

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-01-23 : 09:16:14

surely
"WHERE REPORT_SCHEDULER_ID = convert(nvarchar(40), 1121)"
would be more accurate....especially to keep the column definitions on both sides of the query relation, in sync for 'index' usage reasons.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources