ThinPrint Management Center – Dokumentation

Zweck dieses SQL-Skriptes

Falls Sie mehrere Connections mit derselben Location angelegt haben, kann es sinnvoll sein, diese Location in mehrere aufzuteilen – somit in je eine Location pro Connection. Auf diese Weise können Sie Endungen von Druckernamen wie _p2 oder von Freigabenamen wie _s3 beseitigen.

Vorsicht! Führen Sie dieses Skript nicht zweimal auf derselben Datenbank aus.

1. Ermitteln Sie den Datenbanknamen in der Konsole des Management Centers mit Extra→ Application Settings.
2. Schließen Sie die Konsole des Management Centers.
3. Öffnen Sie SQL Server Management Studio.
4. Markieren Sie (links) die betreffende Datenbank und generieren Sie eine Sicherheitskopie mit Tasks→ Back Up im Kontextmenü.
5. Klicken Sie dann New Query.
6. Kopieren Sie das folgende Skript (siehe unten) in das rechte Eingabefeld.
7. Klicken Sie auf Execute.
8. Schließen Sie SQL Server Management Studio.
9. Fertig. Sie können die Konsole des Management Centers wieder öffnen.

SQL-Skript

-- DO NOT RUN THIS SCRIPT TWICE ON THE SAME DATABASE
SELECT L.NAME + '_' + S.NAME, S.NAME, L.CLIENTTYPE, L.CONSVCADDRESS, L.TCPPORT
FROM
(SELECT LOCATION_ID, NAME, CLIENTTYPE, CONSVCADDRESS, TCPPORT FROM LOCATION) AS L
INNER JOIN
(SELECT NAME, LOCATION_ID FROM SOURCE) AS S
ON L.LOCATION_ID = S.LOCATION_ID;
UPDATE SOURCE SET LOCATION_ID = L.LOCATION_ID
FROM
SOURCE AS S
INNER JOIN
LOCATION AS L
ON L.COMMENT = S.NAME;
UPDATE CONNECTION SET LOCATION_ID = L.LOCATION_ID
FROM
CONNECTION AS C
INNER JOIN
LOCATION AS L
ON L.COMMENT = C.NAME;

-- REMOVE SRVPRINTER OF THE OBSOLETE CONNECTIONS
DELETE FROM SRVPRINTER
WHERE FK_CONPRINTER IN
(
   SELECT PK_IDENTIFIER
   FROM CONPRINTER
   WHERE FK_CONNECTION IN
   (
      SELECT CONNECTION_ID
      FROM CONNECTION
      WHERE LOCATION_ID IN
      (
         SELECT LOCATION_ID
         FROM LOCATION WHERE LOCATION_ID NOT IN
         (
            SELECT LOCATION_ID FROM SOURCE
         )
      )
   )
);

-- REMOVE CONPRINTER OF THE OBSOLETE CONNECTIONS
DELETE FROM CONPRINTER
WHERE PK_IDENTIFIER IN
(
   SELECT PK_IDENTIFIER
   FROM CONPRINTER
   WHERE FK_CONNECTION IN
   (
      SELECT CONNECTION_ID
      FROM CONNECTION
      WHERE LOCATION_ID IN
      (
         SELECT LOCATION_ID
         FROM LOCATION WHERE LOCATION_ID NOT IN
         (
            SELECT LOCATION_ID FROM SOURCE
         )
      )
   )
);

-- REMOVE SERVERPORT OF THE OBSOLETE CONNECTIONS
DELETE FROM SERVERPORT WHERE CONNECTION_ID IN
(
   SELECT CONNECTION_ID
   FROM CONNECTION
   WHERE LOCATION_ID IN
   (
      SELECT LOCATION_ID
      FROM LOCATION WHERE LOCATION_ID NOT IN
      (
         SELECT LOCATION_ID FROM SOURCE
      )
   )
);

-- REMOVE OBSOLETE CONNECTIONS
DELETE FROM CONNECTION WHERE LOCATION_ID IN
(
   SELECT LOCATION_ID
   FROM LOCATION WHERE LOCATION_ID NOT IN
   (
      SELECT LOCATION_ID FROM SOURCE
   )
);

-- REMOVE OBSOLETE LOCATIONS
DELETE FROM LOCATION WHERE LOCATION_ID IN
(
   SELECT LOCATION_ID
   FROM LOCATION WHERE LOCATION_ID NOT IN
   (
      SELECT LOCATION_ID FROM SOURCE
   )
);

-- REMOVE CONPRINTER AND THEIR SRVPRINTERS WHICH POINT TO LOCATIONS THAT DO NOT MATCH THEIR CONNECTIONS TARGET LOCATION
DELETE FROM SRVPRINTER WHERE FK_CONPRINTER IN
(
   SELECT P.PK_IDENTIFIER AS IDENT
   FROM
   (
      CONPRINTER AS P
      INNER JOIN
      CONNECTION AS C
      ON P.FK_CONNECTION = C.CONNECTION_ID
   )
   INNER JOIN
   SOURCE AS S
   ON C.LOCATION_ID <> S.LOCATION_ID AND C.CONNECTION_ID = P.FK_CONNECTION AND P.FK_SRCPRINTER = S.SOURCE_ID
)
DELETE FROM CONPRINTER WHERE PK_IDENTIFIER IN
(
   SELECT P.PK_IDENTIFIER AS IDENT
   FROM
   (
      CONPRINTER AS P
      INNER JOIN
      CONNECTION AS C
      ON P.FK_CONNECTION = C.CONNECTION_ID
   )
   INNER JOIN
   SOURCE AS S
   ON C.LOCATION_ID <> S.LOCATION_ID AND C.CONNECTION_ID = P.FK_CONNECTION AND P.FK_SRCPRINTER = S.SOURCE_ID
)