Purpose of this SQL script
In the Management Center console’s Connection→ Edit printers screen, the Output Gateway printer objects are automatically appended a sharename, in the format:
printername_localprintserver or clientprintername_clientaddress
However, some customers want to remove the _localprintserver or _clientaddress from the sharename, but prefer not to do this manually. In this case the printer names must be unique in the whole print environment.
Note! It is important that neither the local printserver name nor the client address not contains an underscore (_). This is because the script searches (in the sharename) for the first underscore from the right. Alternatively, if, for example, all client addresses on the same connection contain an underscore, the script can be run twice using the connection ID (see below Changing all sharenames of a specific connection).
Changing all sharenames of an SQL database
- In the Management Center console, find out the database name with Extra→ Application Settings.
- Close the Management Center console.
- Open SQL Server Management Studio.
- Mark the database (on the left) and perform a backup with Tasks→ Back Up in the context menu.
- Then click New Query.
script for changing the sharenames of all printers
- Copy the following string into the right input field:
UPDATE CONPRINTER SET SHARENAME = SUBSTRING(SHARENAME, 1, LEN(SHARENAME) - CHARINDEX('_', REVERSE(SHARENAME)) )
- Click Execute. A confirmation message will appear below; in this message the number of rows is double the number of printer objects.
- Close SQL Server Management Studio.
- Finished. Re-open the Management Center console.
confirmation message (example)
Changing all sharenames of a specific connection
As an alternative to running the script on the entire database, it’s also possible to run it on only a specified Management Center Connection. In addition to the steps above, you must identify the relevant Connection ID beforehand (see the column FK_CONNECTION). On this:
- Mark the row of the Connection printers dbo.CONPRINTER and choose Select Top 1000 Rows or Edit Top 200 Rows.
opening the table dbo.CONPRINTER
checking the IDs in FK_CONNECTION or in FK_SRCPRINTER (example)
- At this point, you may receive a “not accessible” message. If so, temporarily change the database property Restrict Access from Single_User to Multi_User.
Database “not accessible” message
changing the Restrict Access property temporarily
- Then copy the following string into the input field on the right:
UPDATE CONPRINTER SET SHARENAME = SUBSTRING(SHARENAME, 1, LEN(SHARENAME) - CHARINDEX('_', REVERSE(SHARENAME)) ) WHERE FK_CONNECTION = 4;
At the end of this script, enter the identified Connection ID – in this case, 4 – in front of the semicolon.
script for changing the sharenames of Connection with ID 4
- Click Execute. A confirmation message will appear below; in this message the number of rows is double the number of printer objects.
- Set the database property Restrict Access back to Single_User and close SQL Server Management Studio.
Changing sharenames of specific printers
As part of other scripts it can be necessary to change sharenames only of single printers. So, alternatively to the use per Connection the script can be run per printer. Here, check the IDs of the relevant printers before (see the column FK_SRCPRINTER).
- On this, maybe you get a “not accessible” message. In this case change the database Restrict Access property from Singe_User to Multi_User temporarily.
- Then copy the following String into the right input field:
UPDATE CONPRINTER SET SHARENAME = SUBSTRING(SHARENAME, 1, LEN(SHARENAME) - CHARINDEX('_', REVERSE(SHARENAME)) ) WHERE FK_SRCPRINTER = 15;
Enter the identified Printer ID at the end of this script – here: 15 – just before the semicolon.
- Click Execute. A confirmation message will appear below; in this message the number of rows is twice as big as the number of printer objects.
- Set the database Restrict Access property back to Singe_User and close SQL Server Management Studio.