I have a series of stored procedures that first does an ETL process, then load the results of some formulas into a new table. At the end of the process, I have a small section of code that compares the output table to the input table to make sure all the records are there. I've put this all into a SQL Agent job and scheduled it to run every morning.
If I manually run the SQL Server Agent job, everything works perfectly fine. However when I let the Agent execute the job as scheduled, the code that compares the table notifies me that a number of records aren't in the final table. Of the roughly 1 million records in the table, each day it say only about 2000 aren't in the final table. This number changes each day.
I doubt it's a permissions problem, since I have other Agent jobs that do not have this problem.
Any thoughts as to what might cause this behaviour?
Edit
This is the code that does the insertion of data. My data has three types, and two different versions. I could have built it all into one stored procedure, but due to the way the processes can change, it's split into an individual stored procedure for each type/version combination. At the end of each stored procedure, a temp table is built with the results of the stored procedure. This temp table is called ##ResultsT1Ver1 for example, and UpdateResultTable is called, with the Type and Version of the parent stored procedure passed in as parameters.
Note that when I run my stored procedure, I build an intermediary table before building the temp table. Looking up one record that was flagged as not making it to the result table shows that it IS in the intermediary table. So the issue I think lies somewhere in either the insert, or the storage of the temp table.
The individual stored procedures are over 300 lines each, it'll take longer to clean them up for posting here.
ALTER PROCEDURE [dbo].[UpdateResultTable] @Version varchar(2), @Type varchar(15)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(4000);
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Results'))
BEGIN
IF (EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.Results') AND name = 'IDX_Results_Epinum'))
BEGIN DROP INDEX [IDX_Results_Epinum] ON dbo.Results END
END
ELSE
CREATE TABLE dbo.Results
(
ResultsId [int] IDENTITY(1,1) NOT NULL,
ValType VARCHAR(10) NULL,
Epinum VARCHAR(50) NULL,
N15 DECIMAL(25,13) NULL,
G15 DECIMAL(25,13) NULL,
N16 DECIMAL(25,13) NULL,
G16 DECIMAL(25,13) NULL,
EstablishmentId VARCHAR(9) NULL,
ServiceDate datetime NULL,
ExcludedRecord VARCHAR(11) NULL,
CONSTRAINT [PK_Results] PRIMARY KEY CLUSTERED (ResultsId)
);
SET @SQL = N'
IF EXISTS (SELECT n.Epinum FROM ##Results' + @Version + @Type + N' n INNER Join dbo.Results r on r.Epinum = n.Epinum WHERE n.N' + @VERSION + ' <> r.N' + @VERSION + ' OR n.G' + @VERSION + ' <> r.G' + @VERSION + '
OR n.ValType <> r.ValType OR n.ExcludedRecord <> r.ExcludedRecord)
BEGIN
UPDATE D
SET D.N' + @VERSION + ' = S.N' + @VERSION + ',
D.G' + @VERSION + ' = S.G' + @VERSION + ',
D.ValType = S.ValType ,
D.ExcludedRecord = S.ExcludedRecord
FROM dbo.Results D
INNER JOIN ##Results' + @Version + @Type + N' S ON D.Epinum = S.Epinum
END
ELSE
BEGIN
INSERT INTO dbo.Results
SELECT
ValType,
Epinum,'
SET @SQL = @SQL + CASE WHEN @Version = '15' THEN N'N15, G15, 0.0, 0.0, ' ELSE N'0.0,0.0,N16, G16, ' END
SET @SQL = @SQL + N'
EstablishmentId ,
ServiceDate,
ExcludedRecord
FROM ##Results' + @Version + @Type + N' S
WHERE NOT EXISTS (SELECT Epinum FROM dbo.Results D WHERE S.Epinum = D.Epinum)
END'
--select @SQL
print @sql
declare @val int;
declare @printsql NVARCHAR(4000);
set @printsql = 'declare @val int;
select @val = count(*) from ##Results' +@version + @type +'; print @val;'
exec sp_executesql @printsql;
exec sp_executesql @SQL;
CREATE NONCLUSTERED INDEX [IDX_Results_Epinum] ON dbo.Results (Epinum ASC) INCLUDE (N15, G15, N16,G16)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
END
