Friday, September 13, 2013

[T-SQL] Error formatting query?

[T-SQL] Error formatting query?

Error formatting query?

Posted: 12 Sep 2013 09:39 PM PDT

HiError Messages, Could you help me. how to resolve this error? purpose of script email sending when rows selected..Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.[code="sql"]select MACHINE, COUNT(*) as Instance into #tableAfrom LIMSPROD.dbo.CM_INSTANCE group by MACHINESelect COUNT(*)/4 as InstanceA into #tableBfrom LIMSPROD.dbo.CM_INSTANCESelect A.MACHINE, A.Instance, B.InstanceA, (B.InstanceA-A.Instance) as Diff into #resultsfrom #tableA A, #tableB Bwhere (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3--number of records to not be zeroIf ((select count(*) from #results) <> 0)BeginEXEC msdb.dbo.sp_send_dbmail @profile_name='db_mail',@recipients='ananda.murugesan@xyz.com',@subject='Alert!-Verify Instance',@query='select * from #results'Enddrop table #tableAdrop table #tableBdrop table #results[/code]

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.

Posted: 12 Sep 2013 09:15 PM PDT

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: [b][b]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.[/b]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

Help in Dynamic query....

Posted: 12 Sep 2013 04:59 PM PDT

Hi all,I am new to this dynamic sql concept and I have written a script uaing dynamic sql but I am getting while executing it...[code="sql"]Declare @ServerName nvarchar(100)='[OTINSRCPELODD01\SFA]', @databasename nvarchar(100)='[ESSOTISNL_73DEV]' , @sql nvarchar(4000) SET @sql = 'SELECT DISTINCT Sol.EquipmentOfficeId, Ofc.CompanyID, DRD.ProjectId, DRD.ProposalId, DRD.SolutionId, DRD.UnitId, DRD.DeviationNo, I.IsRevisedRequest, DRD.DeviationStatus, DRD.RequestDate, DRD.RequestedBy, DRD.RepliedBy, DRD.DeleteFlag, DRD.DateAdded, DRD.AddedBy, DRD.DateChanged, DRD.ChangedBy, DRD.ReplyDate FROM'+ @ServerName +'.'+ @Databasename +'.dbo.'+ 'DeviationRequestDetails DRD WITH(NOLOCK) INNER JOIN'+ @ServerName +'.'+ @Databasename +'.dbo.'+ 'vw_MaxDeviation C ON C.ProjectId = DRD.ProjectId AND C.ProposalId = DRD.ProposalId AND C.SolutionId = DRD.SolutionId AND C.UnitId = DRD.UnitId AND C.RID = DRD.RequestId INNER JOIN'+ @servername+'.'+@Databasename+'.dbo.'+ 'DeviationRequestDetailsInstallation I WITH(NOLOCK) ON DRD.OfficeId = I.OfficeID AND DRD.ProjectID = I.ProjectId AND DRD.ProposalId = I.ProposalID AND DRD.SolutionID = I.SolutionId AND DRD.UnitID = I.UnitId AND DRD.RequestId = I.RequestId INNER JOIN'+ @servername+'.'+@Databasename+'.dbo.'+'Solution Sol WITH(NOLOCK) ON Sol.OfficeId = DRD.OfficeId AND Sol.ProjectId = DRD.ProjectId AND Sol.ProposalId = DRD.ProposalId AND Sol.SolutionId = DRD.SolutionId INNER JOIN'+ @servername+'.'+@Databasename+'.dbo.'+ 'vw_Office Ofc ON Ofc.OfficeID = DRD.OfficeId'--SELECT @sql EXEC sp_executesql @sql,@databasename,@servername --select * from [OTINSRCPELODD01\SFA].ESSOTISCLC_73DEV.dbo.ssislastrun[/code]When I select the query from SELECT @sql and run it it runs succesfully but when I run using sp_execute sql it gives me error:[b]Incorrect syntax near 'ESSOTISNL_73DEV'.[/b]

sum in tsql

Posted: 12 Sep 2013 05:05 AM PDT

Hi,I have a question and see if people can help me out in this, I was trying to sum the column number up, but somehow if I do select " + @GrandTotalCol + " from ##temp1, it doesnot sum the column, however if I do select ISNULL([column1],0) + ISNULL ([column2],0) then it works. my @GrandTotalCol variable does print ISNULL([column1],0) + ISNULL ([column2],0). I am not sure what did I wrong. any help and clue would be appreciate.thanks/* GRAND TOTAL COLUMN */DECLARE @GrandTotalCol NVARCHAR (MAX)SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (ORGANIZATIONID_NAME AS nvarchar(max)) +'],0) + ', 'ISNULL([' + CAST(ORGANIZATIONID_NAME AS nvarchar(max))+ '],0) + ')FROMREQUESTSSET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)select reporttypename," + @GrandTotalCol + "from ##temp1
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog