problems with collate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problems with collate

Author

Topic

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-11-21 : 15:25:32

My following procedure has the following error:
Msg 156, Level 15, State 1, Procedure list_exported_orders3, Line 5
Incorrect syntax near the keyword 'COLLATE'.

ALTER proc [dbo].[list_exported_orders3]
AS
BEGIN

DELETE FROM ups_shipping.dbo.ats_exported_orders
WHERE SalesOrder IN
(SELECT SalesOrder
FROM companyT.dbo.SorMaster
WHERE OrderStatus =9);

END
I can't change the database properties anymore. I know that somewhere I need to include this statement. COLLATE Latin1_General_BIN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-21 : 15:40:07

quote:
Originally posted by Patyk

My following procedure has the following error:
Msg 156, Level 15, State 1, Procedure list_exported_orders3, Line 5
Incorrect syntax near the keyword 'COLLATE'.

ALTER proc [dbo].[list_exported_orders3]
AS
BEGIN

DELETE FROM ups_shipping.dbo.ats_exported_orders
WHERE SalesOrder IN
(SELECT SalesOrder
FROM companyT.dbo.SorMaster
WHERE OrderStatus =9);

END
I can't change the database properties anymore. I know that somewhere I need to include this statement. COLLATE Latin1_General_BIN

I don't see COLLATE in your stored procedure. Show us the stored procedure code that is throwing an error.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-21 : 15:40:07

I don't see the word COLLATE anywhere in the proc definition

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-11-21 : 18:35:53

this is the procedure. The word collate is a part of error not the procedure.
USE [ups_shipping]
GO
/****** Object: StoredProcedure [dbo].[list_exported_orders3] Script Date: 11/21/2014 15:34:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[list_exported_orders3]
AS
BEGIN

DELETE FROM ups_shipping.dbo.ats_exported_orders_raw
WHERE SalesOrder IN
(SELECT SalesOrder
FROM companyT.dbo.SorMaster
WHERE OrderStatus =9);

END

This is the error:

Msg 468, Level 16, State 9, Procedure list_exported_orders3, Line 5
Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-21 : 18:41:17

quote:
Originally posted by Patyk

this is the procedure. The word collate is a part of error not the procedure.
USE [ups_shipping]
GO
/****** Object: StoredProcedure [dbo].[list_exported_orders3] Script Date: 11/21/2014 15:34:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[list_exported_orders3]
AS
BEGIN

DELETE FROM ups_shipping.dbo.ats_exported_orders_raw
WHERE SalesOrder IN
(SELECT SalesOrder
FROM companyT.dbo.SorMaster
WHERE OrderStatus =9);

END

This is the error:

Msg 468, Level 16, State 9, Procedure list_exported_orders3, Line 5
Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

That's much different than your original post. Your original post makes it sound like you tried to fix it by adding COLLATE but couldn't get the right syntax in there, hence the error. But the code you posted didn't reflect that.

What is the data type of OrderStatus in companyT.dbo.SorMaster? Why are you using IN instead of JOIN?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-11-21 : 18:43:14

Order Status char(1) , How would I use Join?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-21 : 18:47:25

DELETE aeor
FROM ups_shipping.dbo.ats_exported_orders_raw aeor
JOIN companyT.dbo.SorMaster sm
ON aeor.SalesOrder = sm.SalesOrder
WHERE sm.OrderStatus = '9';

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-21 : 18:47:53

The key is to treat OrderStatus as a char and not an int. Put single quotes around it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-11-21 : 18:54:23

Same error. The two tables come from different databases. One under the properties has Latin1_General_BIN and the other has SQL_Latin1_General_CP1_CI_AS. I think this is why the error.

Previously I had to create a view also from the above database. I had to add Collate to my view have a look.

SELECT RTRIM(REPLACE(companyB.dbo.SorMaster.SalesOrder, ',', ' ')) AS Pickslip, 'AGPROFFESS' AS Docket, RTRIM(REPLACE(companyB.dbo.SorMaster.Customer, ',', ' '))
AS Account, NULL AS Attention, RTRIM(REPLACE(companyB.dbo.SorMaster.CustomerName, ',', ' ')) AS Name,
RTRIM(REPLACE(companyB.dbo.SorMaster.ShipAddress1, ',', ' ')) AS Address, RTRIM(REPLACE(companyB.dbo.SorMaster.ShipAddress5, ',', ' ')) AS Address2,
RTRIM(REPLACE(companyB.dbo.SorMaster.ShipAddress2, ',', ' ')) AS City, RTRIM(REPLACE(companyB.dbo.SorMaster.ShipAddress3, ',', ' ')) AS Province,
RTRIM(REPLACE(companyB.dbo.SorMaster.ShipAddress4, ',', ' ')) AS Country, '(companyB.dbo.SorMaster.ShipPostalCode, ,, ))' AS Zip,
RTRIM(REPLACE(companyB.dbo.SorMaster.SpecialInstrs, ',', ' ')) AS Phone, RTRIM(REPLACE(companyB.dbo.SorMaster.Email, ',', ' ')) AS Email,
RTRIM(REPLACE(companyB.dbo.SorMaster.ShippingInstrs, ',', ' ')) AS Instructions, RTRIM(REPLACE(companyB.dbo.SorMaster.CustomerPoNumber, ',', ' ')) AS PO,
RTRIM(REPLACE(companyB.dbo.SorMaster.SalesOrder, ',', ' ')) AS Ref
FROM companyB.dbo.SorMaster LEFT OUTER JOIN
dbo.ats_exported_orders_raw ON companyB.dbo.SorMaster.SalesOrder = dbo.ats_exported_orders_raw.SalesOrder COLLATE Latin1_General_BIN
WHERE (companyB.dbo.SorMaster.OrderStatus = '4') AND (dbo.ats_exported_orders_raw.SalesOrder IS NULL) AND (companyB.dbo.SorMaster.Branch = 'AB' OR
companyB.dbo.SorMaster.Branch = 'SM' OR
companyB.dbo.SorMaster.Branch = 'BC' OR
companyB.dbo.SorMaster.Branch = 'IN')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-21 : 19:05:41

Add the COLLATE after the tables/aliases.

FROM .... COLLATE ....

OR:

JOIN .... COLLATE ....

Just depends where this stored procedure is located and which table is not in that database.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-11-21 : 19:12:37

What I am getting is Msg 156, Level 15, State 1, Procedure list_exported_orders3, Line 7
Incorrect syntax near the keyword 'COLLATE'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-21 : 19:13:34

quote:
Originally posted by Patyk

What I am getting is Msg 156, Level 15, State 1, Procedure list_exported_orders3, Line 7
Incorrect syntax near the keyword 'COLLATE'.

Show the exact code that is throwing that error.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-11-21 : 19:16:34

USE [ups_shipping]
GO
/****** Object: StoredProcedure [dbo].[list_exported_orders3] Script Date: 11/21/2014 16:08:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[list_exported_orders3]
AS
BEGIN

DELETE aeor
FROM ups_shipping.dbo.ats_exported_orders_raw aeor
JOIN COLLATE companyT.dbo.SorMaster sm
ON aeor.SalesOrder = sm.SalesOrder
WHERE sm.OrderStatus = 9;
end

I have used your latest code.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-21 : 19:19:11

I don't know which table needs it, so you might need to move it:

DELETE aeor
FROM ups_shipping.dbo.ats_exported_orders_raw aeor COLLATE Latin1_General_BIN
JOIN companyT.dbo.SorMaster sm
ON aeor.SalesOrder = sm.SalesOrder
WHERE sm.OrderStatus = 9;
end

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-11-21 : 19:21:37

I put this your way or this way same error:
DELETE aeor
FROM ups_shipping.dbo.ats_exported_orders_raw aeor
JOIN companyT.dbo.SorMaster sm COLLATE Latin1_General_BIN
ON aeor.SalesOrder = sm.SalesOrder
WHERE sm.OrderStatus = 9;
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-21 : 19:23:50

Try it in the WHERE clause since that's where the error is happening:
DELETE aeor
FROM ups_shipping.dbo.ats_exported_orders_raw aeor
JOIN companyT.dbo.SorMaster sm
ON aeor.SalesOrder = sm.SalesOrder
WHERE sm.OrderStatus = '9' COLLATE Latin1_General_BIN;

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-11-21 : 19:40:14

still the same error

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-21 : 19:49:26

What compatibility level are both databases? Are either of them set to 80?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-21 : 19:50:31

Actually try it here:

DELETE aeor
FROM ups_shipping.dbo.ats_exported_orders_raw aeor
JOIN companyT.dbo.SorMaster sm
ON aeor.SalesOrder = sm.SalesOrder COLLATE Latin1_General_BIN
WHERE sm.OrderStatus = '9';

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources