SQL Results missing in Service

Thanks for the reply.

1. What version of ThingWorx are you using?
   8.5
2. What version of SQL are you using?
   SQL 2017
3. Would you share the code of the Stored Proc/service that you are executing?
   Code follows, sanitized, of course. It just calls some data from a linked server, then does a bit of math and returns results.
   
   

<code>

Create Procedure LineEff AS
SET NOCOUNT ON
--production efficiency

Declare @StartDate datetime, @EndDate datetime
Declare @NumShifts int=7
Set @EndDate=getdate()
Select @StartDate=DATEADD(hh,-1*8*@NumShifts,@EndDate)

Select Top 1 @StartDate=convert(datetime,StartDate) from Shifts where StartDate < @StartDate order by StartDate desc

--Select @StartDate, @EndDate

Declare @Counters table(ID int identity(1,1), Name varchar(50), TS datetime, Value int)
Declare @Down table(ID int identity(1,1), Name varchar(50), TS datetime, Value int)

Declare @s varchar(max)
Set @s= 'SELECT NAME, TS, VALUE FROM HISTORY WHERE NAME IN (''xxxxxxxxxx',''xxxxxxxxxx'',''xxxxxxxxxx'') AND TS BETWEEN '''
+ FORMAT( @StartDate, 'dd-MMM-yy HH:mm', 'en-US' ) + ''' AND ''' + FORMAT( @EndDate, 'dd-MMM-yy HH:mm', 'en-US' ) +''' AND PERIOD=''00:01:00'''

Insert @Counters(Name, TS, Value)
EXECUTE(@s) AT IP21

Declare @q varchar(max)
Set @q= 'SELECT NAME, TS, VALUE FROM HISTORY WHERE NAME IN (''xxxxxxxxxxx'') AND TS BETWEEN '''
+ FORMAT( @StartDate, 'dd-MMM-yy HH:mm', 'en-US' ) + ''' AND ''' + FORMAT( @EndDate, 'dd-MMM-yy HH:mm', 'en-US' ) +''' AND PERIOD=''00:01:00'''

Insert @Down(Name, TS, Value)
EXECUTE(@q) AT IP21

--get a table of the last N shifts
Declare @Results table(ID int identity(1,1),StartDate datetime, EndDate datetime, ShiftID int, Crew varchar(5), TotalBags int,BPH float, Eff float)

Insert @Results(StartDate, EndDate, ShiftID)
Select top (@NumShifts) StartDate, EndDate ,ShiftID from Shifts where convert(datetime,StartDate) < @EndDate
order by StartDate desc

--for each shift, compute metrics
Declare @i int=1

Declare @TotalBags int, @RunMins int, @ShiftEnd datetime, @ShiftNum int, @CountTagName varchar(50), @ShiftStart datetime, @DownMins int, @TotalMins int, @LineEff float

While @i <= (Select max(ID) from @Results)
BEGIN
--get total bags for shift
--value of counter at shift end time
Select @ShiftStart=StartDate, @ShiftEnd = EndDate, @ShiftNum=ShiftID from @Results where ID=@i
If @ShiftNum=1
Set @CountTagName='xxxxxxxxxx'
If @ShiftNum=2
Set @CountTagName='xxxxxxxxxx'
If @ShiftNum=3
Set @CountTagName='xxxxxxxxxx'

If @i=1
BEGIN
Select top 1 @TotalBags=Value from @Counters where Name=@CountTagName order by TS desc
END
ELSE
BEGIN

Select TOP 1 @TotalBags=Value from @Counters where TS=@ShiftEnd and Name=@CountTagName

END

--get the total down minutes, calculate effeciency
If @i=1
BEGIN
Select @TotalMins=datediff(mi,@ShiftStart,getdate())
Select @DownMins=sum(Value) from @Down where TS between @ShiftStart and getdate()
Set @RunMins=@TotalMins-@DownMins
Set @LineEff=100.*@RunMins/@TotalMins

END
ELSE
BEGIN
Select @TotalMins=datediff(mi,@ShiftStart,@ShiftEnd)
Select @DownMins=sum(Value) from @Down where TS between @ShiftStart and @ShiftEnd
Set @RunMins=@TotalMins-@DownMins
Set @LineEff=100.*@RunMins/@TotalMins
END

If @LineEff <0
Set @LineEff=0

Update @Results set Totalbags=@TotalBags, BPH=@TotalBags*1./DATEDIFF(hh,@ShiftStart,@ShiftEnd), Eff=@LineEff where ID=@i

set @i=@i+1
END

Select ID, StartDate, EndDate,ShiftID, Totalbags, BPH, Eff as Eff,
Case when ShiftID=1 then 'A' when ShiftID=2 then 'B' when ShiftID=3 then 'C' end as Crew
from @Results

</ code>