Collation conflict error: while creating SP.

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

SQL Server 2000 Forums

SQL Server Development (2000)

Collation conflict error: while creating SP.

Author

Topic

dylanmendes
Starting Member

7 Posts

Posted - 2005-08-27 : 13:34:42

Hello, I have sent a backup of my SQL Server database (let us call this DB_Parent) to a team member. Upon receiving this on his SQL Server, he performs the following steps:
1. He restores the DB_Parent on his SQL Server.
2. He creates a new DB, say DB_child.
3. He runs a DTS package that copies certain tables and all stored procedures from DB_Parent to DB_Child.

THE PROBLEM: One stored procedure does not get created. The creation fails with the "Cannot resolve collation conflict for equal to operation".

Here is a part of the stored proc; The error msg points to a particular line #.. I have highlighted this in red, though i don't know how reliable that information is:

CREATE PROCEDURE usp_FinalizeAccount
@WaterfallItems text,
@WaterfallOrder text,
@AccountPreferences text
AS
DECLARE @hDoc int
DECLARE @iDoc int
DECLARE @jDOC int
insert into ItemMaster(ItemTypeId, ItemName)
select ItemTypeId, ItemType
from ItemTypeMaster where isFixed = 1

--Parse the XML list of UserIDs and insert these into the AccountRights table
EXEC sp_xml_preparedocument @hDoc OUTPUT, @WaterfallItems
INSERT INTO ItemMaster(ItemTypeId, ItemName)
select ItemTypeId, ItemName from OPENXML(@hdoc, '/ROOT/Table', 2)
WITH(ItemTypeID int, ItemName varchar(20))

insert into Element_Categories(ElementID, CategoryID)
select IM.ItemID, WF.ParentCategoryID
From
ItemMaster as IM,
OPENXML (@hDoc, '/ROOT/Table',2)
WITH (ItemName varchar(20), ParentCategoryID int ,ItemType varchar(20)) as WF
Where IM.ItemName = WF.ItemName and WF.ItemType = 'Element'

NOTE: All databases in question are SQL 2000. When I asked my colleague to check, he confirmed that both DB_Parent and DB_Child on his server had the same Collation.

Please help!

Kristen
Test

22859 Posts

Posted - 2005-08-28 : 03:11:46

I expect the collation sequence of the server/database from which the backup was made is different to the new one the DB was restored onto.

What is the collation of these fields?

Where IM.ItemName = WF.ItemName and WF.ItemType = 'Element'

I reckon you will find that they are different

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources