SQL Server Performance

Error - Incorrect syntax near 'collate'.

Discussion in 'General DBA Questions' started by danan_xu, Apr 12, 2005.

  1. danan_xu New Member

    Hi, guys:

    I have two tables

    a use collation - SQL_Latin1_General_CP1_CI_AS
    b use collation - Latin1_General_CI_AS

    when I run the following statement:

    select b.*
    from b
    join a
    on a.a1 =b.b1 collate SQL_Latin1_General_CP1_CI_AS

    I got such error.

    the version of SQL is SQL 2000 with sp3.


    who can help?

    thanks
  2. satya Moderator

    Collate command inside a table creation will work only from SQL 2000.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. FrankKalis Moderator

  4. Adriaan New Member

    The incorrect syntax is in the part of the query before your collate clause. T-SQL supports only INNER JOIN, LEFT JOIN and RIGHT JOIN (and FULL JOIN and CROSS JOIN, if you should ever need them).

    Collation works at the column level, so you must check the collation of the a.a1 and b.b1 columns (open the design of the table, highlight the column and check the properties).

    You should put the COLLATE clause immediately after the field that has the different collation. If your b.b1 column has a different collation than your a.a1 column, and a.a1 has the default collation for the current database, then you can also use COLLATE DATABASE_DEFAULT after b.b1 in the ON clause.
  5. FrankKalis Moderator

    Oops, forget it. Found that you've posted this in the thread subject...
    Okay, I'll try then another stupid question. The column you join on are character columns?

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  6. FrankKalis Moderator

    Adriaan, but this


    SELECT
    a.*
    FROM
    authors a
    INNER JOIN
    titleauthor ta
    ON
    ta.au_id = a.au_id collate SQL_Latin1_General_CP1_CI_AS

    works for me.

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  7. Adriaan New Member

    Frank,

    Not sure what your point is?
  8. danan_xu New Member

    Mine is sql 2000

    quote:Originally posted by satya

    Collate command inside a table creation will work only from SQL 2000.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. danan_xu New Member

    You are right, I just put the collate after the column that needs to be update


    quote:Originally posted by Adriaan

    The incorrect syntax is in the part of the query before your collate clause. T-SQL supports only INNER JOIN, LEFT JOIN and RIGHT JOIN (and FULL JOIN and CROSS JOIN, if you should ever need them).

    Collation works at the column level, so you must check the collation of the a.a1 and b.b1 columns (open the design of the table, highlight the column and check the properties).

    You should put the COLLATE clause immediately after the field that has the different collation. If your b.b1 column has a different collation than your a.a1 column, and a.a1 has the default collation for the current database, then you can also use COLLATE DATABASE_DEFAULT after b.b1 in the ON clause.
  10. danan_xu New Member

    Thanks Frank.

    It should work for I checked online book and find it just follows the sql gramme

    I am quite headached with it, which cost me half a day but still couldn't fix it.

    Maybe it is related to some system level setting. I will try it in other servers.


    quote:Originally posted by FrankKalis

    Adriaan, but this


    SELECT
    a.*
    FROM
    authors a
    INNER JOIN
    titleauthor ta
    ON
    ta.au_id = a.au_id collate SQL_Latin1_General_CP1_CI_AS

    works for me.

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de

  11. Adriaan New Member

    The COLLATE clause here is used only for comparing the data in the two columns, not for updating.

    The syntax error was that you used the word JOIN without a type specification, which is not allowed.
  12. Adriaan New Member

    I need to get my syntax straight[<img src='/community/emoticons/emotion-6.gif' alt=':(' />] - you can indeed use JOIN with no type specification. Sorry for the confusion.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  13. Adriaan New Member

    ... but as I said above, the error occurs BEFORE the word COLLATE - the phrase "Incorrect syntax near XXX" means that XXX is the first word that makes sense to SQL Server after it has lost track of what you're doing.

    Please post the complete SELECT, because we're probably looking for a typing error (a comma instead of a dot, small things like that).
  14. danan_xu New Member

    thanks. Adriaan.<br /><br />The following is the schema:<br />table 1<br />name:Incomingtrigges<br />collation: Latin1_General_CI_AS<br /><br />col_nametypelengthallow null<br />iActionIDint40<br />vcDestNovarchar200<br />dtStartDatedatetime80<br />dtEndDatedatetime80<br />vcActionvarchar5000<br /><br />table 2<br />Name<img src='/community/emoticons/emotion-4.gif' alt=':p' />honeNumberList<br />collation: SQL_Latin1_General_CP1_CI_AS<br /><br />col_nametypelengthallow null<br />NumberReferenceint40<br />PhoneNumbernvarchar501<br />ServiceTypeint41<br />Tariff int40<br />Golden bit11<br />Availablebit11<br />Commentsnvarchar1501<br />RouteReferenceint40<br />JobNumbernvarchar501<br />TerminationNumberbit11<br />Overflowbit11<br />Systemint40<br />Channelint40<br />PremiumSmsbit11<br />Avalanchebit11<br /><br /><br />my query:<br /><br />select i.vcdestno<br />from incomingtriggers i,phonenumberlist p<br />where i.vcdestno=p.phonenumber collate Latin1_General_CI_AS<br /><br /><br />Thank everyone for your efforts and help.<br /><br />cheers,
  15. danan_xu New Member

    besides, the two columns involved in the queery have different length and type( varchar and nvarchar), but in my another query I have used convert function to format it. So the data type shouldn't be proble.

    It's should be the simplest query use 'Collate', but wondering why it still reported the error 'Incorrect syntax near 'collate'.
  16. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />I need to get my syntax straight[<img src='/community/emoticons/emotion-6.gif' alt=':(' />] - you can indeed use JOIN with no type specification. Sorry for the confusion.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes, you can and that's one of the pitfalls in SQL Server. <br /><pre id="code"><font face="courier" size="2" id="code"><br />USE PUBS<br />SELECT<br /> r.royalty<br /> , t.title<br /> , t.type<br /> , t.price<br />FROM<br /> roysched r<br />INNER JOIN<br /> titles t<br />ON<br /> r.title_id = t.title_id<br />ORDER BY<br /> r.royalty<br /></font id="code"></pre id="code"><br />In this statement one does not need to specify INNER. The query just works fine without. However, and now comes the rub. When you specify a JOIN hint, you *need* to be explicite what JOIN you want. <br /><pre id="code"><font face="courier" size="2" id="code"><br />USE PUBS<br />SELECT<br /> r.royalty<br /> , t.title<br /> , t.type<br /> , t.price<br />FROM<br /> roysched r<br />MERGE JOIN<br /> titles t<br />ON<br /> r.title_id = t.title_id<br />ORDER BY<br /> r.royalty<br /><br />Server: Msg 170, Level 15, State 1, Line 9<br />Line 9: Incorrect syntax near 'MERGE'.<br /></font id="code"></pre id="code"><br />I guess, sometimes it just doesn't pay to be a lazycoder. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />Collations are an area I yet need to discover. But the different length of the columns isn't the problem. Different datatypes? Maybe? Sorry, but apart from this, I can't be of much help here.<br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  17. Adriaan New Member

    Well, I tried the following script on two servers with different collations, but couldn't get a syntax error if I tried to.

    create table a (col varchar(20) COLLATE Latin1_General_CI_AS)
    create table b (col nvarchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS)

    select * from a, b where a.col = b.col collate Latin1_General_CI_AS

    drop table a
    drop table b


    Is the query the only statement in your batch? If there's something before the SELECT, the error may well be in that part of the script.
  18. danan_xu New Member

    Yes, that's the only TSQL statement


    quote:Originally posted by Adriaan

    Well, I tried the following script on two servers with different collations, but couldn't get a syntax error if I tried to.

    create table a (col varchar(20) COLLATE Latin1_General_CI_AS)
    create table b (col nvarchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS)

    select * from a, b where a.col = b.col collate Latin1_General_CI_AS

    drop table a
    drop table b


    Is the query the only statement in your batch? If there's something before the SELECT, the error may well be in that part of the script.
  19. danan_xu New Member

    hi, guys.

    Definitely the grammer is right and I have done simple test on the other servers by using similar code but different tables, which worked well.

    So there should be some other setting with server, I will try to find out.

    If you have any idea, appreciate your help.

    Thanks again for your efforts.





  20. Adriaan New Member

    Is this when using QA, or through a client application? If the latter, check the connection's properties/connect string for special parameters.
  21. danan_xu New Member

    Adriaan:

    I used is throuhg QA. But I though it should be related to server side setting.

    because when I did the test, I used QA as well but on difference db.

    anyway, thanks for your help.

Share This Page