INSERT INTO Reporting.tblServerMonitor(ServerMonitorID, FK_tblServers_ServerID, ServerStatus, ServerTimeStamp)
	
	SELECT ServerMonitorID, FK_tblServers_ServerID, ServerStatus, ServerTimeStamp
	FROM ServerService.tblServerMonitor
	WHERE ArchiveBit = 0

-- ====================================================
-- Reset ArchiveBit to 1 for Archived Records
-- ====================================================
	UPDATE ServerService.tblServerMonitor
	SET ArchiveBit = 1
	WHERE ArchiveBit = 0

-- ====================================================
-- Update Server Monitor Record for ServerDriveID
-- ====================================================
	UPDATE ServerService.tblServerMonitor
	SET ServerStatus = 1, ServerTimeStamp = GETDATE(), ArchiveBit = 0
	WHERE ServerMonitorID = 1

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim strCmd As String = "ServerService.ServerMonitorUpdate"
        Dim myConnStr As String = System.Configuration.ConfigurationManager.ConnectionStrings("ServerConnString").ConnectionString
        Dim myConn As New SqlConnection(myConnStr)
        Dim myColl As New NameValueCollection

        myColl = Request.QueryString

        Using mySQLCmd As New SqlCommand(strCmd, myConn)
            myConn.Open()

            mySQLCmd.CommandType = CommandType.StoredProcedure
            mySQLCmd.CommandText = strCmd

            mySQLCmd.Parameters.Add("@ServerMonitorID", SqlDbType.Int).Value = myColl("ServerID")

            mySQLCmd.ExecuteNonQuery()
        End Using

    End Sub

/****** Object:  StoredProcedure [ServerService].[ServerMonitorUpdate]    

Script Date: 09/07/2010 21:25:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





-- ======================================================
-- Author:	Me
-- Create date: 19/06/2010
-- Description:	Updates the Server record in the ServerMonitor table
-- Change:      Added Archive Procedure on 24/08/2010
-- ======================================================
ALTER PROCEDURE [ServerService].[ServerMonitorUpdate]
	-- Add the parameters for the stored procedure here
		@ServerMonitorID		Int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;

-- ====================================================
-- Archive Records with ArchiveBit = 0
-- ====================================================
	INSERT INTO Reporting.tblServerMonitor(ServerMonitorID, FK_tblServers_ServerID, ServerStatus, ServerTimeStamp)
	
	(SELECT ServerMonitorID, FK_tblServers_ServerID, ServerStatus, ServerTimeStamp
	FROM ServerService.tblServerMonitor
	WHERE ArchiveBit = 0)

-- ====================================================
-- Reset ArchiveBit to 1 for Archived Records
-- ====================================================
	UPDATE ServerService.tblServerMonitor
	SET ArchiveBit = 1
	WHERE ArchiveBit = 0
-- ====================================================
-- Update Server Monitor Record for ServerDriveID
-- ====================================================
	UPDATE ServerService.tblServerMonitor
	SET ServerStatus = 1, ServerTimeStamp = GETDATE(), ArchiveBit = 0
	WHERE ServerMonitorID = @ServerMonitorID


END


GO

quote:

---

Originally posted by cduance

Permissions :(

Due to taking data from one table in schema A and trying to insert into schema B

---