September 2, 2013 at 1:03 am
Hi Guys,
I need help in sorting out this job its begin failing for a week now.
The code is as follows:
--==================================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 DATABASE_DEFAULT
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 DATABASE_DEFAULT
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 <> ''
--==========================================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 DATABASE_DEFAULT
LEFT JOIN #LastPassedTestDate LPT WITH (NOLOCK) ON U.BOX_NO = LPT.Box_No COLLATE DATABASE_DEFAULT
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 COLLATE DATABASE_DEFAULT
LEFT JOIN #LastPassedTestDate LPT WITH (NOLOCK) ON U.BOX_NO = LPT.Box_No
left join #tempCompanyReg tcr 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
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 ;
--============================================DISPLAY DATA=============================================================
--RUNTIME AROUND 35S
/*
--compare to excel file
SELECT [TYPE], COUNT(UnitId) AS Amount
FROM AllUnitsReportDaily
GROUP BY [TYPE]
ORDER BY [TYPE];
SELECT *
FROM AllUnitsReportDaily
order by CONTRACTDATE DESC
*/
/*
--Drop tables
--=================================--
DROP TABLE #navisionUnitSerials;
DROP TABLE #LastPassedTestDate;
DROP TABLE #LastPassedTestDate_Init
--=================================--
--SELECT * FROM AllUnitsReportDaily ORDER BY CONTRACTDATE DESC
September 2, 2013 at 1:26 am
Hi,
It will be easier to read if you also put the error message in the post itself (instead of only as subject). From the error message it seems like you have al collation conflict between the linked server connection and the originating server. Specify the desired collation in the query on the string-columns ((N)VARCHAR and (N)CHAR column types). Something like:
SELECT
int_colum1
, int_column2
, char_column1 COLLATE Latin1_General_CI_AS as 'char_column1'
, char_column2 COLLATE Latin1_General_CI_AS as 'char_column2'
, int_column3
...
September 2, 2013 at 2:02 am
Thanks I will implement the changes and let you know.By way in future i post the error message too.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply