August 7, 2011 at 12:23 pm
Using studentid which is as Uniqueid datatype by doing select @studentID = @@IDENTITY
I'm getting below error:
Arithmetic overflow error converting expression to data type int.
It is ok to use SCOPE_IDENTITY() to fix the error
Thanks in advance for your help!
August 7, 2011 at 2:40 pm
What is the data type of the identity column on the table you are inserting into?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 7, 2011 at 4:24 pm
Jeff - It is uniqueid datatype
August 7, 2011 at 4:33 pm
I was hoping that would lead you to look at how the variable @studentID is defined. Also, I believe you meant to say it is uniqueidentifier.
So, you have 2 problems - the first is that @@identity and scope_identity return values from IDENTITY columns which I do not believe can be defined as uniqueidentifier. And second, you are trying to put a non-integer value into an integer variable - thus, the overflow error.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 7, 2011 at 4:55 pm
@studentid defined as uniqueid which is numeric - I'm thinking it is problem with excceding the length of value causing this problem .
August 7, 2011 at 6:27 pm
I don't think there is a built in datatype called uniqueid in T-SQL, so presumably this is a user defined type. Can you post the type definition, please?
Tom
August 7, 2011 at 7:22 pm
My first bet like Jeff would be a bigint identity.
Then the variable being declared as int or smallint if it's an int identity.
I don't really think it can be a gui because you'd get a casting error, not overflow.
P.S. Yes I'd use SCOPE_IDENTITY(), but not to fix this error, but to prevent future bugs.
If you are inserting more than 1 rows in a single statement, you can look at the output clause of the insert statement to get all the new ids in 1 dataset.
August 7, 2011 at 7:36 pm
It is user definition with Uniqueid(numeric(16,0) and passing as output still seeing this error.
August 7, 2011 at 7:53 pm
vision59 (8/7/2011)
It is user definition with Uniqueid(numeric(16,0) and passing as output still seeing this error.
That's well over the limit of an int and even a bigint..
http://sqlserverplanet.com/sql/sql-server-bigint-max-value/
Declare your variable as numeric(16,0) and it will work.
Open question, could he declare the variable as uniqueid? I don't remember reading anything about that and I'm not sure at all if it's possible.
That would solve the problem and prevent future problems when the udt is changed to something else.
August 7, 2011 at 8:18 pm
Tried that to change numeric (16,0) still same error .
problem passing output as select @student id = @@identity if I coment it it works fine without error. Looking for workaround for it
August 7, 2011 at 8:34 pm
Can you do a quick select from the table and post what the value is?
Can you post the rest of the code so we can all see what's going on?
August 7, 2011 at 8:48 pm
Ninja's_RGR'us (8/7/2011)
vision59 (8/7/2011)
It is user definition with Uniqueid(numeric(16,0) and passing as output still seeing this error.That's well over the limit of an int and even a bigint..
Well, no. The maximum possible bigint is 9223372036854775807
The maximum possible numeric(16,0) value is 9999999999999999, which is quite a lot smaller.
So I imagine using bigint instead of int would work.
Declare your variable as numeric(16,0) and it will work.
Open question, could he declare the variable as uniqueid? I don't remember reading anything about that and I'm not sure at all if it's possible.
That would solve the problem and prevent future problems when the udt is changed to something else.
You are right, it would be better to declare it the same way as the thing being copied to it is declared.
If the datatype is defined as a user defined data type (as opposed to a user defined type, on which I can't comment as I don't know the SQL rules for CLR types) in the current database it can be used to declare a variable of that type, or to declare a column in a table; it can't be used to define a column in a temp table unless it is also defined in tempdb (because three part type names are not allowed). So yes, he could declare the variable as uniqueid if (except maybe he would need to use the two part name - user defined data types are schema-specific [that's schema meaning what MS calls a schema, not what a relational database would call a schema]).
Tom
August 7, 2011 at 9:19 pm
vision59 (8/7/2011)
Tried that to change numeric (16,0) still same error .problem passing output as select @student id = @@identity if I coment it it works fine without error. Looking for workaround for it
There seems to be something inconsistent here, I'm not sure that I understand what you are doing, nor am I sure that you have understood everything people have asked you. So I'll try with a set of four questions which I hope will be very clear, and maybe you can answer those, and then perhaps someone will be able to help fix your problem. But first of all change your code to use
1) You have a type called uniqueid. How was it defined? Was it defined by "create type uniqueid from numeric(16,)" or by something else? If it's something else, please tell us what.
2) You have a table with an identity column. Is the type of that identity column uniqueid, or is it something else? If it's something else, please tell us what.
3) You have a variable @studentid. How is that variable declared? By "declare @studentid uniqueid" or by something else? If it's something else, please tell us what.
4) What is the maximum value in the identity column in the table? You can get this by running this line of code:
dbcc checkident('tablename')
You also need to think about whether you want to use @@IDENTITY, IDENT_CURRENT, or SCOPE_IDENTITY.
Tom
August 8, 2011 at 6:13 am
I'm sure I'm missing something, but it looks like you're trying to use @@IDENTITY with a uniqueidentifier data type. They're not the same and that would explain the fundamental issue. If your code is generating a new uniqueidentifier, you'll need to retrieve that directly from the code or by querying the database through the logical key. You can't get it with @@IDENTITY or SCOPE_IDENTITY() either.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 8, 2011 at 7:32 am
Grant Fritchey (8/8/2011)
I'm sure I'm missing something, but it looks like you're trying to use @@IDENTITY with a uniqueidentifier data type. They're not the same and that would explain the fundamental issue. If your code is generating a new uniqueidentifier, you'll need to retrieve that directly from the code or by querying the database through the logical key. You can't get it with @@IDENTITY or SCOPE_IDENTITY() either.
Perhaps his statement that uniqueid is defined as numeric(16,0). means that uniqueid is a user defined data type, not an abbreviation for UniqueIdentifier. That was my impression (but it may not be right, as communication in this thread has not been the best).
Tom
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply