In the case of that DMV (and probably in many other) the problem is that the internal value which populates the column (total_elapsed_time, or open_transaction_count, or open_resultset_count for that matter) is returned as a ULONG (32bit unsigned integer covering a range of values spanning 0 to 4294967295 decimal.)

However, the GetRow method of the internal table valued function sysrequests grabs that value and stores it into a CXVariant of type XVT_I4 which corresponds to a 32 signed integer covering the range from -2147483648 through 2147483647.

Therefore the following sequence of bits

01111111 11111111 11111111 11111111

both as a ULONG or a LONG represents the same decimal value: 2147483647.

But adding one to it results in

10000000 00000000 00000000 00000000

which as ULONG represents decimal 2147483648, but as a LONG represents -2147483648.

So, as a workaround, for columns with that problem you can refer to them like this:

case when total_elapsed_time < 0 then 2147483647+(2147483649+total_elapsed_time) else total_elapsed_time end as total_elapsed_time

and that way you always get the correct unsigned positive integer representation of whatever value, even when the DMV returns a negative signed representation.

Thanks,

Nacho

Couldn't we just do something like <code>SELECT CAST(total_elapsed_time & 0x7fffffffffffffff AS DECIMAL(38,0))</code> rather than using a CASE?

Not exactly, ANDing a bitmask which only has its leftmost (sign) bit set to zero to either 2147483648 or 0, would produce zero as a result which is not exactly what I'm pursuing with the CASE above.

Can you indicate what versions of SQL Server this impacts?