How to transfer logins and passwords between instances of SQL Server

http://support.microsoft.com/kb/918992

EMP Resource center

http://www.oracle.com/technetwork/middleware/bi-foundation/resource-library-090986.html

Peoplesoft scheduler monitoring scripts for MSSQL Server

PSFINPRD_check_process - To check if any process is processing or in initiated status for over 60 minutes
Text
-------------------
-- exec PSFINPRD_check_process 60
-- Deep Pandey 6/11/2013
CREATE PROCEDURE PSFINPRD_check_process
@timediff int
AS
DECLARE @find1 int;
DECLARE @prcsid VARCHAR(50);
DECLARE @tableHTML  NVARCHAR(MAX);

DECLARE Process_list CURSOR FOR SELECT PRCSINSTANCE FROM PSPRCSRQST
WHERE RUNSTATUS IN (6, 7)
AND DATEDIFF(MINUTE,BEGINDTTM, GETDATE()) > @timediff ;

IF OBJECT_ID('tempdb..#TempTable_PRCSList','u') IS NOT NULL
BEGIN
DROP TABLE #TempTable_PRCSList;
END
ELSE
BEGIN
CREATE TABLE #TempTable_PRCSList
(PRCSINSTANCE1 varchar(50),JOBINSTANCE1  varchar(50), PRCSTYPE1 varchar(50),
PRCSNAME1  varchar(50), SERVERNAMERUN1 varchar(50),Time_in_Minutes  varchar(50), RUNSTATUS1 varchar(50));
END
OPEN Process_list;

FETCH NEXT FROM Process_list INTO @prcsid;
WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @find1 = (SELECT DATEDIFF(MINUTE,BEGINDTTM, GETDATE())
      FROM PSPRCSRQST
      WHERE RUNSTATUS IN (6, 7)
      AND DATEDIFF(MINUTE,BEGINDTTM, GETDATE()) > @timediff
      AND PRCSINSTANCE =@prcsid)
     
INSERT INTO #TempTable_PRCSList(PRCSINSTANCE1, JOBINSTANCE1,PRCSTYPE1,PRCSNAME1,SERVERNAMERUN1,Time_in_Minutes, RUNSTATUS1 )
SELECT PRCSINSTANCE, JOBINSTANCE, PRCSTYPE, PRCSNAME,SERVERNAMERUN,
DATEDIFF(MINUTE,BEGINDTTM, GETDATE()) Time_in_Minutes,
CASE WHEN
RUNSTATUS = 7 THEN 'Processing'
WHEN RUNSTATUS = 6 THEN  'Initiated'
END AS RUNSTATUS
FROM PSPRCSRQST
WHERE RUNSTATUS IN (6, 7)
AND PRCSINSTANCE =@prcsid
AND DATEDIFF(MINUTE,BEGINDTTM, GETDATE()) > @timediff
FETCH NEXT FROM Process_list INTO @prcsid;
END;
SET @tableHTML =
    N'<h1>Please check Finance scheduler for following process(s) running over 60 minutes now</h1>'
  + N'<table border="1">'
  + CAST ( ( (
   SELECT + 'Servername: ',  td = @@SERVERNAME
  for xml path('tr'), TYPE)

    ) AS NVARCHAR(MAX) )
  + CAST ( ( (
   SELECT + 'Database Name: ', td = DB_NAME()
  for xml path('tr'), TYPE)

    ) AS NVARCHAR(MAX) )
  + CAST ( ( (
   SELECT 'td/@bgcolor' = '#FF3366', td =
  PRCSINSTANCE1 + ' process Instance executing ' + ' ', + PRCSNAME1+  ' ' + PRCSTYPE1 + ' ' + ' on' + ' ' +
  SERVERNAMERUN1 + '' + ' server from last ' + '' + Time_in_Minutes + '' +' minutes' , ''+ ' is in ' + '' + RUNSTATUS1 +'' + ' status'
FROM #TempTable_PRCSList
  for xml path('tr'), TYPE)

    ) AS NVARCHAR(MAX) ) +
    N'</table>' +
    N'<br/>'
  + N'<tr><th><br> </br></th></tr>'
  + N'<tr><th><br> </br></th></tr>'
  + N'<tr><th><br>Thanks!</br></th></tr>'
  + N'<tr><th><br>BusApps Admin.</br></th></tr>'
  + N'<br/>'  ;
if (@find1 > @timediff)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'NSAXBPFNDB01P',
@recipients = 'you@me.com',
@importance = high,
@body = @tableHTML,
@body_format = 'HTML',
@subject = 'Process(s) running longer than 60 minutes' ;
END
CLOSE Process_list;
DEALLOCATE Process_list;



Text - Check is process scheduler is down.
----------------------
-- exec PSFINPRD_checkschedulerstatus 1
-- Deep Pandey 6/11/2013
CREATE PROCEDURE PSFINPRD_checkschedulerstatus
@timediff int
AS
DECLARE @find1 int;
DECLARE @name VARCHAR(50);
DECLARE @tableHTML  NVARCHAR(MAX);

DECLARE sCHEDULER_LIST CURSOR FOR SELECT SERVERNAME FROM PSSERVERSTAT
WHERE DATEDIFF(MINUTE,LASTUPDDTTM, GETDATE()) > @timediff;

IF OBJECT_ID('tempdb..#TempTable_PRCS','u') IS NOT NULL
BEGIN
DROP TABLE #TempTable_PRCS;
END
ELSE
BEGIN
CREATE TABLE #TempTable_PRCS (Servername varchar(50),downtime  varchar(50));
END
OPEN sCHEDULER_LIST;

FETCH NEXT FROM sCHEDULER_LIST INTO @name;

WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @find1 = (SELECT DATEDIFF(MINUTE,S.LASTUPDDTTM, GETDATE())LAST_UPDATE_TIME
FROM PSSERVERSTAT S, PSXLATITEM X
WHERE X.FIELDNAME = 'SERVERSTATUS'
AND X.FIELDVALUE = S.SERVERSTATUS
and S.SERVERNAME =@name
AND DATEDIFF(MINUTE,S.LASTUPDDTTM, GETDATE()) > @timediff)
INSERT INTO #TempTable_PRCS(downtime, Servername ) SELECT DATEDIFF(minute,S.LASTUPDDTTM, GETDATE()), S.SERVERNAME
FROM PSSERVERSTAT S, PSXLATITEM X
WHERE X.FIELDNAME = 'SERVERSTATUS'
AND X.FIELDVALUE = S.SERVERSTATUS
AND S.SERVERNAME = @name
AND DATEDIFF(MINUTE,S.LASTUPDDTTM, GETDATE()) > @timediff
FETCH NEXT FROM sCHEDULER_LIST INTO @name;
END;
SET @tableHTML =
    N'<h1>Please check Finance scheduler for issues</h1>'
  + N'<table border="1">'
  + CAST ( ( (
  SELECT 'td/@bgcolor' = '#FF3366', td = Servername + 'Scheduler has not been refreshed since ', '' , downtime + ' minutes. Please check.' , ''
--SELECT td = Servername + 'Scheduler has not been refreshed since ', '' , downtime + ' minutes. Please check.' , ''
FROM #TempTable_PRCS
--where Servername = @name
  for xml path('tr'), TYPE)

    ) AS NVARCHAR(MAX) ) +
    N'</table>' +
    N'<br/>'
  + N'<tr><th><br> </br></th></tr>'
  + N'<tr><th><br> </br></th></tr>'
  + N'<tr><th><br>Thanks!</br></th></tr>'
  + N'<tr><th><br>BusApps Admin.</br></th></tr>'
  + N'<br/>'  ;
if (@find1 > @timediff)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'NSAXBPFNDB01P',
@recipients = 'me@you.com',
@importance = high,
@body = @tableHTML,
@body_format = 'HTML',
@subject = 'Finance Production Process Scheduler Alert' ;
END
CLOSE sCHEDULER_LIST;
DEALLOCATE sCHEDULER_LIST;