User Defined Data Types Problem

[royaldothighness@hotmail.com]

Hi all,

I defined unsigned_int in my database, which uses unsigned_int_ra nge
rule. The unsigned_int_ra nge rule is defined as follows:
@unsigned_int >=0 and @unsigned_int <=4294967295

(4294967295 = 0xFFFFFFFF)

The storage size is 4 bytes.

One of the tables in the database contains a field that is of type
unsigned_int.

When I try to add a new record into the table (ex: using the Enterprise
Manager), it always fails in the unsigned_int field if I enter a value
greater than 2147483647 (which is 0x7FFFFFFF) all the way to 4294967295
(0xFFFFFFFF). The Enterprise Manager shows the following message:

"The value you entered is not consistent with the data type or
length of the column, or over grid buffer limit."

What is wrong here? The 4-byte storage should be good for any value
from 0 to 4294967295.

Any help is appreciated.

Thanks,
Ken

[Erland Sommarskog]

Re: User Defined Data Types Problem

royaldothighnes s@hotmail.com (royaldothighne ss@hotmail.com) writes:

I defined unsigned_int in my database, which uses unsigned_int_ra nge
rule. The unsigned_int_ra nge rule is defined as follows:
@unsigned_int >=0 and @unsigned_int <=4294967295
>
(4294967295 = 0xFFFFFFFF)
>
The storage size is 4 bytes.
>
One of the tables in the database contains a field that is of type
unsigned_int.
>
When I try to add a new record into the table (ex: using the Enterprise
Manager), it always fails in the unsigned_int field if I enter a value
greater than 2147483647 (which is 0x7FFFFFFF) all the way to 4294967295
(0xFFFFFFFF). The Enterprise Manager shows the following message:
>
"The value you entered is not consistent with the data type or
length of the column, or over grid buffer limit."
>
What is wrong here? The 4-byte storage should be good for any value
from 0 to 4294967295.

Since you mention Enterprise Manager, I assume that you use SQL 2000.

To define a user-defined data type with that range you would have to
to base it on bigint or decimal(10, 0). You cannot use int as a base,
since int is signed.

In SQL 2005, you could define your unsigned int throught the CLR, although
I doubt that it would be worth the pain.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Alexander Kuznetsov]

Re: User Defined Data Types Problem

Ken,

If you want both 4 byte storage and 0 to 4294967295 range, you could
store an int stored_value = (your_value - 2147483648) and have a
computed column (cast(stored_va lue as bigint) + 2147483648). Not sure
if saving 4 bytes of storage is worth the effort in your case, but
sometimes it definitely is worth trying.

Good luck!