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 'EandEATSImport@nyct.com' ,'george.lewycky@nyct.com,brian.tabb@nyct.com,Mathew.Varghese3@nyct.com,Shahreen.Ali@nyct.com,Yefim.Kogan@nyct.com' ,'' ,'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 'EandEATSImport@nyct.com' ,'george.lewycky@nyct.com,brian.tabb@nyct.com,Mathew.Varghese3@nyct.com,Shahreen.Ali@nyct.com,Yefim.Kogan@nyct.com' ,'' ,'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 'EandEATSImport@nyct.com' ,'george.lewycky@nyct.com,brian.tabb@nyct.com' ,'' ,'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;
Best Answer
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 EndThat'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.
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.