SQL Server Performance Forum – Threads Archive

Error – Incorrect syntax near ‘collate’.

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

---

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.

---

What is the error message? —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

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.

---

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

---

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

---

Frank, Not sure what your point is?

---

Mine is sql 2000

---

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

---

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.

---

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.

---

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=’‘ />]

---

… 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).

---

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=’‘ />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,

---

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’.

---

<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 />

---

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.

---

Yes, that’s the only TSQL statement

---

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.

---

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

---

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.

---

]]>