collation conflict error in SQL 2005

Hi,

I have just done a Backup/Restore from SQL 2000 and 2005, mostly it seems ok . I set up the 2005 database using the collation as default, then did a restore checking the overwrite option. Both databases are showing as "Latin1_General_CI_AS"

When I execute the SP in 2005 I get the following error:

Msg 468, Level 16, State 9, Procedure wbEmplCostMgrList, Line 57
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

I have pasted the SP below and marked out line 57

Many thanks


USE [WEBBILL]
GO
/****** Object:  StoredProcedure [dbo].[wbEmplCostMgrList]    Script Date: 02/07/2008 13:06:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[wbEmplCostMgrList]
 
(
 	@COMPID int,	
	@COSTID int,
	@MONTH varchar(2),
	@YEAR varchar(4),
	@INVNUMBER varchar(6)
 
)
 
AS
SET NOCOUNT ON
 
CREATE TABLE #COSTCHARGETOTAL (
CostID int,
EmpID int,
FirstName varchar(15),
LastName varchar(20),
TelNumber varchar(15),
RENTALPLUS float,
RENTALMINUS float,
CHARGEOUT float,
CHARGEIN float,
CHARGEDATA float,
CHARGEMESSAGE float
)
 
 
INSERT INTO #COSTCHARGETOTAL
SELECT
Emp.COSTID, 
Emp.EMPID,
Emp.FIRSTNAME, 
Emp.LASTNAME, 
TELNUMBER,
Sum(Case When Rental > 0 Then Rental else 0 end) AS RENTALPLUS,
Sum(Case When Rental < 0 Then Rental else 0 end) AS RENTALMINUS,
0,
0,
0,
0
FROM ((wbRentalCharge A LEFT JOIN wbMCD B ON A.MobDevID = B.MobDevID)
		       		LEFT  JOIN wbAssetTrack C ON C.AssID = B.AssID)
		       			INNER JOIN wbEmpl Emp ON emp.EmpID = C.EmpID             		
 
WHERE  INVNUMBER = @INVNUMBER
   AND C.EMPID IN (
   SELECT EMPID 
   FROM wbEmpl <<===================== LINE 57
   WHERE COMPID= @COMPID
  AND COSTID =@COSTID
  AND VISIBLE <> 0)
GROUP BY Emp.COSTID, Emp.FIRSTNAME, Emp.LASTNAME, TELNUMBER,B.MobDevID,Emp.EMPID
 
 
UPDATE A
SET CHARGEOUT = COALESCE( ( SELECT Sum(Case When Charge > 0 Then Charge else 0 end)
          FROM wbVoiceOut B
          LEFT JOIN wbMCD C
           ON B.MobDevID = C.MobDevID
          LEFT  JOIN wbAssetTrack D
           ON D.AssID = C.AssID
          WHERE C.TelNumber = A.Telnumber  
            AND INVNUMBER = @INVNUMBER AND B.CostID = @CostID ) , 0)
FROM #COSTCHARGETOTAL A 
 
 
UPDATE A
SET CHARGEIN = COALESCE( ( SELECT Sum(Case When Call_Charge > 0 Then Call_Charge else 0 end)
          FROM wbVoiceIn B
          LEFT JOIN wbMCD C
           ON B.MobDevID = C.MobDevID
          LEFT  JOIN wbAssetTrack D
           ON D.AssID = C.AssID
          WHERE C.TelNumber = A.Telnumber  
            AND INVNUMBER = @INVNUMBER  AND B.CostID = @CostID ) , 0)
FROM #COSTCHARGETOTAL A 
 
UPDATE A
SET CHARGEDATA = COALESCE( ( SELECT Sum(Case When Charge > 0 Then Charge else 0 end)
          FROM wbData B
          LEFT JOIN wbMCD C
           ON B.MobDevID = C.MobDevID
          LEFT  JOIN wbAssetTrack D
           ON D.AssID = C.AssID
          WHERE C.TelNumber = A.Telnumber  
            AND INVNUMBER = @INVNUMBER AND B.CostID = @CostID ) , 0)
FROM #COSTCHARGETOTAL A 
 
UPDATE A
SET CHARGEMESSAGE = COALESCE( ( SELECT Sum(Case When Charge > 0 Then Charge else 0 end)
          FROM wbMessage B
          LEFT JOIN wbMCD C
           ON B.MobDevID = C.MobDevID
          LEFT  JOIN wbAssetTrack D
           ON D.AssID = C.AssID
          WHERE C.TelNumber = A.Telnumber  
            AND INVNUMBER = @INVNUMBER  AND B.CostID = @CostID) , 0)
FROM #COSTCHARGETOTAL A
 
 
 
SELECT * FROM #COSTCHARGETOTAL

Open in new window

trojan_ukAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slam69Commented:
Might be quickest to install a new instance of sql and set the collation of it to match the original
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trojan_ukAuthor Commented:
Thanks slam, that's what I have just done and it works fine now
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.