February 6, 2020 at 3:28 pm
Can we assume that you have a QA/Test environment which is intended to be a close-to exact copy of Prod? If so, does the problem happen there too?
If not, I'd start digging into instance-level settings and comparing them. I've seen scripts before which can grab lots of this stuff for you, though I don't have one to hand.
Was just about to suggest something like that myself Phil.
Glenn has his DMV Diagnostic Scripts and Excel sheets which will pull in that kind of information for you Thom. There's also things like AirForge which can company server side settings side by side.
Unable to repro the error using the sample function and proc on any server I have unless I add in a blank string which is to be expected.
February 6, 2020 at 4:17 pm
Can we assume that you have a QA/Test environment which is intended to be a close-to exact copy of Prod? If so, does the problem happen there too?
If not, I'd start digging into instance-level settings and comparing them. I've seen scripts before which can grab lots of this stuff for you, though I don't have one to hand.
I wish I did... I have a UAT environment, however, it is not close to exact in data terms. No where near it.
I've been trying everything to repro this so far today, I literally can't no matter what I try.
I know the error happens in this function because it's the only thing that uses DATEFROMPARTS (note that this is the "fixed" version, where DATEFROMPARTS is commented out, it fails when the comments on TRY_CONVERT and DATEFROMPARTS are reversed):
ALTER FUNCTION [dbo].[RACVehicleSearch] (@Branch int,
@PolicyRef varchar(10))
RETURNS table
AS RETURN
SELECT RTVA.B@,
RTVA.PolRef@,
RTVA.Prn,
RTVA.Reg AS RegNo,
RTVA.Make,
RTVA.Model,
CONVERT(date, RTVA.Bdowndateon) AS BreakdownOn,
CONVERT(date, RTVA.Bdowndateoff) AS BreakdownOff,
0 AS Horses,
RTVA.Fuel,
RTVA.cc,
--DATEFROMPARTS(RTVA.Yrreg,1,1) AS Certdate
TRY_CONVERT(date,CONCAT(RTVA.Yrreg,'01','01')) AS CertDate
FROM OpenGI.dbo.ic_BD_RTVA RTVA
WHERE RTVA.B@ = @Branch
AND RTVA.PolRef@ = @PolicyRef
UNION ALL
SELECT WHVA.B@,
WHVA.PolRef@,
WHVA.Prn,
WHVA.Reg,
WHVA.Make,
WHVA.Model,
CONVERT(date, WHVA.Bdowndateon) AS BreakdownOn,
CONVERT(date, WHVA.Bdowndateoff) AS BreakdownOff,
0 AS Horses,
WHVA.Fuel,
WHVA.cc,
--DATEFROMPARTS(WHVA.Yrreg,1,1) AS Certdate
TRY_CONVERT(date,CONCAT(WHVA.Yrreg,'01','01')) AS CertDate
FROM OpenGI.dbo.ic_BD_WHVA WHVA
WHERE WHVA.B@ = @Branch
AND WHVA.PolRef@ = @PolicyRef
UNION ALL
SELECT HBV.B@,
HBV.PolRef@,
HBV.Prn,
HBV.Regno,
HBV.Make,
HBV.Model,
CONVERT(date,HBV.Bdowndateon) AS BreakdownOn,
CONVERT(date,HBV.Bdowndateoff) AS BreakdownOff,
HBV.Horses,
NULL AS Fuel,
NULL AS Cc,
--DATEFROMPARTS(HBV.Yearreg,1,1) AS Certdate
TRY_CONVERT(date,CONCAT(HBV.Yearreg,'01','01')) AS CertDate
FROM OpenGI.dbo.ic_BD_HBV HBV
WHERE HBV.B@ = @Branch
AND HBV.PolRef@ = @PolicyRef
UNION ALL
SELECT CGV1.B@,
CGV1.PolRef@,
CGV1.Prn,
CGV1.Reg,
CGV1.Make,
CGV1.Model,
CONVERT(date, CGV1.Bdowndateon) AS BreakdownOn,
CONVERT(date, CGV1.Bdowndateoff) AS BreakdownOff,
0 AS Horses,
NULL AS Fuel,
CGV1.Cc,
--DATEFROMPARTS(CGV1.Yearreg,1,1) AS Certdate
TRY_CONVERT(date,CONCAT(CGV1.Yearreg,'01','01')) AS CertDate
FROM OpenGI.dbo.ic_BD_CGV1 CGV1
WHERE CGV1.B@ = @Branch
AND CGV1.PolRef@ = @PolicyRef
UNION ALL
SELECT HTBD.B@,
HTBD.PolRef@,
HTBD.Prn,
HTBD.Reg,
HTBD.Make,
HTBD.Model,
CONVERT(date, HTBD.Date_on) AS BreakdownOn,
CONVERT(date, HTBD.Date_off) AS BreakdownOff,
0 AS Horses,
NULL AS Fuel,
NULL AS Cc,
NULL AS Certdate
FROM OpenGI.dbo.ic_BD_HTBD HTBD
WHERE HTBD.B@ = @Branch
AND HTBD.PolRef@ = @PolicyRef
UNION ALL
SELECT HPT.B@,
HPT.PolRef@,
HPT.Prn,
HPT.Reg,
HPT.Make,
HPT.Model,
CONVERT(date, HPT.Bdowndateon) AS BreakdownOn,
CONVERT(date, HPT.Bdowndateoff) AS BreakdownOff,
0 AS Horses,
NULL AS Fuel,
NULL AS Cc,
NULL AS Certdate
FROM OpenGI.dbo.ic_BD_HPT HPT
WHERE HPT.B@ = @Branch
AND HPT.PolRef@ = @PolicyRef;
I know that if I run the SQL in the SP that that function is called in, it runs fine too:
--ALTER PROC [dbo].[RACOnCoverBDX_sp] @ReportDate date AS
BEGIN
DECLARE @ReportDate date;
SET @ReportDate = '20200205';
WITH CTE AS(
SELECT ROW_NUMBER() OVER (PARTITION BY BTX.PolRef@
ORDER BY BTX.Dt_raised,
BTX.Suffix@,
Veh.BreakdownOn) AS RN,
BPY.PolRef@,
CASE
WHEN BTX.Trantype = 'Renewal' AND BTX.Dt_raised < '20210201' THEN 'A'
WHEN BTX.Trantype = 'Renewal' THEN 'R'
WHEN BTX.Dt_raised = Veh.BreakdownOn THEN 'A'
WHEN BTX.Dt_raised = Veh.BreakdownOff THEN 'D'
WHEN BTX.Trantype = 'Cancellation' THEN 'D'
WHEN BTX.Dt_raised = BPY.Term_date THEN 'D'
WHEN BTX.Dt_raised >= Veh.BreakdownOn AND (BTX.Dt_raised < Veh.BreakdownOff OR Veh.BreakdownOff IS NULL) THEN 'U'
WHEN BTX.Trantype = 'Endorsement' THEN 'U'
--ELSE 'A'
END AS TranFlag,
Veh.RegNo,
'GB' AS RegCountry,
CASE BPY.Ptype WHEN 'CG' THEN 'THOM01'
WHEN 'HT' THEN 'THOM02'
WHEN 'HP' THEN 'THOM02'
WHEN 'HB' THEN 'THOM03'
WHEN 'WH' THEN 'THOM04'
WHEN 'RT' THEN 'THOM05'
END AS AgreementNo,
NULL AS PartnerID,
'TOM A' AS PartnerName,
CONVERT(date,CASE
WHEN BTX.Trantype = 'Renewal' THEN BTX.Dt_raised
WHEN BTX.Dt_raised = Veh.BreakdownOn THEN BTX.Dt_raised
ELSE BTXp.Dt_raised
END) AS CoverStart,
CONVERT(date,COALESCE(Veh.BreakdownOff, BPY.Term_date, DATEADD(DAY, -1, DATEADD(YEAR, 1, BTXp.Dt_raised)))) AS CoverEnd,
CONCAT(BPY.PolRef@,RIGHT(CONCAT('000',Veh.PRN),3)) AS UniqueID,
BPY.PolRef@ AS LinkedId,
NULL AS RetailPrice,
NULL AS Comission,
NULL AS NetPrice,
NULL AS IPT,
Veh.Make,
Veh.Model,
NULL AS Type,
CONVERT(date,Veh.Certdate) AS RegDate,
NULL AS VIN,
NULL AS Mileage,
NULL AS Transmission,
Veh.Fuel,
Veh.Cc AS Engine,
NULL AS Height,
NULL AS [Length],
NULL AS Width,
NULL AS [Weight],
NULL AS TyreSize,
NULL AS Colour,
BDPH.Title_ext,
BDPH.Forename,
BDPH.Surname,
BCM.Char5 AS CompanyName,
REPLACE(BCM.Addr1,',','') AS Addr1,
REPLACE(BCM.Addr2,',','') AS Addr2,
REPLACE(BCM.Addr3,',','') AS Addr3,
REPLACE(BCM.Addr4,',','') AS Addr4,
NULL AS Addr5,
BCM.Pcode,
'GB' AS Country,
NULL AS AddressType,
BCM.Tel,
BCM.Tel3 AS Tel2,
NULL AS ClientRef1,
NULL AS ClientRef2,
NULL AS ClientRef3,
NULL AS ClientRef4,
NULL AS ClientRef5
FROM OpenGI.dbo.ic_yyclient BCM
JOIN OpenGI.dbo.ic_brpolicy BPY ON BCM.B@ = BPY.B@
AND BCM.Ref@ = BPY.Ref@
JOIN OpenGI.dbo.ic_brcledger BTX ON BPY.B@ = BTX.B@
AND BTX.PolRef@ = BPY.PolRef@
LEFT JOIN OpenGI.dbo.ic_brcledger BTXc ON BTX.B@ = BTXc.B@
AND BTX.PolRef@ = BTXc.PolRef@
AND BTX.Suffix@ = BTXc.C_mast
AND BTXc.C_ins IN ('RAC Horsebox Breakdown','RAC Rural Taxi Breakdown','RAC Clergy Breakdown','RAC Trailer Breakdown','RAC Wedding Hire Breakdown')
CROSS APPLY dbo.RACVehicleSearch (BPY.B@, BPY.PolRef@) Veh
OUTER APPLY dbo.RACTransactionFrameSearch (BPY.B@, BPY.PolRef@, BTX.Ref) Trn
OUTER APPLY dbo.RACTransactionFrameSearch_History (BPY.B@, BPY.PolRef@, BTX.Ref) Trnh
CROSS APPLY OpenGIDW.dbo.brcledgerYOA_fn(BTX.B@, BTX.PolRef@, BTX.Dt_raised) BTXp
LEFT JOIN OpenGI.dbo.ic_BD_BDPH BDPH ON BPY.B@ = BDPH.B@
AND BPY.PolRef@ = BDPH.PolRef@
CROSS APPLY (SELECT COUNT(*) AS Vehicles
FROM dbo.RACVehicleSearch (BPY.B@, BPY.PolRef@) VS
WHERE Veh.BreakdownOn = BTX.Dt_raised
OR Veh.BreakdownOff = BTX.Dt_raised) C
WHERE BPY.B@ IN (0,8)
AND BPY.Ptype IN ('RT','WH','HB','CG','HP','HT')
--AND BPY.PolRef@ = 'TETX40WH01'
AND BTX.Dt_raised >= Veh.BreakdownOn
AND (BTX.Dt_raised <= Veh.BreakdownOff OR Veh.BreakdownOff IS NULL)
AND Veh.RegNo IS NOT NULL
AND (BTXc.Key@ IS NOT NULL
OR (BTXc.Key@ IS NULL
AND (BTX.Orig_debt = 0 OR @ReportDate IN (Veh.BreakdownOn,Veh.BreakdownOff))
AND (Trn.BDowninsurer IN ('RAC Horsebox Breakdown','RAC Rural Taxi Breakdown','RAC Clergy Breakdown','RAC Trailer Breakdown','RAC Wedding Hire Breakdown')
OR (Trn.Tranref IS NULL AND Trnh.BDowninsurer IN ('RAC Horsebox Breakdown','RAC Rural Taxi Breakdown','RAC Clergy Breakdown','RAC Trailer Breakdown','RAC Wedding Hire Breakdown')))))
AND ((BTX.Dt_raised = @ReportDate AND BTX.Datecreated <= @ReportDate)
OR (BTX.Dt_raised < @ReportDate AND BTX.Datecreated = @ReportDate))
AND ((BTX.Trantype IN ('New Business', 'Transfrd NB', 'Renewal')
AND Veh.BreakdownOn <= BTX.Dt_raised
AND (Veh.BreakdownOff >= BTX.Dt_raised
OR Veh.BreakdownOff IS NULL))
OR (BTX.Trantype IN ('Endorsement')
AND (Veh.BreakdownOn = BTX.Dt_raised
OR Veh.BreakdownOff = BTX.Dt_raised))
OR (BTX.Trantype IN ('Endorsement')
AND C.Vehicles = 0
AND BTX.Orig_debt = 0
AND (Veh.BreakdownOn <= BTX.Dt_raised
OR Veh.BreakdownOff >= BTX.Dt_raised))
OR (BTX.Trantype IN ('Cancellation')
AND Veh.BreakdownOn < BTX.Dt_raised
AND (Veh.BreakdownOff >= BTX.Dt_raised
OR Veh.BreakdownOff IS NULL)))
)
SELECT CTE.TranFlag,
CTE.RegNo,
CTE.RegCountry,
CTE.AgreementNo,
CTE.PartnerID,
CTE.PartnerName,
CTE.CoverStart,
CTE.CoverEnd,
CTE.UniqueID,
CTE.LinkedId,
CTE.RetailPrice,
CTE.Comission,
CTE.NetPrice,
CTE.IPT,
CTE.Make,
CTE.Model,
CTE.[Type],
CTE.RegDate,
CTE.VIN,
CTE.Mileage,
CTE.Transmission,
CTE.Fuel,
CTE.Engine,
CTE.Height,
CTE.[Length],
CTE.Width,
CTE.[Weight],
CTE.TyreSize,
CTE.Colour,
CTE.Title_ext,
CTE.Forename,
CTE.Surname,
CTE.CompanyName,
CTE.Addr1,
CTE.Addr2,
CTE.Addr3,
CTE.Addr4,
CTE.Addr5,
CTE.Pcode,
CTE.Country,
CTE.AddressType,
CTE.Tel,
CTE.Tel2,
CTE.ClientRef1,
CTE.ClientRef2,
CTE.ClientRef3,
CTE.ClientRef4,
CTE.ClientRef5
FROM CTE
ORDER BY PolRef@,
RN;
END;
But, as soon as I execute the above SP, EXEC dbo.RACOnCoverBDX_sp '20200205'; (obviously with the mentioned commented out functions reversed) I get the error below:
Cannot construct data type date, some of the arguments have values which are not valid.
Regardless of which DATEFROMPARTS I comment out, as long as one is there, the process fails when you EXEC the SP. Also, the dataset returned has no rows where RegDate (which comes from Certdate in the function) where the value is NULL , so there's no conversion failures when the TRY_CONVERT solution is used.
I'm really at a loss, and I don't really know what else I can do. The EXEC statement above, and the above SQL are effectively identical, so it makes no sense that one fails and the other does not. it also makes no sense that this worked yesterday, and today does not... 🙁
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 6, 2020 at 4:40 pm
I haven't reviewed them yet, but did wonder if, perhaps, the order in which the data engine is running the joins is different when I run the SQL and when it runs the SP, which is resulting in an odd row being found.
My familiarity with Execution plans is "ok" at best, so those with keener eyes may see something.
Actual Execution Plan, from when SQL inside SP is run: https://www.brentozar.com/pastetheplan/?id=HJhAyaYGL
Estimated Execution Plan, from when SP is run (can't get actual due to error): https://www.brentozar.com/pastetheplan/?id=BkIEZatfL
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 6, 2020 at 4:45 pm
Ok, that looks like I might be onto something. The SP has loads of scans, however, the SQL being run is all seeks. I jsut got the Actual Execution Plan for the fixed version (https://www.brentozar.com/pastetheplan/?id=rJedG6FfI) and that too has scans, not seeks.
Any ideas why the SP uses scans, but the SQL seeks? That's something I really don't understand well.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 6, 2020 at 4:57 pm
upgrade quickly, then you can use querystore and plan guides
MVDBA
February 6, 2020 at 4:58 pm
upgrade quickly, then you can use querystore and plan guides
Yeah, just wing it. I'm sure everything will be OK 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 6, 2020 at 5:08 pm
upgrade quickly, then you can use querystore and plan guides
Upgrade (to 2019) is on the cards, but probably not till Q3 this year. Me working out the budget has been put on hold while other projects that people have given us far little notice for have taken priority.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 6, 2020 at 6:46 pm
Phil Parkin wrote:MVDBA (Mike Vessey) wrote:upgrade quickly, then you can use querystore and plan guides
Yeah, just wing it. I'm sure everything will be OK 🙂
that is what my IT director keeps telling me 🙁
Get it in writing, notarised, sealed and fingerprint in blood. Then you should be covered.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 6, 2020 at 7:06 pm
I haven't reviewed them yet, but did wonder if, perhaps, the order in which the data engine is running the joins is different when I run the SQL and when it runs the SP, which is resulting in an odd row being found.
My familiarity with Execution plans is "ok" at best, so those with keener eyes may see something.
Actual Execution Plan, from when SQL inside SP is run: https://www.brentozar.com/pastetheplan/?id=HJhAyaYGL
Estimated Execution Plan, from when SP is run (can't get actual due to error): https://www.brentozar.com/pastetheplan/?id=BkIEZatfL%5B/quote%5D
I was going to mention this as an option - then look for any negative years. Is it possible that should be excluded - but the optimizer is pushing the datefromparts to an early segment in the plan before that data is filtered out?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 7, 2020 at 9:21 am
OK, yes, it was the order of the processing being different when EXEC was used and the SQL inside the SQL was being run. When the SQL was run, any rows from "bad" rows was being filtered out by joins or the WHERE before the function (RACVehicleSearch) was called. It seems that when EXEC was being used, those rows weren't being filtered, and there were 2 rows in the database where they had a value of 0 in yrreg. These are old rows, but the query was catching them and therefore failing.
Fixed by wrapping Yrreg in a NULLIF. Now I just need to work out why it scans. Tbut that's a different qusetion.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 10, 2020 at 2:14 pm
I had a problem a few days ago where using TRY_CAST gave no nulls in the result, but using just CAST caused an error. It turned out that either my WHERE clause or one of the JOINs was filtering out the guilty rows, but only after the relevant calculation had been attempted. Could yours be a similar situation? It would explain why it fails even for previous days' successful runs.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply