GoldMine 9 Truncates Long Detail Names

select distinct left(contact,20) + '|' + substring(contact,21,20) 
from contsupp
where rectype = 'P' and Len(contact) >20
order by left(contact,20) + '|' + substring(contact,21,20) 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Rename_Detail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
  drop procedure [dbo].[Rename_Detail]
end
GO


/*
	  Rename one GoldMine Detail (Profile):
    - Update all occurrences in ContSupp.
    - Update the value in Lookup (if Lookup table is present in current database).

    Note:  This proc does not update the Last Update fields in the Detail record.
           It is assumed you prefer to leave those to reflect the last _manual_ update.
           
    Warning:  This change is NOT sync-aware.  Run this on a primary installation 
              then re-sync _all_ data (ignore cutoff) to other installations.
*/

CREATE procedure Rename_Detail
(	@OldDetailName   varchar(40)
,	@NewDetailName   varchar(40)
)
as

set nocount on

declare @DetailsUpdated   int
,   @LookupsUpdated		int
,		@UpdateError		int
,		@ReturnValue		int
,		@ProcedureName		sysname

set @ProcedureName = object_name(@@procid)

------------------------------------------------------------------------------------------------------
-- Make sure we have required parameters
------------------------------------------------------------------------------------------------------
if (@OldDetailName is null or rtrim(@OldDetailName) = '')
 or (@NewDetailName is null or rtrim(@NewDetailName) = '')
begin
	raiserror ('%s requires that you specify @OldDetailName and @NewDetailName', 16, 1, @ProcedureName)
	goto EndProc
end

begin transaction

raiserror ('%s: Preparing to rename detail from "%s" to "%s"', 10, 1, @ProcedureName, @OldDetailName, @NewDetailName)

------------------------------------------------------------------------------------------------------
-- Update ContSupp
------------------------------------------------------------------------------------------------------
update ContSupp
set Contact = @NewDetailName
, U_Contact = upper(@NewDetailName)
where RecType = 'P' -- Profile record
  and Contact = @OldDetailName  
select @UpdateError = @@Error, @DetailsUpdated = @@RowCount
if @UpdateError = 0 -- No SQL error
begin
  set @ReturnValue = 0 -- successfully updated ContSupp
  if @DetailsUpdated = 0
  begin
	raiserror ('%s: - No ContSupp detail records found', 10, 1, @ProcedureName, @DetailsUpdated)
  end
  else
  begin
    raiserror ('%s: - Successfully renamed %d ContSupp detail records', 10, 1, @ProcedureName, @DetailsUpdated)
  end
end
else -- SQL error
begin
  raiserror ('%s: ContSupp update failed with SQL error %d.', 16, 1, @ProcedureName, @UpdateError)
  set @ReturnValue = @UpdateError -- Error number from Update
  goto RollbackTrans
end

------------------------------------------------------------------------------------------------------
-- Update Lookup
------------------------------------------------------------------------------------------------------
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Lookup]') and OBJECTPROPERTY(id, N'IsTable') = 1)
begin
  raiserror ('%s: - Lookup table does not exist in this database. Lookup rename skipped.', 10, 1, @ProcedureName)
  goto CommitTrans
end

update Lookup
set Entry = @NewDetailName
, U_Entry = upper(@NewDetailName)
where FieldName = 'MPROFHEAD V' -- Profile Header Value
  and Entry = @OldDetailName
select @UpdateError = @@Error, @LookupsUpdated = @@RowCount
if @UpdateError = 0 -- no SQL error
begin
  if @LookupsUpdated = 1
  begin
    set @ReturnValue = 0 -- successfully updated 1 Lookup
    raiserror ('%s: - Successfully renamed %d Lookup detail header records', 10, 1, @ProcedureName, @LookupsUpdated)
  end
  else
  begin
    raiserror ('%s: - Expected to update one Lookup but updated %d', 16, 1, @ProcedureName, @LookupsUpdated)
	set @ReturnValue = -1
    goto RollbackTrans
  end
end
else -- SQL error
begin
  raiserror ('%s: - Lookup update failed with SQL error %d', 16, 1, @ProcedureName, @UpdateError)
  set @ReturnValue = @UpdateError -- Error number from Update
  goto RollbackTrans
end

set @ReturnValue = @DetailsUpdated
goto CommitTrans

RollbackTrans:
rollback transaction
raiserror ('%s: - Transaction rolled back. All changes made by this call to the proc have been reversed.', 16, 1, @ProcedureName)
goto EndProc

CommitTrans:
commit transaction

EndProc:
return @ReturnValue

GO

exec @ReturnCode = dbo.Rename_Detail @OldDetailName = 'Airborne Account Number',        @NewDetailName = 'Airborne Account No'
exec @ReturnCode = dbo.Rename_Detail @OldDetailName = 'Express Mail Corp. Acct. No.',   @NewDetailName = 'Expr Mail Corp Acct'
exec @ReturnCode = dbo.Rename_Detail @OldDetailName = 'Federal Express Acct. #',        @NewDetailName = 'FedEx Account No'
exec @ReturnCode = dbo.Rename_Detail @OldDetailName = 'GoldMine Serial Number',         @NewDetailName = 'GoldMine Serial No'
exec @ReturnCode = dbo.Rename_Detail @OldDetailName = 'Tax Exempt ID number(s)',        @NewDetailName = 'Tax Exempt ID No(s)'
exec @ReturnCode = dbo.Rename_Detail @OldDetailName = 'UPS Billing Invoice Number',     @NewDetailName = 'UPS Billing Inv No'