PRB: Query Analyzer Returns Error Message When You Select NEWID() After You Reduce the Maximum Characters Per Column (280834)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q280834

SYMPTOMS

In the SQL Server Query Analyzer, if you create a unique value, of type uniqueidentifier, by using the NEWID function, with the Maximum Characters Per Column option set to a value less than 36, the SQL Server Query Analyzer outputs this error message:
[Microsoft][ODBC SQL Server Driver]Numeric value out of range
The error message does not indicate what setting, in particular, is causing the error to occur.

CAUSE

This behavior is by design.

WORKAROUND

Always confirm that the Maximum Characters Per Column option under the Results tab of the Query Analyzer Options dialog box, is set to a value of 36, or higher, when you use a uniqueidentifier datatype.

MORE INFORMATION

Steps to Reproduce the Problem

  1. Open the SQL Server Query Analyzer.
  2. Set the Maximum Characters Per Column option in Query Analyzer to a value less than 36.

    In SQL Server 2000

    To set the Maximum Characters Per Column option:

    In the SQL Server Query Analyzer, from the Tools menu, click Options. In the Options dialog box, click the Results tab and type a numeric value less than 36 in the Maximum Characters Per Column text box.

    In SQL Server 7.0

    To set the Maximum Characters Per Column option:

    In the SQL Server Query Analyzer, from the Query menu, point to Current Connection Options. In the Current Connection Options dialog box, click the Advanced tab and type a numeric value less than 36 in the Maximum Characters Per Column text box.
  3. After you set the Maximum Characters Per Column option, run this code:
    SELECT NEWID()
    This error message occurs:
    [Microsoft][ODBC SQL Server Driver]Numeric value out of range
The following quote is from the UNIQUEIDENTIFIER topic in Microsoft SQL Server Books Online:

"Uniqueidentifier consists of a 16-byte hexadecimal number, indicating a Globally Unique Identifier (GUID), of the form (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f). For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

Setting the option, in Query Analyzer, to specify the 'maximum number of characters' to a value less than 36 - including the (-) dashes - outputs an error message, which is misleading, and does not indicate where exactly the error has happened.

Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbprb KB280834
©2003 Microsoft Corporation. All rights reserved.