Other Support Options
|
| • |
Listserv
Pose a question to other users. |
|
|
| Revised | : | January 5, 2006 (mc) |
| Version | : | Client Version 7 and higher |
| Revision | : | 1 |
HOWTO: Increasing the Username field in the ILLiad database
January 5, 2006
SUMMARY
By default, the username field for ILLiad customers is 20 characters. In some cases this field may need to be expanded to a larger size to accomodate usernames longer than 20 characters. An example would be a site using the customer's full name or full email address as their username (i.e. johnqpublic@university.edu). Also if you are importing Loansome Doc requests in version 2.5 of Docline, the Loansome Doc UserID has been replaced with the Patron EMail as a matching point against the ILLiad username, which may also require expanding the username field.
HOWTO
There are several tables in the ILLData database that need to be edited. Be sure to backup the SQL Server database before making any of these changes. The tables and field names that need to be edited are:
| Table Name | Field Name |
| History | Username |
| ImportValidation | Username |
| InvoiceSessions | CustomerSymbol |
| TestModeInvoiceSessions | CustomerSymbol |
| Tracking | ChangedBy |
| Transactions | Username |
| UserAccountsLink | Username |
| UserNotes | Username |
| Users or UsersALL | Username |
| WebSession | Username |
- To increase the field size, open the SQL Server Enterprise Manager on the ILLiad server.
- Expand SQL Server Group, your ILLiad server name, Databases, ILLData, and click on Tables in the left pane.
- Look in the right pane for the table (i.e. History). Right click on the table and choose Design Table.
- In the table view look for the appropriate field name (i.e. Username) and change the length field from 20 to 50. Hit Enter.
- Click the save icon in the top left of the window to save the table. This will make a temporary copy of the table and its contents before moving the data back to a fresh table. For very large tables like Tracking or History the save process can take several minutes.
- Close the modified table and move to the next table in the list and repeat the above steps.
SHARED SERVER DATABASES
If you are a shared server installation and expand the username field in the UsersALL table, you will need to drop and recreate the User views for each site. This can be done with one SQL query. Open the SQL Query Analyzer and login to the SQL Server as a system administrator (sa account or Windows authentication). In the blank white query window paste the following query:
----------------
DECLARE nvtgc_cursor CURSOR
FOR
SELECT TABLE_SCHEMA
FROM INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'Users' ORDER BY TABLE_SCHEMA
OPEN nvtgc_cursor
DECLARE @ViewNVTGC varchar(10)
SET @ViewNVTGC = '00000000'
FETCH NEXT FROM nvtgc_cursor INTO @ViewNVTGC
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @ViewNVTGC = RTRIM(@ViewNVTGC)
DECLARE @VIEWDEF varchar(255)
SET @VIEWDEF = (SELECT VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'Users' and table_schema = @ViewNVTGC)
EXEC ('drop view ['+ @ViewNVTGC +'].Users')
EXEC (@VIEWDEF)
EXEC ('grant select on ['+ @ViewNVTGC +'].Users to [illiad]')
END
FETCH NEXT FROM nvtgc_cursor INTO @ViewNVTGC
END
CLOSE nvtgc_cursor
DEALLOCATE nvtgc_cursor
----------------
Run the above script by clicking the green arrow at the top of the form or hitting F5. After the query has been run, close the SQL Query Analyzer. You do not need to save changes to the query.
