Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    London
    Posts
    5

    Unanswered: select statement works in v7, not in 2000

    We have come across a problem when upgrading to SQL Server 2000 from version 7. The following (admittedly dodgy) SQL works OK in version 7 on varchar column 'telephone'.

    SELECT count(*)
    FROM dbo.broker
    WHERE (telephone <> 0)

    but in SQL Server 2000, it gives the error:
    Server: Msg 245, Level 16, State 1, Line 1
    Syntax error converting the varchar value '020 408 7189' to a column of data type int.

    It works OK like this, as one would expect:
    SELECT count(*)
    FROM dbo.broker
    WHERE (telephone <> '0')

    My advice would be - change the SQL! But there is a lot of it and the PTB would like to know whether there is a server setting that controls this behaviour, or any other workaround.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    It is known issue (or feature) of 2000. You have to use cast or convert...

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by snail
    It is known issue (or feature) of 2000. You have to use cast or convert...
    Issue?

    To understand that you're working with the appopriate datatypes

    I think the issue was in 7.....

    How many lines of code are there?

    Why not script the whole database...load it to a table, and use REPLACE

    then bcp it out

    Even if you don't do replace, at least it will be good for analysis

    CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 varchar(8000))
    GO
    BULK INSERT...yada yada yada...

    SELECT * FROM myTable99 WHERE Col2 LIKE '%Telephone%'

    MOO

    Good Luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Brett Kaiser

    Issue?

    Converting issue from 7

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •