<?xml version="1.0"?>
<configuration>
    <configSections>
        <sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
            <section name="CardAccess.ArchiveDataAccess.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
        </sectionGroup>
    </configSections>
    <applicationSettings>
        <CardAccess.ArchiveDataAccess.Properties.Settings>
            <setting name="GetArchiveSettings" serializeAs="String">
                <value>Select ArchiveConfigServer,ArchiveConfigDatabase,ArchiveEventsServer,ArchiveEventsDatabase,DBFileSize,EventsToRetain,EventsInArchiveDatabase,LastUpdated From ArchiveSettings</value>
            </setting>
            <setting name="MoveAuditSql" serializeAs="String">
                <value>ALTER PROCEDURE  [dbo].[ca_sp_ArchiveAuditTrail] @Server varchar(150), @Database varchar(150),@RetainCount int, @MovedCount int output
AS
BEGIN
DECLARE	 @sql nVarChar(500)
		,@TotalCount int
		,@ArchiveCount int = 0

	SET @sql=N'select @totalCount = COUNT(*) FROM OPENQUERY( "'+ @Server +'", ''SELECT  [AuditId]
				,CAST(OldData AS nvarchar(max)) OldData 
				,CAST(NewData AS nvarchar(max)) NewData
				FROM ['+@Database+'].[dbo].[DBAudit] ORDER BY [RevisionStamp]'')'
	EXEC sp_executesql @sql, N'@TotalCount int output', @TotalCount OUTPUT

	IF(@TotalCount &gt; @RetainCount)
	BEGIN
		SET @ArchiveCount = @TotalCount - @RetainCount
		IF (@ArchiveCount &gt; 1000) SET @ArchiveCount = 1000	
	END

	IF(@ArchiveCount &gt; 0)
	BEGIN
		CREATE TABLE [#tempDBAudit](
		[AuditId] [int] NOT NULL PRIMARY KEY,
		[RevisionStamp] [datetime] NULL,[TableName] [nvarchar](50) NULL,[OperatorName] [nvarchar](50) NULL,
		[StationName] [nvarchar](50) NULL,[Actions] [nvarchar](1) NULL,[Description] [nvarchar](200) NULL,[OldData] [xml] NULL,
		[NewData] [xml] NULL,[ChangedColumns] [nvarchar](1000) NULL,[LastOperator] [uniqueidentifier] NULL,[caObjectID] [uniqueidentifier] NULL,
		[UTCOffset] [int] NULL,[ParentAuditId] [int] NULL)

		BEGIN TRANSACTION TranAudit
		BEGIN TRY
			EXEC ('INSERT INTO [#tempDBAudit] 
						([AuditId],[RevisionStamp],[TableName],[OperatorName],[StationName],[Actions],[Description]
						,[OldData]
						,[NewData]
						,[ChangedColumns],[LastOperator],[caObjectID],[UTCOffset],[ParentAuditId])
				   SELECT  [AuditId],[RevisionStamp],[TableName],[OperatorName],[StationName],[Actions],[Description]
				   		,CAST(OldData AS XML) OldData
				   		,CAST(NewData AS XML) NewData
				   		,[ChangedColumns],[LastOperator],[caObjectID],[UTCOffset],[ParentAuditId] 
				   FROM OPENQUERY("'+@Server +'", ''SELECT TOP '+@ArchiveCount+' [AuditId], [RevisionStamp],[TableName],[OperatorName],[StationName],[Actions],[Description]
				   		,CAST(OldData AS nvarchar(max)) OldData 
				   		,CAST(NewData AS nvarchar(max)) NewData
				   		,[ChangedColumns],[LastOperator],[caObjectID],[UTCOffset],[ParentAuditId]
				   FROM ['+@Database+'].[dbo].[DBAudit] ORDER BY [RevisionStamp]'')');
			
			EXECUTE('INSERT INTO [DBAudit] ([AuditId],[RevisionStamp],[TableName],[OperatorName],[StationName],[Actions],[Description],[OldData],[NewData]
				   ,[ChangedColumns],[LastOperator],[caObjectID],[UTCOffset],[ParentAuditId])
					SELECT [AuditId],[RevisionStamp],[TableName],[OperatorName],[StationName],[Actions],[Description],CAST(OldData AS nvarchar(max)) OldData 
					,CAST(NewData AS nvarchar(max)) NewData,[ChangedColumns],[LastOperator],[caObjectID],[UTCOffset],[ParentAuditId]
				    FROM [#tempDBAudit] WHERE NOT EXISTS (SELECT 1 from [DBAudit] WHERE [#tempDBAudit].[AuditId]= [AuditId])')

			IF @@SERVERNAME = @Server
				 Exec ('DELETE FROM ['+@Database+'].[dbo].[DBAudit] WHERE EXISTS (SELECT 1 from [#tempDBAudit] WHERE [#tempDBAudit].[AuditId]= [DBAudit].[AuditId])')
		 
			COMMIT TRANSACTION TranAudit

			IF @@SERVERNAME &lt;&gt; @Server
			BEGIN
				EXEC ('DELETE OPENQUERY ("'+@Server +'", ''SELECT TOP '+@ArchiveCount+' [RevisionStamp]
					  ,[TableName]
					  ,[OperatorName]
					  ,[StationName]
					  ,[Actions]
					  ,[Description]
					  ,CAST(OldData AS nvarchar(max)) OldData 
					  ,CAST(NewData AS nvarchar(max)) NewData
					  ,[ChangedColumns]
					  ,[LastOperator]
					  ,[caObjectID]
					  ,[UTCOffset] 
					  FROM ['+@Database+'].[dbo].[DBAudit]  
					  ORDER BY [RevisionStamp]'')');
			END

		END TRY
		BEGIN CATCH
			Print ERROR_MESSAGE();

			IF @@SERVERNAME = @Server
				ROLLBACK TRANSACTION TranAudit
			ELSE
				Exec ('DELETE FROM [DBAudit] WHERE EXISTS (SELECT 1 from [#tempDBAudit] WHERE [#tempDBAudit].[AuditId]= [DBAudit].[AuditId])')

			SET @ArchiveCount = -1

		END CATCH

		DROP TABLE [#tempDBAudit]
	END

	SET @MovedCount = @ArchiveCount
END
</value>
            </setting>
        </CardAccess.ArchiveDataAccess.Properties.Settings>
    </applicationSettings>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>
  <runtime>
    <NetFx40_LegacySecurityPolicy enabled="true"/>
  </runtime>
</configuration>
