Collation Conflict Error
-
Hi All,
I am trying to create a stored procedure but I am getting a collation conflict error with the following SQL.
SELECT OrderID, MFID, ItemName, QuantityPurchased, BuyerName,
isnull(o.ShippingAddress1, '') + ', ' + isnull(o.ShippingAddress2, '') + ', ' + isnull(o.ShippingAddress3, '') + ', ' + isnull(o.ShippingCity, '') + ', ' + isnull(o.ShippingState, '') + ', ' + isnull(o.ShippingZip, '') AS Address,
BuyerPhoneNumber, PO
FROM Orders as o
INNER JOIN products p on o.SKU = p.idProduct
WHERE PO IN ('po1', 'po2', 'po3')
ORDER BY o.ID
I have had a collation error before which I solved by putting in COLLATE Latin1_General_CI_AS just after the join which was causing the error. I tried it on the SQL above but it hasn't worked. I don't exactly know where to place it in the SQL if at all.
Can anyone help?
Cheers
Reet
-
Can you post the exact error message along with the DDL for the tables involved in your SELECT?
-
....
INNER JOIN products p on o.SKU COLLATE Latin1_General_CI_AS = p.idProduct COLLATE Latin1_General_CI_AS
BTW, don't use char values as ID's. There are int's for your service.
They must have told you about it, but you considered it as boring academic stuff. Not for "real world" developers.
Now enjoy all "advantages" of your ignorance to proper database design.
_____________
Code for TallyGenerator -
Hi Sergiy,
I tried what you suggested above but I get an 'Expression type int is invalid for COLLATE clause'
My ID column is an int. If you are referring to OrderID that needs to ba a datatype of char.
Cheers
Reet
-
Hi,
I have resolved the issue......I put a sub select statement in the WHERE section which I failed to mention...my bad!
Therefore, I needed to place COLLATE Latin1_General_CI_AS in the where clause:
WHERE PO COLLATE Latin1_General_CI_AS IN (SELECT colA FROM......
Thanks guys for your input. I will include the correct code next time.
Reet
-
Oh, no...
Everything is even worse!
NEVER JOIN ON DIFFERENT DATATYPE COLUMNS!
It means implicit conversion and full table scan on every query!
Than, replace IN with INNER JOIN.
Your subquery will effectively create hash table in tempdb and invoke index scan (not seek) on main table because that #table not gonna be indexed.
The way you made it it will work in dev environment, but with any reasonable amount of data you'll need to explain users why they have coffee break everytime they try to get some data from your system.
_____________
Code for TallyGenerator -
Thank you
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply