|
Closed
|
Case #
|
10017
|
|
Affiliated Job:
|
New Trier Township District 2031
|
|
|
Opened:
|
Unknown
|
|
Closed:
|
Unknown
|
|
Total Hit Count:
|
37442
|
|
Last Hit:
|
Friday, April 19, 2024 9:04:04 AM
|
|
Unique Hit Count:
|
8865
|
|
Last Unique Hit:
|
Friday, April 19, 2024 9:04:04 AM
|
|
Case Type(s):
|
Database, Server
|
|
|
Case Notes(s):
|
All cases are posted for review purposes only. Any implementations should be performed at your own risk.
|
|
|
|
Project:
|
Shortly after I started at New Trier, we lost our MIS Manager who had a variety of manually run synchronization routines in place to carry data between our many environments. I was tasked to review these integrations and either get them running or improve upon them.
I took his manually run routines, which were being performed through a Windows based Linux emulator which ran scripts that pulled data into temporary tables, adjusted the data and then carried that data into the destination system, and revised them into a single query with join statements that in the query "massaged" the data for the destination environments.
|
|
|
Action(s) Performed:
|
|
Total Action(s): 2
|
|
Action #
|
Recorded Date
|
Type
|
Hit(s)
|
User
|
Expand Details
|
|
10064
|
2/16/2010 2:58:46 PM
|
Database
|
3507
|
contact@danieljchu.com
|
We have internal and also outsourced hosted, almost all web products which More ...
|
|
10068
|
2/16/2010 2:35:29 PM
|
Database
|
3442
|
contact@danieljchu.com
|
Sample A.D. Query to Batch below, this assembles data from the eSchool stud Collapse ...
|
Last Hit: Friday, April 19, 2024 9:03:55 AM
Sample A.D. Query to Batch below, this assembles data from the eSchool student records with memberships in our user management system.
Query to Create Sync A.D. Batch [update-ad.bat]:
SELECT '@ECHO OFF' AS CMD, 0.1 AS Priority UNION
SELECT '@ECHO ----------- >> \\SQLSRV01\e$\_Batch\00-Logs\log.log' AS CMD, 0.2 AS Priority UNION
SELECT '@ECHO eSchool to AD Sync Start %DATE% %TIME% >> \\SQLSRV01\e$\_Batch\00-Logs\log.log' AS CMD, 0.3 AS Priority UNION
SELECT 'dsquery user -samid ' + LTRIM(RTRIM(A.STUDENT_ID)) + ' | dsmod group "CN=All Grade 09 Students,OU=Groups,OU=School-Wide,DC=somedomain,DC=net" -rmmbr' AS CMD, 1 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10','11','12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsquery user -samid ' + LTRIM(RTRIM(A.STUDENT_ID)) + ' | dsmod group "CN=All Grade 10 Students,OU=Groups,OU=School-Wide,DC=somedomain,DC=net" -rmmbr' AS CMD, 1 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09','11','12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsquery user -samid ' + LTRIM(RTRIM(A.STUDENT_ID)) + ' | dsmod group "CN=All Grade 11 Students,OU=Groups,OU=School-Wide,DC=somedomain,DC=net" -rmmbr' AS CMD, 1 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09','10','12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsquery user -samid ' + LTRIM(RTRIM(A.STUDENT_ID)) + ' | dsmod group "CN=All Grade 12 Students,OU=Groups,OU=School-Wide,DC=somedomain,DC=net" -rmmbr' AS CMD, 1 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09','10','11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsquery user -samid ' + LTRIM(RTRIM(A.STUDENT_ID)) + ' | dsmove -newparent "OU=Grade_09,OU=Student,OU=Northfield,DC=somedomain,DC=net"' AS CMD, 2 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsquery user -samid ' + LTRIM(RTRIM(A.STUDENT_ID)) + ' | dsmove -newparent "OU=Grade_10,OU=Student,OU=Winnetka,DC=somedomain,DC=net"' AS CMD, 2 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsquery user -samid ' + LTRIM(RTRIM(A.STUDENT_ID)) + ' | dsmove -newparent "OU=Grade_11,OU=Student,OU=Winnetka,DC=somedomain,DC=net"' AS CMD, 2 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsquery user -samid ' + LTRIM(RTRIM(A.STUDENT_ID)) + ' | dsmove -newparent "OU=Grade_12,OU=Student,OU=Winnetka,DC=somedomain,DC=net"' AS CMD, 2 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsquery user -samid ' + LTRIM(RTRIM(A.STUDENT_ID)) + ' | dsmod user -fn "' + LTRIM(RTRIM(A.FIRST_NAME)) + '" -ln "' + LTRIM(RTRIM(A.LAST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN '" -mi "' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + '" -display "' + LTRIM(RTRIM(A.LAST_NAME)) + ', ' + LTRIM(RTRIM(A.FIRST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN ' ' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + '" -desc "' + CASE WHEN NOT C.STAFF_ID IS NULL THEN C.LAST_NAME + ', ' + C.FIRST_NAME ELSE 'Unknown' END + '" -office "' + CASE WHEN A.BUILDING = '1' THEN 'Northfield' ELSE 'Winnetka' END + '" -email "' + CASE WHEN NOT I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS IS NULL AND NOT LEN(LTRIM(RTRIM(I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS))) = 0 THEN I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS ELSE LTRIM(RTRIM(CONVERT(VARCHAR(20),A.STUDENT_ID))) + '@student.somedomain.net' END + '" -title "Student" -dept "' + CONVERT(VARCHAR(4),B.GRADUATION_YEAR) + '" -company "New Trier Township High School" -loscr "LogonScript\NTLogon.vbs" -mustchpwd no -pwdneverexpires yes -disabled no' AS CMD, 3 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
LEFT JOIN OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(H.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, UPPER(LTRIM(RTRIM(H.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS))) AS PersonalEmail FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS H') AS I ON (LTRIM(RTRIM(A.STUDENT_ID)) = LTRIM(RTRIM(I.UserID COLLATE SQL_Latin1_General_CP1_CS_AS)))
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09','10','11','12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsadd user "CN=' + LTRIM(RTRIM(A.FIRST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN ' ' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + ' ' + LTRIM(RTRIM(A.LAST_NAME)) + ',OU=Grade_09,OU=Student,OU=Northfield,DC=somedomain,DC=net" -samid "' + LTRIM(RTRIM(A.STUDENT_ID)) + '" -upn "' + LTRIM(RTRIM(A.STUDENT_ID)) + '@somedomain.net" -fn "' + LTRIM(RTRIM(A.FIRST_NAME)) + '" -ln "' + LTRIM(RTRIM(A.LAST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN '" -mi "' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + '" -display "' + LTRIM(RTRIM(A.LAST_NAME)) + ', ' + LTRIM(RTRIM(A.FIRST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN ' ' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + '" -pwd "' + CASE WHEN LEN(DATEPART(mm,A.BIRTHDATE)) < 2 THEN '0' ELSE '' END + CONVERT(VARCHAR(2),DATEPART(mm,A.BIRTHDATE)) + CASE WHEN LEN(DATEPART(dd,A.BIRTHDATE)) < 2 THEN '0' ELSE '' END + CONVERT(VARCHAR(2),DATEPART(dd,A.BIRTHDATE)) + CONVERT(VARCHAR(4),DATEPART(yyyy,A.BIRTHDATE)) + '" -desc "' + CASE WHEN NOT C.STAFF_ID IS NULL THEN C.LAST_NAME + ', ' + C.FIRST_NAME ELSE 'Unknown' END + '" -office "' + CASE WHEN A.BUILDING = '1' THEN 'Northfield' ELSE 'Winnetka' END + '" -email "' + CASE WHEN NOT I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS IS NULL AND NOT LEN(LTRIM(RTRIM(I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS))) = 0 THEN I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS ELSE LTRIM(RTRIM(CONVERT(VARCHAR(20),A.STUDENT_ID))) + '@student.somedomain.net' END + '" -title "Student" -dept "' + CONVERT(VARCHAR(4),B.GRADUATION_YEAR) + '" -company "New Trier Township High School" -loscr "LogonScript\NTLogon.vbs" -mustchpwd no -pwdneverexpires yes' AS CMD, 4 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
LEFT JOIN OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(H.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, UPPER(LTRIM(RTRIM(H.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS))) AS PersonalEmail FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS H') AS I ON (LTRIM(RTRIM(A.STUDENT_ID)) = LTRIM(RTRIM(I.UserID COLLATE SQL_Latin1_General_CP1_CS_AS)))
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsadd user "CN=' + LTRIM(RTRIM(A.FIRST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN ' ' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + ' ' + LTRIM(RTRIM(A.LAST_NAME)) + ',OU=Grade_10,OU=Student,OU=Winnetka,DC=somedomain,DC=net" -samid "' + LTRIM(RTRIM(A.STUDENT_ID)) + '" -upn "' + LTRIM(RTRIM(A.STUDENT_ID)) + '@somedomain.net" -fn "' + LTRIM(RTRIM(A.FIRST_NAME)) + '" -ln "' + LTRIM(RTRIM(A.LAST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN '" -mi "' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + '" -display "' + LTRIM(RTRIM(A.LAST_NAME)) + ', ' + LTRIM(RTRIM(A.FIRST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN ' ' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + '" -pwd "' + CASE WHEN LEN(DATEPART(mm,A.BIRTHDATE)) < 2 THEN '0' ELSE '' END + CONVERT(VARCHAR(2),DATEPART(mm,A.BIRTHDATE)) + CASE WHEN LEN(DATEPART(dd,A.BIRTHDATE)) < 2 THEN '0' ELSE '' END + CONVERT(VARCHAR(2),DATEPART(dd,A.BIRTHDATE)) + CONVERT(VARCHAR(4),DATEPART(yyyy,A.BIRTHDATE)) + '" -desc "' + CASE WHEN NOT C.STAFF_ID IS NULL THEN C.LAST_NAME + ', ' + C.FIRST_NAME ELSE 'Unknown' END + '" -office "' + CASE WHEN A.BUILDING = '1' THEN 'Northfield' ELSE 'Winnetka' END + '" -email "' + CASE WHEN NOT I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS IS NULL AND NOT LEN(LTRIM(RTRIM(I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS))) = 0 THEN I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS ELSE LTRIM(RTRIM(CONVERT(VARCHAR(20),A.STUDENT_ID))) + '@student.somedomain.net' END + '" -title "Student" -dept "' + CONVERT(VARCHAR(4),B.GRADUATION_YEAR) + '" -company "New Trier Township High School" -loscr "LogonScript\NTLogon.vbs" -mustchpwd no -pwdneverexpires yes' AS CMD, 4 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
LEFT JOIN OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(H.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, UPPER(LTRIM(RTRIM(H.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS))) AS PersonalEmail FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS H') AS I ON (LTRIM(RTRIM(A.STUDENT_ID)) = LTRIM(RTRIM(I.UserID COLLATE SQL_Latin1_General_CP1_CS_AS)))
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsadd user "CN=' + LTRIM(RTRIM(A.FIRST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN ' ' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + ' ' + LTRIM(RTRIM(A.LAST_NAME)) + ',OU=Grade_11,OU=Student,OU=Winnetka,DC=somedomain,DC=net" -samid "' + LTRIM(RTRIM(A.STUDENT_ID)) + '" -upn "' + LTRIM(RTRIM(A.STUDENT_ID)) + '@somedomain.net" -fn "' + LTRIM(RTRIM(A.FIRST_NAME)) + '" -ln "' + LTRIM(RTRIM(A.LAST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN '" -mi "' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + '" -display "' + LTRIM(RTRIM(A.LAST_NAME)) + ', ' + LTRIM(RTRIM(A.FIRST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN ' ' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + '" -pwd "' + CASE WHEN LEN(DATEPART(mm,A.BIRTHDATE)) < 2 THEN '0' ELSE '' END + CONVERT(VARCHAR(2),DATEPART(mm,A.BIRTHDATE)) + CASE WHEN LEN(DATEPART(dd,A.BIRTHDATE)) < 2 THEN '0' ELSE '' END + CONVERT(VARCHAR(2),DATEPART(dd,A.BIRTHDATE)) + CONVERT(VARCHAR(4),DATEPART(yyyy,A.BIRTHDATE)) + '" -desc "' + CASE WHEN NOT C.STAFF_ID IS NULL THEN C.LAST_NAME + ', ' + C.FIRST_NAME ELSE 'Unknown' END + '" -office "' + CASE WHEN A.BUILDING = '1' THEN 'Northfield' ELSE 'Winnetka' END + '" -email "' + CASE WHEN NOT I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS IS NULL AND NOT LEN(LTRIM(RTRIM(I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS))) = 0 THEN I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS ELSE LTRIM(RTRIM(CONVERT(VARCHAR(20),A.STUDENT_ID))) + '@student.somedomain.net' END + '" -title "Student" -dept "' + CONVERT(VARCHAR(4),B.GRADUATION_YEAR) + '" -company "New Trier Township High School" -loscr "LogonScript\NTLogon.vbs" -mustchpwd no -pwdneverexpires yes' AS CMD, 4 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
LEFT JOIN OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(H.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, UPPER(LTRIM(RTRIM(H.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS))) AS PersonalEmail FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS H') AS I ON (LTRIM(RTRIM(A.STUDENT_ID)) = LTRIM(RTRIM(I.UserID COLLATE SQL_Latin1_General_CP1_CS_AS)))
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsadd user "CN=' + LTRIM(RTRIM(A.FIRST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN ' ' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + ' ' + LTRIM(RTRIM(A.LAST_NAME)) + ',OU=Grade_12,OU=Student,OU=Winnetka,DC=somedomain,DC=net" -samid "' + LTRIM(RTRIM(A.STUDENT_ID)) + '" -upn "' + LTRIM(RTRIM(A.STUDENT_ID)) + '@somedomain.net" -fn "' + LTRIM(RTRIM(A.FIRST_NAME)) + '" -ln "' + LTRIM(RTRIM(A.LAST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN '" -mi "' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + '" -display "' + LTRIM(RTRIM(A.LAST_NAME)) + ', ' + LTRIM(RTRIM(A.FIRST_NAME)) + CASE WHEN NOT A.MIDDLE_NAME IS NULL AND NOT LEN(LTRIM(RTRIM(A.MIDDLE_NAME))) = 0 THEN ' ' + SUBSTRING(LTRIM(RTRIM(A.MIDDLE_NAME)),1,1) ELSE '' END + '" -pwd "' + CASE WHEN LEN(DATEPART(mm,A.BIRTHDATE)) < 2 THEN '0' ELSE '' END + CONVERT(VARCHAR(2),DATEPART(mm,A.BIRTHDATE)) + CASE WHEN LEN(DATEPART(dd,A.BIRTHDATE)) < 2 THEN '0' ELSE '' END + CONVERT(VARCHAR(2),DATEPART(dd,A.BIRTHDATE)) + CONVERT(VARCHAR(4),DATEPART(yyyy,A.BIRTHDATE)) + '" -desc "' + CASE WHEN NOT C.STAFF_ID IS NULL THEN C.LAST_NAME + ', ' + C.FIRST_NAME ELSE 'Unknown' END + '" -office "' + CASE WHEN A.BUILDING = '1' THEN 'Northfield' ELSE 'Winnetka' END + '" -email "' + CASE WHEN NOT I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS IS NULL AND NOT LEN(LTRIM(RTRIM(I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS))) = 0 THEN I.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS ELSE LTRIM(RTRIM(CONVERT(VARCHAR(20),A.STUDENT_ID))) + '@student.somedomain.net' END + '" -title "Student" -dept "' + CONVERT(VARCHAR(4),B.GRADUATION_YEAR) + '" -company "New Trier Township High School" -loscr "LogonScript\NTLogon.vbs" -mustchpwd no -pwdneverexpires yes' AS CMD, 4 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
LEFT JOIN OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(H.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, UPPER(LTRIM(RTRIM(H.PersonalEmail COLLATE SQL_Latin1_General_CP1_CS_AS))) AS PersonalEmail FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS H') AS I ON (LTRIM(RTRIM(A.STUDENT_ID)) = LTRIM(RTRIM(I.UserID COLLATE SQL_Latin1_General_CP1_CS_AS)))
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsquery user -samid ' + LTRIM(RTRIM(A.STUDENT_ID)) + ' | dsmod group "CN=All Grade 09 Students,OU=Groups,OU=School-Wide,DC=somedomain,DC=net" -addmbr' AS CMD, 5 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsquery user -samid ' + LTRIM(RTRIM(A.STUDENT_ID)) + ' | dsmod group "CN=All Grade 10 Students,OU=Groups,OU=School-Wide,DC=somedomain,DC=net" -addmbr' AS CMD, 5 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsquery user -samid ' + LTRIM(RTRIM(A.STUDENT_ID)) + ' | dsmod group "CN=All Grade 11 Students,OU=Groups,OU=School-Wide,DC=somedomain,DC=net" -addmbr' AS CMD, 5 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsquery user -samid ' + LTRIM(RTRIM(A.STUDENT_ID)) + ' | dsmod group "CN=All Grade 12 Students,OU=Groups,OU=School-Wide,DC=somedomain,DC=net" -addmbr' AS CMD, 5 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'dsquery user -samid "' + LTRIM(RTRIM(B.UserID COLLATE SQL_Latin1_General_CP1_CS_AS)) + '" | dsmod user -disabled yes' AS CMD, 6 AS Priority
FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(A.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS A WHERE Disabled = 0') AS B
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG AS C ON (LTRIM(RTRIM(B.UserID COLLATE SQL_Latin1_General_CP1_CS_AS)) = LTRIM(RTRIM(C.STUDENT_ID)))
WHERE (NOT C.CURRENT_STATUS = 'A' OR C.CURRENT_STATUS IS NULL)
AND (SUBSTRING(B.UserID,1,3) = '200' OR SUBSTRING(B.UserID,1,3) = '201')
AND (NOT B.UserID = '20109999' AND NOT B.UserID = '20119999' AND NOT B.UserID = '20129999' AND NOT B.UserID = '20139999' AND NOT B.UserID = '20149999' AND NOT B.UserID = '30009999')
UNION
SELECT 'repadmin /syncall dc1 DC=somedomain,DC=net' AS CMD, 6.1 AS Priority UNION
SELECT 'repadmin /syncall dc2 DC=somedomain,DC=net' AS CMD, 6.2 AS Priority UNION
SELECT 'repadmin /syncall dc3 DC=somedomain,DC=net' AS CMD, 6.3 AS Priority UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" XCOPY "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /E /C /R /I /H /O /X /Y' AS CMD, 7 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" XCOPY "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /E /C /R /I /H /O /X /Y' AS CMD, 7 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" XCOPY "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /E /C /R /I /H /O /X /Y' AS CMD, 7 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" XCOPY "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /E /C /R /I /H /O /X /Y' AS CMD, 7 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" XCOPY "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /E /C /R /I /H /O /X /Y' AS CMD, 7 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" XCOPY "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /E /C /R /I /H /O /X /Y' AS CMD, 7 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" XCOPY "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /E /C /R /I /H /O /X /Y' AS CMD, 7 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" XCOPY "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /E /C /R /I /H /O /X /Y' AS CMD, 7 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" XCOPY "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /E /C /R /I /H /O /X /Y' AS CMD, 7 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" XCOPY "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /E /C /R /I /H /O /X /Y' AS CMD, 7 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" XCOPY "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /E /C /R /I /H /O /X /Y' AS CMD, 7 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" XCOPY "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /E /C /R /I /H /O /X /Y' AS CMD, 7 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" RD "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /S /Q' AS CMD, 8 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" RD "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /S /Q' AS CMD, 8 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" RD "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /S /Q' AS CMD, 8 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" RD "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /S /Q' AS CMD, 8 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" RD "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /S /Q' AS CMD, 8 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" RD "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /S /Q' AS CMD, 8 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" RD "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /S /Q' AS CMD, 8 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" RD "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /S /Q' AS CMD, 8 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" RD "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /S /Q' AS CMD, 8 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" RD "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /S /Q' AS CMD, 8 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" RD "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /S /Q' AS CMD, 8 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" RD "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /S /Q' AS CMD, 8 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF NOT EXIST "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" MKDIR "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '"' AS CMD, 9 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF NOT EXIST "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" MKDIR "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '"' AS CMD, 9 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF NOT EXIST "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" MKDIR "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '"' AS CMD, 9 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF NOT EXIST "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" MKDIR "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '"' AS CMD, 9 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" echo y| cacls "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /T /P "NTHS\Technicians":F "NTHS\Lab Supervisors":F "NTHS\Domain Admins":F "Administrators":F "NTHS\' + LTRIM(RTRIM(A.STUDENT_ID)) + '":F' AS CMD, 10 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" echo y| cacls "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /T /P "NTHS\Technicians":F "NTHS\Lab Supervisors":F "NTHS\Domain Admins":F "Administrators":F "NTHS\' + LTRIM(RTRIM(A.STUDENT_ID)) + '":F' AS CMD, 10 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" echo y| cacls "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /T /P "NTHS\Technicians":F "NTHS\Lab Supervisors":F "NTHS\Domain Admins":F "Administrators":F "NTHS\' + LTRIM(RTRIM(A.STUDENT_ID)) + '":F' AS CMD, 10 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" echo y| cacls "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /T /P "NTHS\Technicians":F "NTHS\Lab Supervisors":F "NTHS\Domain Admins":F "Administrators":F "NTHS\' + LTRIM(RTRIM(A.STUDENT_ID)) + '":F' AS CMD, 10 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" @ECHO OFF | subinacl /noverbose /subdirectories "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /setowner=NTHS\' + LTRIM(RTRIM(A.STUDENT_ID)) AS CMD, 11 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" @ECHO OFF | subinacl /noverbose /subdirectories "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /setowner=NTHS\' + LTRIM(RTRIM(A.STUDENT_ID)) AS CMD, 11 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" @ECHO OFF | subinacl /noverbose /subdirectories "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /setowner=NTHS\' + LTRIM(RTRIM(A.STUDENT_ID)) AS CMD, 11 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" @ECHO OFF | subinacl /noverbose /subdirectories "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" /setowner=NTHS\' + LTRIM(RTRIM(A.STUDENT_ID)) AS CMD, 11 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" @ECHO OFF | subinacl /noverbose /subdirectories "\\fileserver02\students\Grade_09\' + LTRIM(RTRIM(A.STUDENT_ID)) + '\*.*" /setowner=NTHS\' + LTRIM(RTRIM(A.STUDENT_ID)) AS CMD, 12 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('09')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" @ECHO OFF | subinacl /noverbose /subdirectories "\\fileserver01\students\Grade_10\' + LTRIM(RTRIM(A.STUDENT_ID)) + '\*.*" /setowner=NTHS\' + LTRIM(RTRIM(A.STUDENT_ID)) AS CMD, 12 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('10')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" @ECHO OFF | subinacl /noverbose /subdirectories "\\fileserver01\students\Grade_11\' + LTRIM(RTRIM(A.STUDENT_ID)) + '\*.*" /setowner=NTHS\' + LTRIM(RTRIM(A.STUDENT_ID)) AS CMD, 12 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('11')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT 'IF EXIST "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '" @ECHO OFF | subinacl /noverbose /subdirectories "\\fileserver01\students\Grade_12\' + LTRIM(RTRIM(A.STUDENT_ID)) + '\*.*" /setowner=NTHS\' + LTRIM(RTRIM(A.STUDENT_ID)) AS CMD, 12 AS Priority
FROM SQLSRV01.TBL_Student_Records.dbo.REG AS A LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_ACADEMIC AS B ON (A.STUDENT_ID = B.STUDENT_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_STAFF AS C ON (A.COUNSELOR = C.STAFF_ID)
LEFT JOIN SQLSRV01.TBL_Student_Records.dbo.REG_PERSONAL AS D ON (A.STUDENT_ID = D.STUDENT_ID)
WHERE A.CURRENT_STATUS = 'A' AND A.BUILDING IN (1,2) AND A.GRADE IN ('12')
AND NOT UPPER(LTRIM(RTRIM(A.STUDENT_ID))) IN
(SELECT UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID FROM OPENQUERY(SQLSRV02,'SELECT UPPER(LTRIM(RTRIM(E.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) AS UserID, F.GrpAccountID FROM SQLSRV02.TBL_USER_MANAGEMENT.dbo.UAccounts AS E LEFT JOIN SQLSRV02.TBL_USER_MANAGEMENT.dbo.LI_Group2UserID AS F ON (E.AccountID = F.UsrAccountID) WHERE E.Disabled = 0') AS G WHERE UPPER(LTRIM(RTRIM(G.UserID COLLATE SQL_Latin1_General_CP1_CS_AS))) = UPPER(LTRIM(RTRIM(A.STUDENT_ID))) AND CASE WHEN A.GRADE = '10' THEN '40' WHEN A.GRADE = '11' THEN '41' WHEN A.GRADE = '12' THEN '42' ELSE '39' END = G.GrpAccountID)
UNION
SELECT '@ECHO eSchool to AD Sync Complete %DATE% %TIME% >> \\SQLSRV01\e$\_Batch\00-Logs\log.log' AS CMD, 100.1 AS Priority UNION
SELECT '@ECHO ----------- >> \\SQLSRV01\e$\_Batch\00-Logs\log.log' AS CMD, 100.2 AS Priority UNION
SELECT 'REM EXIT' AS CMD, 100.3 AS Priority
ORDER BY Priority, CMD
|
|
|
|
|
Outcome:
|
|
The above scripts are now entirely automated in the form of DTS packages that run across our SQL 2000 & 2005 environment and also out to our vendors to supply any updated and year to year roll over activity. Through these scripts, a great number of previously manual activities, like a new student in the middle of a current year, can now be achieved entirely automated also allowing a single point of entry through the Student Records or Finance system.
|
|
|
|
|
|
|
|
