ThinPrint Management Center – Documentation

Purpose of this SQL script

In case you specified multiple Connections using the same Location it can make sense to split this single Location into multiples – thus, a single Location per Connection. So, you can remove printer name suffixes like _p2 or share name suffixes like _s3.

Caution! Do not run this script twice on the same database.

1. In the Management Center console, find out the database name with Extra→ Application Settings.
2. Close the Management Center console.
3. Open SQL Server Management Studio.
4. Mark the database (on the left) and perform a backup with Tasks→ Back Up in the context menu.
5. Then click New Query.
6. Copy the following SQL script (see below) into the right input field.
7. Click Execute.
8. Close SQL Server Management Studio.
9. Finished. Re-open the Management Center console.

SQL script

-- 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
)