Professional Web Applications Themes

MS SQL 2005 and ntext - Coldfusion Database Access

I have a simple SELECT statement: SELECT RestaurantID, Status, RestaurantName, City, PostalCode, EmailAddress, URL FROM tblRestaurants WHERE URL <> '' It works with an Access mdb but not with MS SQL 2005. I get an error "varchar and ntext data types are incompatible with the <> (NEQ) operator". Of the above, the only ntext colum is URL; when I remove it, the query works fine, even though other colums are type varchar. Any ideas?...

  1. #1

    Default MS SQL 2005 and ntext

    I have a simple SELECT statement:
    SELECT RestaurantID, Status, RestaurantName, City, PostalCode, EmailAddress,
    URL
    FROM tblRestaurants
    WHERE URL <> ''
    It works with an Access mdb but not with MS SQL 2005. I get an error "varchar
    and ntext data types are incompatible with the <> (NEQ) operator". Of the
    above, the only ntext colum is URL; when I remove it, the query works fine,
    even though other colums are type varchar. Any ideas?

    cheftimo Guest

  2. #2

    Default Re: MS SQL 2005 and ntext

    cheftimo wrote:
    > I have a simple SELECT statement:
    > SELECT RestaurantID, Status, RestaurantName, City, PostalCode, EmailAddress,
    > URL
    > FROM tblRestaurants
    > WHERE URL <> ''
    can the URL column be NULL & is that the logic you're after? if so, try

    WHERE URL IS NOT NULL
    PaulH *ACE* Guest

  3. #3

    Default Re: MS SQL 2005 and ntext

    The only valid comparator for text and ntext fields is the LIKE function. These fields can be up to 2GB in length, so you could imagine the performance hit for a complete comparison of values.
    philh Guest

  4. #4

    Default Re: MS SQL 2005 and ntext

    philh wrote:
    > The only valid comparator for text and ntext fields is the LIKE function.
    > These fields can be up to 2GB in length, so you could imagine the performance
    > hit for a complete comparison of values.
    IS NULL/IS NOT NULL works on ntext/text ok. but i guess that raises the question
    of why use ntext for a column holding URLs? i would guess nvarchar(1000) would
    be more than enough.
    PaulH *ACE* Guest

Similar Threads

  1. Replies: 0
    Last Post: March 22nd, 05:51 AM
  2. The FreeBSD Diary: 2005-02-06 - 2005-02-26
    By Dan Langille in forum FreeBSD
    Replies: 0
    Last Post: February 27th, 08:10 AM
  3. The FreeBSD Diary: 2005-01-30 - 2005-02-19
    By Dan Langille in forum FreeBSD
    Replies: 0
    Last Post: February 20th, 08:10 AM
  4. ntext formatting
    By Darren Woodbrey in forum ASP Database
    Replies: 1
    Last Post: April 22nd, 03:37 AM
  5. nText
    By Rocky in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 12:56 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139