Post reply

varchar2 field needs quotes - Microsoft Server 2000

  • July 19, 2004 at 2:21 pm

    #162924

    We were running Microsoft Server 7 and we upgraded to 2000.  Before the upgrade, when we ran a query in transact sql we did not need quotes around this type of number:

    select * from [case] where causeno = 17741

    Since the upgrade to 2000 we get an error:

    Syntax error converting the varchar value ']' to a column of data type int.

    Now we have to use extra quotes:

    select * from [case]  where causeno = '17741'

    Is there a setting somewhere that can be set differently to eliminate this irritant.

     

    Thank You

  • July 19, 2004 at 2:37 pm

    #515121

    Which data type do you define for "causeno" on both SQL Server 7.0 and SQL Server 2000?

    Also, Paste the results of "DBCC USEROPTIONS" from both servers here.

     

  • July 19, 2004 at 2:56 pm

    #515123

    VarChar(12) is the data type for the column/field on both servers

    and  SQL Server 2000    DBCC USEROPTIONS (cannot get to server 7):

    Set Option Value

    textsize                    64512

    language                   us_english

    dateformat                mdy

    datefirst                    7

    quoted_identifier         SET

    arithabort                  SET

    ansi_null_dflt_on         SET

    ansi_defaults              SET

    ansi_warnings             SET

    ansi_padding              SET

    ansi_nulls                   SET

    concat_null_yields_null SET

     

    Thank You

     

  • July 20, 2004 at 7:26 am

    #515255

    If the column is of type varchar(12), you should use the quoted version of your query:

       select * from [case]  where causeno = '17741'

    Without the quotes, SQL Server is performing an implicit conversion from type int to type varchar(12). It's best not to rely on implicit conversions. It's also more confusing for others who may have to read the code in the future.

    You might want to check the causeno column for values which are invalid integers. If just one such column exists, you may get a type conversion error when you run the SELECT statement.

     

  • July 20, 2004 at 8:47 am

    #515277

    thank you for the information.  I am curious why do you think in SQL Server 7.0 it allowed the  no quotes for that type of field?

     

    Thanks Again,

    Sincerely,

    Kim Powell

  • July 20, 2004 at 10:08 am

    #515303

    Apparently, the orginal question should have been:

    How do we turn on implicit conversions in/on SQL Server 2000?

     

    Thank You

  • July 20, 2004 at 12:10 pm

    #515320

    Sorry if I wasn't clear. SQL Server 2000 still performs implicit conversions such as your example. However, if the varchar(12) column contains non-integer values, you may get an error when you specify an integer constant in the WHERE clause (WHERE causeno = 17741) because in order to compare values, SQL Server converts the value in the column to int.

    If you properly quote the value (WHERE causeno = '17741'), no such conversion takes place. Strings are compared to strings.

    If the [case] table contains any values that cannot be converted to a 32-bit integer (2147483647...-2147483648), then you'll get an error with the non-quoted example (WHERE causeno = 17741). This includes values that contain character data, or numeric values that are too big or too small.

    What I was saying originally is that I certainly would not rely on implicit conversions. I would code the query the correct way, and that is to compare causeno to strings. If you don't, you're asking for trouble (in my opinion).

    By the way, if causeno is always a number, why isn't it of type int?

    Mike

     

  • July 20, 2004 at 12:21 pm

    #515323

    I think it has an occasional alpha character.  Thank You.

  • July 20, 2004 at 10:19 pm

    #515400

    Someone isn't doing something right on Kim's machine... the following example shows that, although it may not be a good idea, implicit transactions as Kim described should work just fine as witnessed below...

    DECLARE @MyTest VARCHAR(10)

    SET @MyTest = 17210

    SELECT @Mytest

    ----------

    17210

    (1 row(s) affected)

    DBCC USEROPTIONS

    textsize 64512

    language us_english

    dateformat mdy

    datefirst 7

    quoted_identifier SET

    arithabort SET

    ansi_null_dflt_on SET

    ansi_defaults SET

    ansi_warnings SET

    ansi_padding SET

    ansi_nulls SET

    concat_null_yields_null SET

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 21, 2004 at 5:44 am

    #515454

    Kim - run this to see if you get any results:

    SELECT * FROM [case] WHERE caseno LIKE '%]%'

    Jeff - it works until the caseno column contains an value that can't be converted to int. I think Kim's [case] table has one or more rows where caseno = ']'.

    Try this example:

    -- drop table [case]

    go

    create table [case] (causeno varchar(12))

    set nocount on

    insert [case] values ('17741')

    insert [case] values (17742)

    set nocount off

    -- verify that all values inserted ok

    select * from [case]

    -- the following works because all caseno values can be converted to int

    select * from [case] where causeno = 17741

    -- insert non-int value

    set nocount on

    insert [case] values (']')

    set nocount off

    -- the following fails because a non-int caseno value exists

    select * from [case] where causeno = 17741

    Mike

     

  • July 21, 2004 at 9:57 am

    #515485

    SELECT * FROM [case] WHERE caseno LIKE '%]%'

    Ran this with no results found, and now that I think about it this

    I found this field has the - in it as in:

     

    01-0215

    01-0126

    etc

     

    Thank You

  • July 21, 2004 at 8:12 pm

    #515562

    >it works until the caseno column contains an value that can't be converted to int.

    Mike, you are absolutely correct.  I was aiming at the original question and I guess I missed.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply