Professional Web Applications Themes

Collation problem joining a table and view........ - Microsoft SQL / MS SQL Server

Our database has the SQL Server default collation, but all char and varchar columns have a case-sensitive collation. I created a view which has a derived column and when I try to join this derived column with a table column, I get the following error: Server: Msg 446, Level 16, State 9, Line 1 Cannot resolve collation conflict for equal to operation. I'm assuming the derived column is taking on the database's default collation. Does anyone know of a work- around for this problem? Thanks in advance, Paul...

  1. #1

    Default Collation problem joining a table and view........

    Our database has the SQL Server default collation, but all
    char and varchar columns have a case-sensitive collation.
    I created a view which has a derived column and when I try
    to join this derived column with a table column, I get the
    following error:

    Server: Msg 446, Level 16, State 9, Line 1
    Cannot resolve collation conflict for equal to operation.

    I'm assuming the derived column is taking on the
    database's default collation. Does anyone know of a work-
    around for this problem?

    Thanks in advance,
    Paul

    Paul Guest

  2. #2

    Default Re: Collation problem joining a table and view........

    Can you set the derived column to COLLATE <insert case-sensitive collation
    here> ?



    "Paul" <com> wrote in message
    news:003601c355f6$913d1100$gbl... 


    Aaron Guest

  3. #3

    Default Re: Collation problem joining a table and view........

    No, as far as I know, you can't set the collation in a view.

    Paul

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Paul Guest

  4. #4

    Default Re: Collation problem joining a table and view........

    So collate the other way, on the other side of the join??


    "Paul Armitage" <com> wrote in message
    news:#c4$phx.gbl... 


    Aaron Guest

  5. #5

    Default Re: Collation problem joining a table and view........

    Yes you can use COLLATE in a view. However, a computed column doesn't
    inherit the database collation, it inherits the collation of the
    char/varchar column that it was based on. Example:

    CREATE DATABASE testdb COLLATE Latin1_General_CI_AS
    GO
    USE testdb
    GO
    CREATE TABLE t1 (c1 int, c2 varchar (10) COLLATE Latin1_General_CS_AS)
    INSERT INTO t1 VALUES (1, 'abc')
    INSERT INTO t1 VALUES (2, 'ABC')
    GO
    CREATE VIEW v1 AS SELECT c1, UPPER (c2) AS c2 FROM t1
    GO
    SELECT * FROM t1
    INNER JOIN v1 ON t1.c2 = v1.c2

    If this doesn't represent what you are trying to, it would help if you
    could post a script that contains the CREATE statements for the relevant
    objects so we can see the problem.

    HTH,
    Bart
    ------------
    Please reply to the newsgroup only - thanks.

    This posting is provided "AS IS" with no warranties, and confers no rights.





    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------
    From: Paul Armitage <com>
    References: <003601c355f6$913d1100$gbl>
    X-Newsreader: AspNNTP 1.50 (ActionJackson.com)
    Subject: Re: Collation problem joining a table and view........
    Mime-Version: 1.0
    Content-Type: text/plain; cht="us-ascii"
    Content-Transfer-Encoding: 7bit
    Message-ID: <#c4$phx.gbl>
    Newsgroups: microsoft.public.sqlserver.programming
    Date: Tue, 29 Jul 2003 12:13:10 -0700
    NNTP-Posting-Host: actionjackson133.dsl.frii.net 216.17.147.133
    Lines: 1
    Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
    Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:377490
    X-Tomcat-NG: microsoft.public.sqlserver.programming

    No, as far as I know, you can't set the collation in a view.

    Paul

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!





    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------
    Reply-To: "Aaron Bertrand - MVP" <com>
    From: "Aaron Bertrand - MVP" <com>
    References: <003601c355f6$913d1100$gbl>
    Subject: Re: Collation problem joining a table and view........
    Date: Tue, 29 Jul 2003 13:30:50 -0400
    Lines: 25
    Organization: aspfaq.com
    X-Priority: 3
    X-MSMail-Priority: Normal
    X-Newsreader: Microsoft Outlook Express 6.00.3790.0
    X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
    Message-ID: <phx.gbl>
    Newsgroups: microsoft.public.sqlserver.programming
    NNTP-Posting-Host: 12.38.121.251
    Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
    Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:377435
    X-Tomcat-NG: microsoft.public.sqlserver.programming

    Can you set the derived column to COLLATE <insert case-sensitive collation
    here> ?




    ----------------------------------------------------------------------------
    ----------------------------------------------------------------------
    Content-Class: urn:content-classes:message
    From: "Paul" <com>
    Sender: "Paul" <com>
    Subject: Collation problem joining a table and view........
    Date: Tue, 29 Jul 2003 10:26:38 -0700
    Lines: 16
    Message-ID: <003601c355f6$913d1100$gbl>
    MIME-Version: 1.0
    Content-Type: text/plain;
    cht="iso-8859-1"
    Content-Transfer-Encoding: 7bit
    X-Newsreader: Microsoft CDO for Windows 2000
    X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    Thread-Index: AcNV9pE9YpBTfp8aRBWW/d6VImeYaA==
    Newsgroups: microsoft.public.sqlserver.programming
    Path: cpmsftngxa06.phx.gbl
    Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:377430
    NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
    X-Tomcat-NG: microsoft.public.sqlserver.programming

    Our database has the SQL Server default collation, but all
    char and varchar columns have a case-sensitive collation.
    I created a view which has a derived column and when I try
    to join this derived column with a table column, I get the
    following error:

    Server: Msg 446, Level 16, State 9, Line 1
    Cannot resolve collation conflict for equal to operation.

    I'm assuming the derived column is taking on the
    database's default collation. Does anyone know of a work-
    around for this problem?

    Thanks in advance,
    Paul





    Bart Guest

  6. #6

    Default Re: Collation problem joining a table and view........

    Using your example, I've added a column with a constant to
    the view. It takes on the collation of the database. I
    really don't want to change the collation of the table
    column (for sorting purposes) and I want to keep the
    default collation of the database.

    CREATE DATABASE testdb COLLATE Latin1_General_CI_AS
    GO
    USE testdb
    GO
    CREATE TABLE t1 (c1 int, c2 varchar (10) COLLATE
    Latin1_General_CS_AS)
    INSERT INTO t1 VALUES (1, 'abc')
    INSERT INTO t1 VALUES (2, 'ABC')
    GO
    CREATE VIEW v1 AS SELECT c1, UPPER (c2) AS c2, 'TEST'
    as C3 FROM t1
    GO
    SELECT * FROM t1
    INNER JOIN v1 ON t1.c2 = v1.c3

    Thanks,
    Paul 
    column doesn't 
    of the 
    Latin1_General_CS_AS) 
    t1 
    would help if you 
    for the relevant 
    confers no rights. 
    ------------------ 
    ------------ 
    view........ 
    216.17.147.133 
    TK2MSFTNGP10.phx.gbl 
    microsoft.public.sqlserver.programming:377490 
    view. 
    *** 
    ------------------ 
    ------------ 
    view........ 
    TK2MSFTNGP10.phx.gbl 
    microsoft.public.sqlserver.programming:377435 
    sensitive collation 
    ------------------ 
    ------------ 
    view........ 
    microsoft.public.sqlserver.programming:377430 
    all 
    collation. 
    try 
    the 
    Paul Guest

Similar Threads

  1. Joining 2 columns from the same table
    By Nicos in forum MySQL
    Replies: 7
    Last Post: September 22nd, 08:17 AM
  2. Large table join using "text" as the joining key
    By howachen@gmail.com in forum MySQL
    Replies: 4
    Last Post: September 5th, 03:08 PM
  3. queryNew() joining to physical table
    By DannoParker in forum Coldfusion Database Access
    Replies: 2
    Last Post: September 7th, 10:41 PM
  4. change table to view problem
    By Sim Zacks in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: February 1st, 09:13 AM
  5. Pivot Table View Problem
    By Milan in forum Microsoft Access
    Replies: 1
    Last Post: July 28th, 03:32 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