September 13, 2013 at 4:15 am
Hi Guys,
Got the following sp that I created a job to run once a day in Hosted server and the collate is Latin1_General_CI_AS and I have DR server that has collate SQL_Latin1_General_CP1_CI_AS and I'm creating the same job on my DR server but I come the following error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. [SQLSTATE 42000] (Error 468). The step failed.
here is the Sp..please assist it's urgent the job has been failing over a week now.
ALTER PROCEDURE [dbo].[RPT_MTX_AllUnitsReportDaily]
AS
--REMOVE OLD DATA
DELETE FROM dbo.AllUnitsReportDaily;
--==================================Get Box_no, MSISDN from Navision==================================================
--Pulls MSISDN info from DynamicsNavdatabase
SELECT DISTINCT
CAST(REPLACE(si2.[Serial No_],'AC1','BX1') AS VARCHAR(15)) AS Box_no,
si.[Serial No_] AS SIM,
si.[MSISDN Serial No_] AS MSISDN
INTO #NavisionUnitSerials -- drop table #NavisionUnitSerials
FROM DynamicsNavdatabase.dbo.[MIX TELEMATICS$Serial No_ Information] si
LEFT JOIN DynamicsNavdatabase.dbo.[MIX TELEMATICS$Serial No_ Information] si2
ON si2.[SIM Serial No_ (Embeded)] = si.[Serial No_]
WHERE
si.[Item No_] = '1000851'
AND si.[MSISDN Serial No_] IS NOT NULL
AND si.[MSISDN Serial No_] <> ''
AND si2.[Serial No_] <> ''
AND si2.[Serial No_] IS NOT NULL;
CREATE NONCLUSTERED INDEX IDX_NAV_BOX ON #NavisionUnitSerials ( Box_no );
--=============================Get last passed test date of RSLead & MiXControl=========================================
SELECT
unitid AS [Box_No],
createdate AS [CreateDate], 1 as tab
INTO #LastPassedTestDate_Init -- drop table #LastPassedTestDate_Init
FROM RSLead.dbo.unittesthead H WITH (NOLOCK)
WHERE (testrefcode IS NOT NULL
OR testrefcode <> '') --IF HAS REF THEN TEST PASSED
AND H.unittestheadno IN (SELECT MAX(td2.unittestheadno)
FROM RSLead.dbo.unittesthead td2 WITH (NOLOCK)
WHERE h.unitid = td2.unitid COLLATE SQL_Latin1_General_CP1_CI_AS
GROUP BY td2.unitid)
UNION ALL
SELECT
[Box_No],
[CreateDate], 2
FROM [MixControl].[dbo].[UnitTestResultHead] H WITH (NOLOCK)
WHERE TestStatus = 3
AND TestReference IS NOT NULL
AND H.[UnitTestResultHeadID] IN (SELECT MAX(td2.[UnitTestResultHeadID])
FROM [MixControl].[dbo].[UnitTestResultHead] td2 WITH (NOLOCK)
WHERE h.[Box_No] = td2.[Box_No] COLLATE SQL_Latin1_General_CP1_CI_AS
GROUP BY td2.[Box_No]);
CREATE CLUSTERED INDEX IDX_LastPassedTestDate_Init ON #LastPassedTestDate_Init ( [Box_No] );
SELECT
[Box_No],
MAX([CreateDate]) AS [CreateDate]
INTO #LastPassedTestDate -- drop table #LastPassedTestDate
FROM #LastPassedTestDate_Init
GROUP BY Box_No;
CREATE CLUSTERED INDEX IDX_LastPassedTestDate ON #LastPassedTestDate ( [Box_No] );
--=============================Get Company RegNo on MiXControl=========================================
Select
ClientID,CompanyRegistrationNumber
into #tempCompanyReg
from dbo.ClientCompany
where CompanyRegistrationNumber <> ''
COLLATE SQL_Latin1_General_CP1_CI_AS
--==========================================Get Unit Info=============================================================
DECLARE @debperiodno INT;
SET @debperiodno = ( SELECT DISTINCT(periodno)FROM rsdebtors.dbo.period WHERE curflag = 1 );
INSERT INTO [AllUnitsReportDaily]
SELECT Distinct
UT.UnitTypeDesc AS [TYPE],
C.ContractID,
U.Box_No,
A.RegistrationNumber,
CL.AccountName,
rscon.chargecode,
--CASE
-- WHEN (cch.escalchargeflag = 1) THEN rscon.chargeamt
-- WHEN (rscon.incrperiod > @debperiodno + 1) THEN rscon.chargeamt
-- ELSE cch.chargeamt
-- END AS 'ChargeAmt',
rscon.chargeamt AS 'ChargeAmt',
cch.chargeamt as NormalChargeAmt,
rscon.startperiod,
C.CreateDate AS [ContractDate],
rscon.accountno AS [AccountNo],
C.salespersonid AS [SalesPersonID],
b2.brokername as SalesPersonDesc,
C.FitmentCentreID AS [FitmentCentreID],
case
when left(u.Box_No,2) = '04' then i.name
when LEFT(u.box_no,2) = '35' then i.name
else b1.brokername end as [FitmentCentreName],
CASE
WHEN (cch.escalchargeflag = 1) THEN 'RENTAL CONTRACT'
ELSE 'CASH CONTRACT'
END AS 'ContractType',
ISNULL(CCT.contracttypedesc,CCT1.contracttypedesc) AS ContractTypeDesc,
CAST(ISNULL(aic.PolicyNumber,'') AS CHAR(30)) AS 'PolicyNumber',
ISNULL(C.UniqueNumber,'') AS 'UniqueNumber',
ISNULL(l.leadno,ML.LEADID) AS LeadNo,
ISNULL(l.brokerno,LP.BrokerID) AS BrokerNo,
ISNULL(ISNULL(B.brokercode,LP.ExternalPartnerCode),'') AS ExternalPartnerCode,
ISNULL(L.idno,LP.IDNumber) AS IDNumber,
zl.MSISDN,
LPT.CreateDate AS LastPassedTestDate,
a.ChassisNumber,
tcr.CompanyRegistrationNumber,
ISNULL(l.createopr,ML.CreateOpr) AS LeadCreatedBy,
c.editOpr as [ContractModifiedBy],
ama.AssetMakeDesc,
am.AssetModelDesc
FROM MixControl.dbo.unit U WITH (NOLOCK)
JOIN MixControl.dbo.unittype UT WITH (NOLOCK) ON U.UnitTypeID = UT.UnitTypeID
JOIN MixControl.dbo.ContractAsset CA WITH (NOLOCK) ON CA.AssetID = U.AssetID
JOIN MixControl.dbo.CONTRACT C WITH (NOLOCK) ON C.ContractID = CA.ContractID
JOIN MixControl.dbo.Asset A WITH (NOLOCK) ON CA.AssetID = A.AssetID
JOIN MixControl.dbo.assetmodel AM WITH (NOLOCK) ON a.assetmodelid = am.assetmodelid
JOIN MixControl.dbo.assetmake AMA WITH (NOLOCK) ON am.assetmakeid = ama.assetmakeid
JOIN MixControl.dbo.Client CL WITH (NOLOCK) ON CL.ClientID = C.ClientID
JOIN RSDebtors.dbo.contractcharge rscon WITH (NOLOCK) ON rscon.contractno = C.ContractID
JOIN RSDebtors.dbo.charge cch WITH (NOLOCK) ON rscon.chargecode = cch.chargecode
LEFT JOIN RSlead.dbo.lead L WITH (NOLOCK) ON l.contractid = c.ContractID AND l.eventtypecode = 'PUSHTOFX'
LEFT JOIN RSlead.dbo.broker B WITH (NOLOCK) ON L.externalpartnerno = B.brokerno
LEFT JOIN MixControl.dbo.lead ML WITH (NOLOCK) ON ML.contractid = c.ContractID
LEFT JOIN MixControl.dbo.LeadPerson LP WITH (NOLOCK) ON LP.LeadPersonID = ML.LeadPersonID
LEFT JOIN MixControl.dbo.AssetInsuranceCompany AIC WITH (NOLOCK) ON CA.AssetID = AIC.AssetID
LEFT JOIN RSLead.dbo.contracttype CCT WITH (NOLOCK) ON CCT.contracttypeno = L.contracttypeno
LEFT JOIN RSLead.dbo.contracttype CCT1 WITH (NOLOCK) ON CCT1.contracttypeno = ML.contracttypeID
LEFT JOIN #navisionUnitSerials zl ON U.Box_No = zl.box_no COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT JOIN #LastPassedTestDate LPT WITH (NOLOCK) ON U.BOX_NO = LPT.Box_No COLLATE SQL_Latin1_General_CP1_CI_AS
left join #tempCompanyReg tcr with(nolock) on cl.ClientID = tcr.ClientID
left join RSlead.dbo.broker b1 on c.FitmentCentreID = cast(b1.brokerno as varchar(10))
left join RSlead.dbo.broker b2 on c.SalespersonID = b2.brokercode
left join Installer i on c.FitmentCentreID = cast(i.installerID as varchar(15))
WHERE rscon.endperiod > @debperiodno --and rscon.startperiod < = @debperiodno
AND U.IsActive = 1
AND CA.IsActive = 1
AND C.IsActive = 1
order by U.Box_No desc ;
--=========================================CA with service charges=========================================================
INSERT INTO [AllUnitsReportDaily]
SELECT Distinct UT.UnitTypeDesc AS [TYPE],
C.ContractID,
U.Box_No,
A.RegistrationNumber,
CL.AccountName,
'MixServices',
SUM(conserv.serviceamt) AS [chargeamt],
'0.00' as NormalChargeAmt,
conserv.startperiod,
C.CreateDate AS [ContractDate],
conserv.accountno AS [AccountNo],
C.salespersonid AS [SalesPersonID],
b2.brokername as SalesPersonDesc,
C.FitmentCentreID AS [FitmentCentreID],
b1.brokername as [FitmentCentreName],
CASE
WHEN l.contracttypeno = 2 THEN 'RENTAL CONTRACT'
ELSE 'CASH CONTRACT'
END AS 'Contract Type',
ISNULL(CCT.contracttypedesc,CCT1.contracttypedesc) AS ContractTypeDesc,
CAST(ISNULL(aic.PolicyNumber,'') AS CHAR(30)) AS 'PolicyNumber',
ISNULL(C.UniqueNumber,'') AS 'UniqueNumber',
ISNULL(l.leadno,ML.LEADID) AS LeadNo,
ISNULL(l.brokerno,LP.BrokerID) AS BrokerNo,
ISNULL(ISNULL(B.brokercode,LP.ExternalPartnerCode),'') AS ExternalPartnerCode,
ISNULL(L.idno,LP.IDNumber) AS IDNumber,
zl.MSISDN,
LPT.CreateDate AS LastPassedTestDate,
a.ChassisNumber,
tcr.CompanyRegistrationNumber,
ISNULL(l.createopr,ML.CreateOpr) AS LeadCreatedBy,
c.editOpr as [ContractModifiedBy],
ama.AssetMakeDesc,
am.AssetModelDesc
FROM unit U WITH (NOLOCK)
JOIN unittype UT WITH (NOLOCK) ON U.UnitTypeID = UT.UnitTypeID
JOIN ContractAsset CA WITH (NOLOCK) ON CA.AssetID = U.AssetID
JOIN CONTRACT C WITH (NOLOCK) ON C.ContractID = CA.ContractID
JOIN Asset A WITH (NOLOCK) ON CA.AssetID = A.AssetID
JOIN dbo.assetmodel AM WITH (NOLOCK) ON a.assetmodelid = am.assetmodelid
JOIN dbo.assetmake AMA WITH (NOLOCK) ON am.assetmakeid = ama.assetmakeid
JOIN Client CL WITH (NOLOCK) ON CL.ClientID = C.ClientID
JOIN rsdebtors.dbo.contractservice conserv WITH (NOLOCK) ON conserv.contractno = ca.ContractID
LEFT JOIN AssetInsuranceCompany AIC WITH (NOLOCK) ON CA.AssetID = AIC.AssetID
LEFT JOIN rslead.dbo.lead l WITH (NOLOCK) ON CA.ContractID = l.contractid AND l.eventtypecode = 'PUSHTOFX'
LEFT JOIN RSlead.dbo.broker B WITH (NOLOCK) ON L.externalpartnerno = B.brokerno
LEFT JOIN MixControl.dbo.lead ML WITH (NOLOCK) ON ML.contractid = c.ContractID
LEFT JOIN MixControl.dbo.LeadPerson LP WITH (NOLOCK) ON LP.LeadPersonID = ML.LeadPersonID
LEFT JOIN RSLead.dbo.contracttype CCT WITH (NOLOCK) ON CCT.contracttypeno = L.contracttypeno
LEFT JOIN RSLead.dbo.contracttype CCT1 WITH (NOLOCK) ON CCT1.contracttypeno = ML.contracttypeID
LEFT JOIN #navisionUnitSerials zl ON U.Box_No = zl.box_no
LEFT JOIN #LastPassedTestDate LPT WITH (NOLOCK) ON U.BOX_NO = LPT.Box_No
left join #tempCompanyReg tcr on cl.ClientID = tcr.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS
left join RSlead.dbo.broker b1 on c.FitmentCentreID = cast(b1.brokerno as varchar(10))
left join RSlead.dbo.broker b2 on c.SalespersonID = b2.brokercode
WHERE @debperiodno BETWEEN conserv.startperiod AND conserv.endperiod
AND U.UnitTypeID = 7 --ca
AND U.IsActive = 1
AND CA.IsActive = 1
AND c.IsActive = 1
GROUP BY
C.ContractID,
U.Box_No,
A.RegistrationNumber,
CL.AccountName,
conserv.startperiod,
C.CreateDate,
conserv.accountno,
C.salespersonid,
b2.brokername,
C.FitmentCentreID,
b1.brokername,
CASE
WHEN l.contracttypeno = 2 THEN 'RENTAL CONTRACT'
ELSE 'CASH CONTRACT'
END,
CCT.contracttypedesc,
CCT1.contracttypedesc,
aic.PolicyNumber,
C.UniqueNumber,
UT.UnitTypeDesc,
ISNULL(l.leadno,ML.LEADID),
ISNULL(l.brokerno,LP.BrokerID),
ISNULL(B.brokercode,LP.ExternalPartnerCode),
ISNULL(L.idno,LP.IDNumber),
zl.MSISDN,
LPT.CreateDate,
a.ChassisNumber,
tcr.CompanyRegistrationNumber,
ISNULL(l.createopr,ML.CreateOpr),
c.editOpr,
ama.AssetMakeDesc,
am.AssetModelDesc
COLLATE SQL_Latin1_General_CP1_CI_AS
September 13, 2013 at 4:28 am
I come across this one from time to time with different third party applications.
Somewhere in your procedure, on a comparison field (i.e. tablea.col1 = tableb.col1)
you need to add
collate SQL_Latin1_General_CP1_CI_AS
in the comparison e.g.
e.employ_ref COLLATE SQL_Latin1_General_CP1_CI_AS = i.employ_ref
The problem is trying to find the right place, best bet is to break the code down to chunks to find this out - good luck!
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
September 13, 2013 at 4:32 pm
Yes.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply