Overflowed int column

I am trying to run a stored procedure but I get the
following error:
Server: Msg 248, Level 16, State 1, Procedure
sp_MarriageLookup, Line 34
The conversion of the varchar value '2820103430'
overflowed an int column. Maximum integer value exceeded.
Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
Return Code = -6
Output Parameter(s):
@.Barcode = 2820103430
The length of the @.Barcode variable is 10 characters and
it is a nchar type variable so I don't understand the 'int
column' overflow issue.
Also, the wierd thing is that I can run this same stored
procedure on another server (similar database, just
different data) and it works just fine. See below:
Stored Procedure: Unifirst.dbo.sp_MarriageLookup
Return Code = 0
Output Parameter(s):
@.Barcode = 4320000849
I don't see anything noticibly different between the two
databases.
What causes this problem and how do I correct?
Roger.Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.
In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter
ph

> I am trying to run a stored procedure but I get the
> following error:
> Server: Msg 248, Level 16, State 1, Procedure
> sp_MarriageLookup, Line 34
> The conversion of the varchar value '2820103430'
> overflowed an int column. Maximum integer value exceeded.
> Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
> The length of the @.Barcode variable is 10 characters and
> it is a nchar type variable so I don't understand the 'int
> column' overflow issue.
> Also, the wierd thing is that I can run this same stored
> procedure on another server (similar database, just
> different data) and it works just fine. See below:
> Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
> I don't see anything noticibly different between the two
> databases.
> What causes this problem and how do I correct?
> Roger.

Overflowed int column

I am trying to run a stored procedure but I get the
following error:
Server: Msg 248, Level 16, State 1, Procedure
sp_MarriageLookup, Line 34
The conversion of the varchar value '2820103430'
overflowed an int column. Maximum integer value exceeded.
Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
Return Code = -6
Output Parameter(s):
@.Barcode = 2820103430
The length of the @.Barcode variable is 10 characters and
it is a nchar type variable so I don't understand the 'int
column' overflow issue.
Also, the wierd thing is that I can run this same stored
procedure on another server (similar database, just
different data) and it works just fine. See below:
Stored Procedure: Unifirst.dbo.sp_MarriageLookup
Return Code = 0
Output Parameter(s):
@.Barcode = 4320000849
I don't see anything noticibly different between the two
databases.
What causes this problem and how do I correct?
Roger.Here is a copy of the SP:
/*
Purpose: Given a marriage tag code, return the original
barcode
Input params: Marriage tag code, type varchar(10)
Output params: Original bar code, type varchar(10)
Error params: Output of '9' indicates lookup failed
*/
CREATE PROCEDURE sp_MarriageLookup
@.Tag varchar(10),
@.Barcode varchar(10) output
AS
Declare @.eventid int
Declare cur_Marriage cursor for
Select CurrentTag from tblMarriage
Where MarriageTag = @.Tag
Open cur_Marriage
Fetch next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status <> 0)
Begin
/* Error log: Lookup failed */
Set @.Barcode = 9
Set @.eventid = 901
End
Else Begin
Fetch Next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status = 0)
Begin
/* Error log: More than one entry for the marriage
code */
Set @.Barcode = 9
Set @.eventid = 902
End
End
Close cur_Marriage
Deallocate cur_Marriage
If (@.Barcode = 9)
Begin
Execute sp_LogError 1, @.eventid, 0, @.Tag
End
GO
>--Original Message--
>I am trying to run a stored procedure but I get the
>following error:
>Server: Msg 248, Level 16, State 1, Procedure
>sp_MarriageLookup, Line 34
>The conversion of the varchar value '2820103430'
>overflowed an int column. Maximum integer value exceeded.
>Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
>The length of the @.Barcode variable is 10 characters and
>it is a nchar type variable so I don't understand
the 'int
>column' overflow issue.
>Also, the wierd thing is that I can run this same stored
>procedure on another server (similar database, just
>different data) and it works just fine. See below:
>Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
>I don't see anything noticibly different between the two
>databases.
>What causes this problem and how do I correct?
>Roger.
>.
>|||Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.
In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter
ph
> I am trying to run a stored procedure but I get the
> following error:
> Server: Msg 248, Level 16, State 1, Procedure
> sp_MarriageLookup, Line 34
> The conversion of the varchar value '2820103430'
> overflowed an int column. Maximum integer value exceeded.
> Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
> The length of the @.Barcode variable is 10 characters and
> it is a nchar type variable so I don't understand the 'int
> column' overflow issue.
> Also, the wierd thing is that I can run this same stored
> procedure on another server (similar database, just
> different data) and it works just fine. See below:
> Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
> I don't see anything noticibly different between the two
> databases.
> What causes this problem and how do I correct?
> Roger.

overflow error

i m getting date like this 25-Dec-2006.....now i want to convert this date to 2006-12-25...

but i m getting erorr ?

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date into #SuccessBJSIP from tbl_cams_uploaddetails WITH(NOLOCK)

join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%',tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 4) and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=left(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) - 1) and
tbl_cams_uploaddetails.trade_date=convert(datetime,parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2),126)
where tbl_cams_uploaddetails.compare_status='Pending' and Payment_Mechanism = 'EC'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (88,89)
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date
having count(tbl_cams_uploaddetails.folio_no) = 1

instead of your manual logic try to use the Convert(datetime, tbl_cms_uploaddetails.AdditionalField1)|||hi mani thanx for reply.....acyually in additionalfoeld1 i m getting string like this

1930454/IFG/25/DEC-06/15878....so after doing parsing i m getting date 25-Dec-2006......

|||

check all your rows retunrs the same value as you expected (only date)..

You can use the following expression...

Case When IsDate(parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2)) = 1 Then

Convert(datetime, parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2))

Else NULL END

|||

try this, you can change you convert(datetime.. to convert(smalldatetime.. and you can also change the tbl_cams_uploaddetails.trade_date so that both have the same format

convert(varchar(10),tbl_cams_uploaddetails.trade_date,101)=convert(varchar(10),convert(smalldatetime,parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2)),101)

|||

i added this in my existing query..now it is returning 12 records......and when i run my actual query than it returns only 3 records....

why?

|||i tride yours also...getting same error message.|||

Remove the Group BY clause & validate your records ..

|||same result..3 and 12 records.|||hmm.. did you check you excel if it has dirty date values?
|||

How about rewriting the query as follow as...

select

tbl_cams_uploaddetails.amount

,tbl_cams_uploaddetails.folio_no

,tbl_cams_uploaddetails.Scheme_Code

,tbl_cams_uploaddetails.trade_date into #SuccessBJSIP

From

tbl_cams_uploaddetails WITH(NOLOCK)

join tbl_cms_uploaddetails WITH(NOLOCK) on

tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount

and tbl_cms_uploaddetails.AdditionalField1Like cast(tbl_cams_uploaddetails.Scheme_Code as varchar) + '/' +

tbl_cams_uploaddetails.folio_no+ '/' + Cast(day(tbl_cams_uploaddetails.trade_date) as varchar) + '/' +

Upper(Substring(Datename(MM,tbl_cams_uploaddetails.trade_date),1,3)) + '-' +

Cast(Year(tbl_cams_uploaddetails.trade_date) as varchar) + '/%'

Where

tbl_cams_uploaddetails.compare_status='Pending'

andPayment_Mechanism = 'EC'

and tbl_cms_uploaddetails.compare_status='Pending'

and Format_ID in (88,89)

group by

tbl_cams_uploaddetails.amount

,tbl_cams_uploaddetails.folio_no

,tbl_cams_uploaddetails.Scheme_Code

,tbl_cams_uploaddetails.trade_date

|||now 5 minutes passed..still i m waiting for query result...still it is executing.|||

Its bcs of the expression may violate to use the index - the expression.

The operator LIKE also may cause this issue...

'1930454/IFG/25/DEC-06/15878' here what is 15878 stands for. Are you connecting this value also on join?

|||no i m not using this value 15878