?
Solved

collation conflict error in SQL 2005

Posted on 2008-02-07
2
Medium Priority
?
2,083 Views
Last Modified: 2008-02-07
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

0
Comment
Question by:trojan_uk
2 Comments
 
LVL 25

Accepted Solution

by:
slam69 earned 1000 total points
ID: 20840489
Might be quickest to install a new instance of sql and set the collation of it to match the original
0
 

Author Comment

by:trojan_uk
ID: 20840802
Thanks slam, that's what I have just done and it works fine now
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question