Working With Custom Fields in MS SQL

Document #527, Working With Custom Fields in SQL

Introduction

This document presents an alternate method for modifying GoldMine’s User Defined Fields with your data rehosted to SQL. Some situations encountered by SQL database administrators can preclude the use of GoldMine’s normal procedures for adding custom fields, such as:

• Creating or removing User Defined Fields from GoldMine SQL databases when rebuilding is not possible
• Adding User Defined fields to GoldMine SQL databases while users are logged into GoldMine
• Rehosting more than 233 User Defined Fields to MS SQL 7

Note: This information is provided as is and cannot be elaborated on or supported by GoldMine Technical Support. The operations described herein may adversely affect your database. Only database administrators and those familiar with SQL server administration should attempt to implement these features. As with any operation that could affect your entire database, it is suggested that you backup your data before altering the configuration of your database server.

How do User Defined fields work?

All of the characteristics for the fields in CONTACT1 and CONTACT 2 are stored in the CONTUDEF table. When User Defined Fields are added or removed, the records in the CONTUDEF table are immediately modified, but the CONTACT2 table will not reflect these changes until after the CONTACT2 table structure is modified (rebuilt) to reflect these changes.

The illustration is of GoldMine’s default custom fields with three additional fields UCHAR, UDATE, and UNUM. Until the CONTACT2 table is rebuilt, the new fields will not be added to the actual table and will not be available for input.

CONTACT2 Contents Before Rebuilding:

CONTACT2 Contents After Rebuilding:

The new fields are shown highlighted in grey here.

Adding and Removing User Defined Fields Normally.

Adding and removing custom fields in GoldMine can be done the same way regardless of if you are rehosted to SQL or not. The custom field is defined under File>>Configure GoldMine...>>Custom Fields and the database is then rebuilt. Note that users must be logged out of GoldMine when rebuilding databases. Under SQL, however, when a table is rebuilt through GoldMine, previously granted permissions are lost on that table and must be regranted through the appropriate SQL server tool(s). For MS SQL 6.5 and 7, this can be accomplished by issuing the following commands in ISQL_W or the Query

Analyzer.

Grant ALL on CONTACT2 to PUBLIC
Grant ALL on CONTUDEF to PUBLIC

These queries specify that all applicable permissions are being granted for the Contact2 and Contudef tables. Permissions granted to the public role are applied to all users of the public role on the database server.

Note: Other SQL servers should conform to similar standards; refer to your SQL server’s documentation for more information.

Adding User Defined Fields without Rebuilding - MS SQL 6.5/7

User Defined fields can be added without having to rebuild or have all users log out of GoldMine.

Steps in GoldMine:

1. Select File>>Configure GoldMine>>Custom Fields.
2. Select New.
3. For Field Name, type a unique field name that begins with the letter U.
4. Type a Description for the field.
5. Specify the Field Type as Character, Date, or Numeric. If a character or numeric format is selected, specify the length of the field.
6. Make a note of your field’s name, type and length (if applicable.) This information will be used later in MS SQL.
7. Select OK.
8. Repeat steps 2 through 7 to add more fields.
9. Select Close.
10. A prompt will appear stating that a rebuild is necessary for the changes to take affect. Do not rebuild by selecting No.
11. Continue with the steps specific to your MS SQL version below.

Steps in MS SQL 6.5:

1. Launch the MS SQL 6.5 Enterprise Manager.
2. Double-click the server’s name to connect to the database server.
3. Expand the Databases folder. Locate the GoldMine database and expand its folder.
4. Expand the Objects folder and then the Tables folder.
5. Double-click on the CONTACT2 table.
6. Scroll down to the last row and place the cursor in the empty cell in the Column Name entry.
7. Enter the name of the new field you added in GoldMine (i.e. UTEST) then hit the tab key.
8. Select a data type from the drop down list that corresponds to the field type specified in GoldMine according to the following table:

9. If a Character field was created in GoldMine, a length must be specified for the field. Hit the tab key to move to the length column and specify the field’s length. This number must match the field length specified in GoldMine exactly.
10. Repeat steps 6 through 9 for each field added in GoldMine.
11. Select the icon (the one with the small diskette picture) to save the changes.

Steps in MS SQL 7:

1. Launch the MS SQL 7 Enterprise Manager.
2. Double-click the server’s name to connect to the database server.
3. Expand the Databases folder.
4. Locate the GoldMine database and expand its folder.
5. Select Tables.
6. In the window on the right, right click on the CONTACT2 table and select ‘design table’.
7. Scroll down to the last row and place the cursor in the empty cell in the Column Name entry.
8. Enter the name of the new field you added in GoldMine (i.e. UTEST) then hit the tab key.
9. Select a data type from the drop down list that corresponds to the field type specified in GoldMine according to the table below.

10. If a Character field was created in GoldMine, a length must be specified for the field. Hit the tab key to move to the length column and specify the field’s length. This number must match the field length specified in GoldMine exactly.
11. Repeat steps 7 through 10 for each field added in GoldMine.
12. Select the icon (the one with the small diskette picture) to save the changes.

Rehosting more than 233 User Defined fields to MS SQL 7

Currently, there is a limitation within MS SQL’s DBLIB.DLL (used by the Borland Database Engine) that will limit the number of fields that are created on any MS SQL server. This limitation will only allow users who are rehosting to MS SQL 7 to create databases containing 233 User Defined Fields. The 10 User defined fields created by GoldMine decrease this limitation to 223 custom, fields. The following steps illustrate how to rehost a larger number of User defined fields from dBase contact files to MS SQL 7 and are intended to act as a work-around until this MS SQL limitation is corrected.

1. Make a Full backup of ** ALL ** GoldMine data.
2. Identify 233 User defined fields (This must include the 10 default field that come with GoldMine).
3. Export Accountno and all User defined fields above the 233 identified in step 2. (To DBF for easier import).
4. Delete User defined fields that were exported in step 3 from File>>Configure GoldMine>>Custom Fields.
5. Rebuild CONTUDEF, and CONTACT2.
6. Rehost to MS SQL 7 (see the MSSQL7.PDF document available at ftp://ftp.goldmine.com/public/manuals/mssql7.pdf for detailed instructions.)
7. Once done rehosting, verify, under Help>>About>>System, that the GoldMine Files and Contact Files are pointing to something similar to MSSQL: MyGMdata: dbo: and not to a mapped drive or UNC path.
8. Recreate the User defined fields deleted in step 4, under File>>Configure GoldMine>>Custom Fields. Do not elect to rebuild your databases at this time.
9. Launch Enterprise Manager and connect to the database server.
10. Expand the Databases folder. Locate the GoldMine database and expand its folder. Select Tables.
11. In the right window pane, right click on the CONTACT2 table and select design table. Scroll to the bottom of all of the columns and place the cursor in the cell below the last Column Name entry.
12. Type the name of the field that was added in GoldMine, i.e. UTEST, then hit the tab key.
13. Select a data type from the drop down list that corresponds to the field type specified in GoldMine according to the table below.

14. If a Character field was created in GoldMine, a length must be specified for the field. Hit the tab key to move to the length column and specify the field’s length. This number must match the field length specified in GoldMine exactly.
15. Close the Design Table.
16. Within GoldMine, Import into the database with the file that was saved in step 3 above. During the import, map the Accountno and the User defined fields. Specify the Accountno field as your match field, in the Profile Options, to have the information associated with the appropriate contact records. For more information on using the Match Field option, see ‘Setting Import Profile Options’ in the GoldMine Reference Manual (available at: ftp://ftp.goldmine.com/public/manuals/gm4-ref.pdf)

* NOTE: Tlog entries (Sync time stamps) will show all the user defined fields that are imported this way as being created/modified on the day/time you imported these fields.

Copyright (c) 2002 FrontRange Solutions Inc.

All rights reserved. You may use this document for personal and informational (non-commercial) purposes, provided that the copyright notice and all other notices and  permissions appear in all copies, the document is not copied or posted on any network computer or broadcast in any media and modifications are not made to the document. Use for any other purpose is expressly prohibited by law, and may result in civil or criminal penalties.

The information contained in this document is provided “as is” without warranty of any kind. To the maximum extent permitted by applicable law, FrontRange Solutions disclaims all warranties, either express or implied, including warranties for quality, accuracy, merchantability, fitness for a particular purpose, title and non-infringement; and in no event shall FrontRange Solutions or its suppliers be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of profits or data or special damages, even if FrontRange Solutions or its suppliers have been advised of the possibility of such damages.