Hi all,

I defined unsigned_int in my database, which uses unsigned_int_range
rule. The unsigned_int_range 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

royaldothighness@hotmail.com (royaldothighness@hotmail.com) writes:
> I defined unsigned_int in my database, which uses unsigned_int_range
> rule. The unsigned_int_range 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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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_value 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!