Always On: SQL Agent can still run stored proc on inactive server

Question

We have the AO Clustering - HA&DR Hybrid configuration. Out of a dozen databases only this one database/application is encountering this.

The problem is that we have a SQL Agent Job scheduled to run on the PRIMARY (active) but for some reason it also runs on the SECONDARY (inactive) yet pointing correctly to the PRIMARY (active) which usually doubles the records being inserted after we FTP from an external machine.

YET - we cannot connect to SECONDARY or manually run the stored procedure, but the SQL Agent Job seems to be able to override and supersede AO!!!!

According to Bren Ozar's video: https://youtu.be/YNyY_A5733E?t=12m58s --- jobs & security can't fail over HA SyncMirroring

Any ideas? Anyone else having this?

I'm wondering if a hotfix is needed.

TIA

George

Sean,
Bren Ozar's video gave me some new leads but confirming it and identifying why it still happens and even why the agent job gets re-enabled on the secondary and runs in parallel is still a mystery. When it should return "The database EEOutageTracking is not accessible. (ObjectExplorer)". It's a 1-step SQL Server Agent job exec SP_ATSImport ----- how can I attach/paste the code ?

USE [EEOutageTracking]
GO
/****** Object:  StoredProcedure [dbo].[SP_ATSImport]    Script Date: 8/19/2016 2:49:36 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--- Lewycky  Dec 10, 2014: modifications for date header record to verify file xmitted 
---  is correct and recent 
ALTER PROCEDURE [dbo].[SP_ATSImport]
AS
BEGIN

BEGIN
    SET XACT_ABORT ,NOCOUNT ON;
    DECLARE
       @starttrancount int;
    BEGIN TRY
        IF @@TRANCOUNT > 0              --- added 
            BEGIN
                ROLLBACK TRANSACTION;
            END;

        SELECT @starttrancount = @@TRANCOUNT;
        ---- SET @starttrancount = 0;    --<-----------
        ----   SET @starttrancount = @@TRANCOUNT;
        ----  should be zero and not trancount 
        IF @starttrancount
           = 
           0
            BEGIN
                BEGIN TRANSACTION ATSIMPORT;
            END;



        DECLARE
           @dtCurrent datetime;
        SET @dtCurrent = GETDATE( );

        ----------Lewycky 12/2/14: added to handle verifying incoming file FTPd from ATS that it was sent the date 
        ----------                 SP_ATSImport is scheduled to run. Should be same date! 
        DECLARE
           @datestring char( 08 );
        SET @datestring = CONVERT( varchar( 8 ) ,GETDATE( ) ,112 );
        ----------

        ------  FOR FUTURE USE 
        --EXEC sp_configure 'show advanced options', 1
        --GO
        --RECONFIGURE
        --GO
        --EXEC sp_configure 'xp_cmdshell', 1
        --GO
        --RECONFIGURE
        --GO


        --exec sp_configure
        --go
        --exec sp_configure 'Ole Automation Procedures', 1
        ---- Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.
        --go
        --reconfigure
        --go


        DECLARE
           @filename varchar( 40 );


        -- In FTP Scheduled DOS job:  the file from AS/400 is placed in this subdir under this filename 
        -- indicating FTP was successful 
        -- PRODUCTION    SET @filename = 'S:\ATSJobs\Archive\ATSToday.txt';  --- where FTP placed file ( FTPATSProd.CMD )
        --------------------------------------------------------------------------------------------------
        -- use for TESTING BELOW     on 10.8.86.78
        -- SET @filename = 'E:\ATSJobs\Archive\ATSTodayX.txt';     ---   on 10.8.86.78
        ----SET @filename = 'C:\ATSJobs\Archive\ATSTodayX.txt';  --- where FTP placed file ( FTPATSProd.CMD )
        --------------------------------------------------------------------------------------------------
        -- PROD       SET @filename = 'S:\ATSJobs\Archive\ATSToday.txt';  --- where FTP placed file ( FTPATSProd.CMD )
        --*&SET @filename = 'E:\ATSJobs\Archive\ATSToday.txt';  --- where FTP placed file ( FTPATSProd.CMD )

      SET @filename = 'D:\ATS2EandEJobs\Archive\ATSToday.txt';  --- where FTP placed file on 10.9.76.27 ( FTPATSProd.CMD )


        DECLARE
           @i int;
        EXEC master..xp_fileexist @Filename ,@i OUT;

        IF @i = 1
            BEGIN

                IF OBJECT_ID( 'tempdb..#tempfile' )IS NOT NULL
                    BEGIN
                        DROP TABLE #tempfile;
                    END;

                DECLARE
                   @TotalRecordsForImport int ,
                   @EmptyInput char( 1 );


                CREATE TABLE #tempfile( Inputline varchar( 250 ));
                EXEC ( 'bulk INSERT #tempfile FROM "'+@filename+'"' );

                SET @EmptyInput = 'N';
                SET @TotalRecordsForImport = ( SELECT COUNT( * )
                                                 FROM #tempfile );

                IF @TotalRecordsForImport
                   = 
                   0
                    BEGIN
                        SET @EmptyInput = 'Y';    ---  used to report if FTPd file is empty 
                    END;

            ---------  immediately delete the ATSToday.txt file after bulk inserted                                            
            --------  EXEC xp_cmdshell 'del "S:\ATSJobs\ATS_Yada20141010_1652.txt" '                                           


            END;
        ELSE
            BEGIN
                SET @TotalRecordsForImport = 0;
                SET @EmptyInput = 'N';    ---  indicates file not received since count will also be zero 
            END;


        --------------------------------------------------------------------------------    
        ----------Lewycky 12/2/14: added to handle verifying incoming file FTPd from ATS that it was sent the date 
        ----------                 SP_ATSImport is scheduled to run. Should be same date!                                                 
        ------------------------------
        IF @i = 1
            BEGIN
                DECLARE
                   @ATSDateIn varchar( 08 );
                DECLARE
                   @DateCheck varchar( 300 );
                SET @DateCheck = ( SELECT TOP 1 *
                                     FROM #tempfile );
                SET @ATSDateIn = CAST( SUBSTRING( @DateCheck ,1 ,8 )AS char );

                ----------Lewycky 12/2/14: added to handle verifying incoming file FTPd from ATS that it was sent the date 
                ----------                 SP_ATSImport is scheduled to run. Should be same date! 

                IF @ATSDateIn
                   <> 
                   @datestring
                    BEGIN
                        SET @TotalRecordsForImport = 9999999;   --- overriding logic for zero in code below with ambiguous value for bypass code
                        SET @EmptyInput = 'X';    ---  Dummy override value not to interfere with logic below 

                        EXEC sp_SendCDOSysMail 

                    END;

            END;   --------------------------------------------------------------------------------                                   

        ---- SPECIAL LOGIC if file is empty with zero records - drop temp and rollback 
        IF @TotalRecordsForImport
           = 
           0
       AND @EmptyInput
           = 
           'Y'
            BEGIN EXEC sp_SendCDOSysMail '[email protected]' ,'[email protected],[email protected],[email protected],[email protected],[email protected]' ,'' ,'ATS Input file received is empty' ,'';
            -----  still  keep the file ?????

            END;

        --------------------------------------------------------------------------------

        ---- LOGIC if file is NOT received via STEP  - drop temp and rollback 
        IF @TotalRecordsForImport
           = 
           0
       AND @EmptyInput
           <> 
           'Y'
            BEGIN EXEC sp_SendCDOSysMail 
            END;

        --  Reverse special value back to Zero to prevent INSERTs  
        IF @EmptyInput
           = 
           'X'
            BEGIN
                SET @TotalRecordsForImport = 0;   --- reverse override value of 9999999 for Date conflict 
            END;


        ---- if valid file   insert and archive the flat file with a date & time stamp 
        ----- Bypass if "X" signifying Date Stamp on input file isn't run date 
        IF @TotalRecordsForImport
           > 
           1
            BEGIN
                INSERT INTO EEOutageTracking.dbo.ATStoAFCSpearInterface( EMPL_ID ,
                                                                         C_EMPL_FIRST_INIT ,
                                                                         C_EMPL_MIDD_INIT ,
                                                                         C_EMPL_LAST_NAME ,
                                                                         C_FORM_DATE ,
                                                                         C_CODE_1 ,
                                                                         C_HOURS_1 ,
                                                                         C_JOB_NO_1 ,
                                                                         C_CODE_2 ,
                                                                         C_HOURS_2 ,
                                                                         C_JOB_NO_2 ,
                                                                         C_CODE_3 ,
                                                                         C_HOURS_3 ,
                                                                         C_JOB_NO_3 ,
                                                                         C_CODE_4 ,
                                                                         C_HOURS_4 ,
                                                                         C_JOB_NO_4 ,
                                                                         C_CODE_5 ,
                                                                         C_HOURS_5 ,
                                                                         C_JOB_NO_5 ,
                                                                         C_CODE_6 ,
                                                                         C_HOURS_6 ,
                                                                         C_JOB_NO_6 ,
                                                                         C_CODE_7 ,
                                                                         C_HOURS_7 ,
                                                                         C_JOB_NO_7 ,
                                                                         C_CODE_8 ,
                                                                         C_HOURS_8 ,
                                                                         C_JOB_NO_8 ,
                                                                         C_CODE_9 ,
                                                                         C_HOURS_9 ,
                                                                         C_JOB_NO_9 ,
                                                                         C_CODE_10 ,
                                                                         C_HOURS_10 ,
                                                                         C_JOB_NO_10 ,
                                                                         C_CODE_11 ,
                                                                         C_HOURS_11 ,
                                                                         C_JOB_NO_11 ,
                                                                         C_CODE_12 ,
                                                                         C_HOURS_12 ,
                                                                         C_JOB_NO_12 ,
                                                                         C_START_DATE ,
                                                                         C_END_DATE ,
                                                                         C_MOD_FLAG ,
                                                                         C_WORK_SHIFT ,
                                                                         C_LAST_UPDATE_DATE ,
                                                                         C_UPDATED_BY )
                SELECT CAST( SUBSTRING( InputLine ,1 ,6 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,7 ,1 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,8 ,1 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,9 ,16 )AS char ) ,
                       SUBSTRING( InputLine ,29 ,4 ) + '-' + SUBSTRING( InputLine ,25 ,2 ) + '-' + SUBSTRING( InputLine ,27 ,2 ) ,
                       CAST( SUBSTRING( InputLine ,33 ,3 )AS char ) ,      
                       --CODE 1             
                       CAST( SUBSTRING( InputLine ,36 ,4 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,40 ,5 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,45 ,3 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,48 ,4 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,52 ,5 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,57 ,3 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,60 ,4 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,64 ,5 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,69 ,3 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,72 ,4 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,76 ,4 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,81 ,3 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,84 ,4 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,88 ,5 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,93 ,3 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,96 ,4 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,100 ,5 )AS char ) ,  
                       --jobno 6
                       CAST( SUBSTRING( InputLine ,105 ,3 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,108 ,4 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,112 ,5 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,117 ,3 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,120 ,4 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,124 ,5 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,129 ,3 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,132 ,4 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,136 ,5 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,141 ,3 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,144 ,4 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,148 ,5 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,153 ,3 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,156 ,4 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,160 ,5 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,165 ,3 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,168 ,4 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,172 ,5 )AS char ) ,   
                       --jobno 12 
                       CASE
                       WHEN SUBSTRING( InputLine ,177 ,12 )
                            = 
                            '000000000000' THEN NULL
                           ELSE SUBSTRING( InputLine ,181 ,4 ) + '-' + SUBSTRING( InputLine ,177 ,2 ) + '-' + SUBSTRING( InputLine ,179 ,2 ) + ' ' + SUBSTRING( InputLine ,185 ,2 ) + ':' + SUBSTRING( InputLine ,187 ,2 )
                       END ,
                       CASE
                       WHEN SUBSTRING( InputLine ,189 ,12 )
                            = 
                            '000000000000' THEN NULL
                           ELSE SUBSTRING( InputLine ,193 ,4 ) + '-' + SUBSTRING( InputLine ,189 ,2 ) + '-' + SUBSTRING( InputLine ,191 ,2 ) + ' ' + SUBSTRING( InputLine ,197 ,2 ) + ':' + SUBSTRING( InputLine ,199 ,2 )
                       END ,                 
                       --SUBSTRING( InputLine ,181 ,4 ) + '-' + SUBSTRING( InputLine ,177 ,2 ) + '-' + SUBSTRING( InputLine ,179 ,2 ) + ' ' + SUBSTRING( InputLine ,185 ,2 ) + ':' + SUBSTRING( InputLine ,187 ,2 ) ,
                       --SUBSTRING( InputLine ,193 ,4 ) + '-' + SUBSTRING( InputLine ,189 ,2 ) + '-' + SUBSTRING( InputLine ,191 ,2 ) + ' ' + SUBSTRING( InputLine ,197 ,2 ) + ':' + SUBSTRING( InputLine ,199 ,2 ) ,
                       CAST( SUBSTRING( InputLine ,201 ,1 )AS char ) ,
                       CAST( SUBSTRING( InputLine ,202 ,9 )AS char ) ,
                       SUBSTRING( InputLine ,215 ,4 ) + '-' + SUBSTRING( InputLine ,211 ,2 ) + '-' + SUBSTRING( InputLine ,213 ,2 ) ,
                       CAST( SUBSTRING( InputLine ,219 ,9 )AS char ) 
                --CAST( SUBSTRING( InputLine ,220 ,1 )AS char ) ,
                --CAST( SUBSTRING( InputLine ,221 ,16 )AS varchar )
                  FROM #tempfile
                  WHERE SUBSTRING( INPUTLINE ,1 ,8 )
                        <> 
                        @ATSDateIn;   -----   exclude the header record containing the date stamp ----  Lewycky
                ---- dbo.ATSStaging;

                --DROP TABLE #tempfile created from the flat file 
                DROP TABLE #tempfile;

                SELECT @starttrancount = @@TRANCOUNT;
                IF @starttrancount    ---  set to 1 from begin trans ---@starttrancount
                   <> 
                   0
               AND @TotalRecordsForImport
                   > 
                   0
                    BEGIN
                        COMMIT TRANSACTION ATSIMPORT;
                        --- relocated **& Lewycky 
                        EXEC sp_SendCDOSysMail '[email protected]' ,'[email protected],[email protected],[email protected],[email protected],[email protected]' ,'' ,'ATS loaded successfully' ,'';

                    END;
                ELSE
                    BEGIN
                        ROLLBACK TRANSACTION;
                    END;


            END;                    

        -- RENAME INPUT FILE ATSToday.txt  WITH DATE & TIME STAMP AND ARCHIVE IT             
        IF @TotalRecordsForImport
           > 
           0
            BEGIN

                ---  create string to copy or rename file with a date and time stamp 
                DECLARE
                   @dt datetime ,
                   @SQLstmt varchar( 250 );
                SET @dt = GETDATE( );

                --  Important: double quotes ARE NEEDED around full filenames  from & to work in T-SQL   -- George 
                --  Important: double quotes ARE NEEDED around full filenames  from & to work in T-SQL   -- George 
                ---------------------------------------------------------------------------------------------------------------
                ----SET @SQLstmt = 'MOVE /Y  "' + @filename + '"' + '  "S:\ATSJobs\Archive\ATS_Archive' + CONVERT( varchar( 15 ) ,@dt ,112 ) + '_' + REPLACE( CONVERT( char( 5 ) ,GETDATE( ) ,108 ) ,':' ,'' ) + '.txt' + '"';
                ----EXEC xp_cmdshell @SQLstmt;
                ------------------------------
                DECLARE
                   @oFS int;
                DECLARE
                   @FS int;
                DECLARE
                   @oFol int;
                DECLARE
                   @oFC int;
                DECLARE
                   @ERR int;
                DECLARE
                   @FD int;
                DECLARE
                   @oFil int;
                DECLARE
                   @Path varchar( 255 );
                DECLARE
                   @Path1 varchar( 255 ); 
                --DECLARE @FileName VARCHAR(255) 
                DECLARE
                   @NumFiles int;
                DECLARE
                   @RetCode int;
                DECLARE
                   @ErrObject int; 
                ---
                DECLARE
                   @src varchar( 250 ) ,
                   @desc varchar( 2000 );
                DECLARE
                   @source varchar( 255 ) ,
                   @dest varchar( 255 );


                DECLARE
                   @newname varchar( 250 );
                SET @dt = GETDATE( );
                ---------------------------------------------------------------------------------------------------------------
                SET @newname = 'ATS_Archive' + CONVERT( varchar( 15 ) ,@dt ,112 ) + '_' + REPLACE( CONVERT( char( 5 ) ,GETDATE( ) ,108 ) ,':' ,'' ) + '.txt';
                EXEC @RetCode = sp_OACreate 'Scripting.FileSystemObject' ,@oFS OUTPUT;
                IF @RetCode <> 0
                    BEGIN
                        --   ROLLBACK TRANSACTION;
                        EXEC sp_OAGetErrorInfo @oFS ,@src OUT ,@desc OUT;
                        EXEC sp_SendCDOSysMail '[email protected]' ,'[email protected],[email protected]' ,'' ,'ATS - Unable to archive incoming file (1 Create)' ,'';
                        RAISERROR( 'Object Creation Failed 0x%x, %s, %s' ,16 ,1 ,@retcode ,@src ,@desc );
                        RETURN;
                    END;


                SET @Path = 'D:\ATS2EandEJobs\Archive\ATSToday.txt';
                SET @Path1 = 'D:\ATS2EandEJobs\Archive\' + @newname;

                --&SET @Path = 'E:\ATSJobs\Archive\ATSToday.txt';
                --&SET @Path1 = 'E:\ATSJobs\Archive\' + @newname;

                EXEC @RetCode = sp_OAMethod @oFS ,'GetFile' ,@oFol OUTPUT ,@Path;
                IF @RetCode <> 0
                    BEGIN
                        --  ROLLBACK TRANSACTION;
                        EXEC sp_SendCDOSysMail '
                        EXEC sp_OAGetErrorInfo @oFS ,@src OUT ,@desc OUT;
                        RAISERROR( 'OA Get File failed 0x%x, %s, %s' ,16 ,1 ,@retcode ,@src ,@desc );
                        RETURN;
                    END;


                -- http://www.vbforums.com/showthread.php?367306-Move-file-with-sp_OAMethod
                EXECUTE @err = sp_OAMethod @ofs ,'MoveFile' ,NULL ,@Path ,@Path1;
                IF @RetCode <> 0
                    BEGIN
                        --  ROLLBACK TRANSACTION;
                        EXEC sp_SendCDOSysMail 
                        EXEC sp_OAGetErrorInfo @oFS ,@src OUT ,@desc OUT;
                        RAISERROR( 'OA Move File Failed 0x%x, %s, %s' ,16 ,1 ,@retcode ,@src ,@desc );
                        RETURN;
                    END;

                ------------------------------                   


            END;

        -------------------------------------------------------------------------------------------------------------------               
        -----   DELETE ATSTODAY.TXT to be ready for next run - message already emailed above 
        -----   NOTHING TO ARCHIVE  - also for when Import job is accdentally re-run without the FTP preceding it 
        -------------------------------------------------------------------------------------------------------------------

        IF @TotalRecordsForImport
           = 
           0
            ------------AND @EmptyInput     - culprit ? 10/15   **&
            ------------    = 
            ------------    'Y'
            BEGIN
                SELECT @starttrancount = @@TRANCOUNT;
                IF @starttrancount
                   > 
                   0
                    BEGIN
                        ROLLBACK TRANSACTION;
                    END;    
                ---  create string to copy or rename file with a date and time stamp 
                --DECLARE
                --   @SQLstmt2 varchar( 250 );
                --SET @dt = GETDATE( );

                ----  Important: For Deletes quotes are NOT NEEEDED around filenames  to work in T-SQL  --  George 
                ----  Important: For Deletes quotes are NOT NEEEDED around filenames  to work in T-SQL  --  George
                -----------------------------------------------------------------------------------------------------------------               
                --SET @SQLstmt2 = 'del S:\ATSJobs\Archive\ATSToday.txt';
                --EXEC xp_cmdshell @SQLstmt2;
                ----EXEC sp_SendCDOSysMail 
                ----------------------------------------------------------------------------------------------------------------------------------
                -- using OLE to delete the file 
                DECLARE
                   @Result int;
                DECLARE
                   @FSO_Token int;
                EXEC @Result = sp_OACreate 'Scripting.FileSystemObject' ,@FSO_Token OUTPUT;
                EXEC @Result = sp_OAMethod @FSO_Token ,'DeleteFile' ,NULL ,'D:\ATS2EandEJobs\Archive\ATSToday.txt';
                --&EXEC @Result = sp_OAMethod @FSO_Token ,'DeleteFile' ,NULL ,'E:\ATSJobs\Archive\ATSToday.txt';
                EXEC @Result = sp_OADestroy @FSO_Token;

            END;
    END TRY
    BEGIN CATCH


        BEGIN
            SELECT @starttrancount = @@TRANCOUNT;
            IF @starttrancount
               > 
               0
                BEGIN
                    ROLLBACK TRANSACTION;
                END;                  
            ----  IF SP needs to be aborted for whatever reason 
            ----------- Delete input file if problem encountered -----------
            -- using OLE to delete the file 
            DECLARE
               @Result2 int;
            DECLARE
               @FSO_Token2 int;
            EXEC @Result = sp_OACreate 'Scripting.FileSystemObject' ,@FSO_Token2 OUTPUT;
            --EXEC @Result = sp_OAMethod @FSO_Token2 ,'DeleteFile' ,NULL ,'D:\ATS2EandEJobs\Archive\ATSToday.txt';
            --&EXEC @Result = sp_OAMethod @FSO_Token2 ,'DeleteFile' ,NULL ,'E:\ATSJobs\Archive\ATSToday.txt';
            EXEC @Result = sp_OADestroy @FSO_Token2;                    
            ----------- Delete input file if problem encountered -----------                    

            EXEC sp_SendCDOSysMail 
        END;

        DECLARE
           @ErrorNumber int ,
           @ErrorMessage nvarchar( 4000 ) ,
           @ErrorProcedure nvarchar( 4000 ) ,
           @ErrorLine int;


        SET @ErrorNumber = ERROR_NUMBER( );
        SET @ErrorMessage = ERROR_MESSAGE( );
        SET @ErrorProcedure = ERROR_PROCEDURE( );
        SET @ErrorLine = ERROR_LINE( );



        RAISERROR( 'An error occurred within transaction.
    Error Number  : %d
    Error Message : %s 
    Procedure   : %s
    Line Number : %d' ,16 ,1 ,@ErrorNumber ,@ErrorMessage ,@ErrorProcedure ,@ErrorLine );

    --------------   EXEC SP_SendICESErrorEmail @ErrorNumber ,@ErrorMessage ,@ErrorProcedure ,@ErrorLine ,'' ,'Daily ATS Import';
    END CATCH;




END;

END;

Answer 1

... even the agent job gets re-enabled on the secondary and run's in parallel is still a mystery.

In Availability Group environments, please do not use enabled and disabled jobs. The proper way to handle this is to check if the instance is currently the primary replica. If it is, continue with the job. If it isn't, complete with success.

Here is a small example using your current T-SQL Jobstep:

IF sys.fn_hadr_is_primary_replica('EEOutageTracking') = 1 Begin EXEC SP_ATSImport End

it should return "The database EEOutageTracking is not accessible. (ObjectExplorer)"

That's an error you would get if the database wasn't readable (offline, etc) or if the connection string wasn't properly set (aka ApplicationIntent). That's not the error you would get attempting to write to a readonly database which would be 3906:

Failed to update database %s because the database is read-only.

This error is severity 16 and should trigger the try/catch block.

which usually doubles the records being inserted after we FTP from an external machine.

This line is suspicious:

SET @filename = 'D:\ATS2EandEJobs\Archive\ATSToday.txt';

I don't know if it's local to the server (does the FTP program really know who is primary?) or if it is a "local" share/Dfs. If that server can "see" the file, it'll probably try to run as per normal.

While I highly doubt synonyms are used, I never want to assume.

Additional Thoughts

It would make sense to run this with some debug output/tracing. Put some print statements around your internal checks. I can only theorize the code path it might take not knowing the environment.

If you don't want to go through that, doing the checks for your agent job steps as I have above will stop this from happening.