Microsoft KB Archive/280834

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

Article ID: 280834

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



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.



Steps to Reproduce the Problem
 Open the SQL Server Query Analyzer. 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.  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:

&quot;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.

Additional query words: Newid, Query Analyzer, Options

Keywords: kbprb KB280834

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.