CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012sql-agent-job
Matt
Matt

Reputation: 4200

Stored procedure execution different when scheduled vs executed manually

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

Upvotes: 2

Views: 964

Answers (1)

Matt
Matt

Reputation: 4200

So I managed to solve the problem by removing the universal upsert procedure that used temp tables. Instead, I wrote my data sets to proper tables, then after all procedures ran, combined the results of these individual tables into one, then delete the tables. Seems to be working fine now. Odd

Upvotes: 0

Related Questions