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

Hi:

I am trying to pump data from Sybase to SQL Server using SSIS and I get this error:

Conversion failed because the data overflowed the specified type

The data on the external column metadata shows as type database timestamp, as does the output column. The database values are all datetime, coming in through OLEDB to Sybase. Any idea what could be going on here?

Thanks,

Kayda

I should correct, the data is coming from a datetime field in Sybase to a datetime field in SQL Server. The values in Sybase are all datetime--it runs for awhile and then gives this error.

Any ideas?
|||Just wondering whether anyone has the answer to this--this problem is cropping up again. There isn't nothing wrong in Sybase with the datetime field as far as I can tell...|||

A quick search did not yield any good results on differences between SQL Server and Sybase DATETIME data types. The thing that seems most likely to me that there are different data ranges supported by each platform.

Can you select a MIN and MAX of the problem field when the error is occuring?

|||

Yes, I looked for NULLs and did a MIN and MAX on Sybase--just normal dates from 2004 up until "yesterday". I am running this pump on many other similar tables with the same time field. Is there a way to figure out exactly is going on?

Thanks,

Kayda

|||

Hi Kayda,

Is the error coming from the source component, or the destination (on insertion)?

What are your source and destination components?

Can you try to hook up an error output, and have the error rows redirected to see if you can identify the data that is failing?

Thanks

Mark

|||

I tried something like that, but didn't seem to work. Do you mean just hooking a table to the red arrow or putting a transform in between? Could you give me an idea of what might work in this particular case?

|||

Kayda_SQL wrote:

I tried something like that, but didn't seem to work. Do you mean just hooking a table to the red arrow or putting a transform in between? Could you give me an idea of what might work in this particular case?

It might be faster to connect the red arrow to a row count transformation and then add a data viewer to that path. But yes, you could hook that red arrow up to an OLE DB destination and store the contents in a table.

Just make sure that you aren't using the fast load option (for this test) so that you get each row in error instead of a full batch. Know that this is much slower though -- but it should give you the data at least along with an error code.

Overflow error

Hi:

I am trying to pump data from Sybase to SQL Server using SSIS and I get this error:

Conversion failed because the data overflowed the specified type

The data on the external column metadata shows as type database timestamp, as does the output column. The database values are all datetime, coming in through OLEDB to Sybase. Any idea what could be going on here?

Thanks,

Kayda

I should correct, the data is coming from a datetime field in Sybase to a datetime field in SQL Server. The values in Sybase are all datetime--it runs for awhile and then gives this error.

Any ideas?|||Just wondering whether anyone has the answer to this--this problem is cropping up again. There isn't nothing wrong in Sybase with the datetime field as far as I can tell...|||

A quick search did not yield any good results on differences between SQL Server and Sybase DATETIME data types. The thing that seems most likely to me that there are different data ranges supported by each platform.

Can you select a MIN and MAX of the problem field when the error is occuring?

|||

Yes, I looked for NULLs and did a MIN and MAX on Sybase--just normal dates from 2004 up until "yesterday". I am running this pump on many other similar tables with the same time field. Is there a way to figure out exactly is going on?

Thanks,

Kayda

|||

Hi Kayda,

Is the error coming from the source component, or the destination (on insertion)?

What are your source and destination components?

Can you try to hook up an error output, and have the error rows redirected to see if you can identify the data that is failing?

Thanks

Mark

|||

I tried something like that, but didn't seem to work. Do you mean just hooking a table to the red arrow or putting a transform in between? Could you give me an idea of what might work in this particular case?

|||

Kayda_SQL wrote:

I tried something like that, but didn't seem to work. Do you mean just hooking a table to the red arrow or putting a transform in between? Could you give me an idea of what might work in this particular case?

It might be faster to connect the red arrow to a row count transformation and then add a data viewer to that path. But yes, you could hook that red arrow up to an OLE DB destination and store the contents in a table.

Just make sure that you aren't using the fast load option (for this test) so that you get each row in error instead of a full batch. Know that this is much slower though -- but it should give you the data at least along with an error code.

Overflow error

Hi:

I am trying to pump data from Sybase to SQL Server using SSIS and I get this error:

Conversion failed because the data overflowed the specified type

The data on the external column metadata shows as type database timestamp, as does the output column. The database values are all datetime, coming in through OLEDB to Sybase. Any idea what could be going on here?

Thanks,

Kayda

I should correct, the data is coming from a datetime field in Sybase to a datetime field in SQL Server. The values in Sybase are all datetime--it runs for awhile and then gives this error.

Any ideas?|||Just wondering whether anyone has the answer to this--this problem is cropping up again. There isn't nothing wrong in Sybase with the datetime field as far as I can tell...|||

A quick search did not yield any good results on differences between SQL Server and Sybase DATETIME data types. The thing that seems most likely to me that there are different data ranges supported by each platform.

Can you select a MIN and MAX of the problem field when the error is occuring?

|||

Yes, I looked for NULLs and did a MIN and MAX on Sybase--just normal dates from 2004 up until "yesterday". I am running this pump on many other similar tables with the same time field. Is there a way to figure out exactly is going on?

Thanks,

Kayda

|||

Hi Kayda,

Is the error coming from the source component, or the destination (on insertion)?

What are your source and destination components?

Can you try to hook up an error output, and have the error rows redirected to see if you can identify the data that is failing?

Thanks

Mark

|||

I tried something like that, but didn't seem to work. Do you mean just hooking a table to the red arrow or putting a transform in between? Could you give me an idea of what might work in this particular case?

|||

Kayda_SQL wrote:

I tried something like that, but didn't seem to work. Do you mean just hooking a table to the red arrow or putting a transform in between? Could you give me an idea of what might work in this particular case?

It might be faster to connect the red arrow to a row count transformation and then add a data viewer to that path. But yes, you could hook that red arrow up to an OLE DB destination and store the contents in a table.

Just make sure that you aren't using the fast load option (for this test) so that you get each row in error instead of a full batch. Know that this is much slower though -- but it should give you the data at least along with an error code.