We have the AO Clustering - HA&DR Hybrid configuration. Out of dozen databases only this one database/application is encountering this.
The dilemma 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 the to 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 cant 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 the agent job gets re-enabled on the secondary and run's in parallel is still a mystery. when it should return "The database EEOutageTracking is not accessible. (ObjectExplorer)" . its a 1-step sql server agent job exec SP_ATSImport ----- how can I attach/paste the code ?
[![enter image description here][1]][1]
[1]: https://i.sstatic.net/ECluL.jpg
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;