SQL Learning Center - GoldMine

Use SQL queries to get answers from GoldMine

May 28, 2008

Find Deleted Contacts by Comparing Current Database with Older Version

by Doug Steinschneider @ 9:53 pm. Filed under MSSQL Query Analyzer, MSSQL Contacts

Type: MSSQL
Description: In this more advanced query we see the following scenario: You suspect that some contacts have been deleted from GoldMine when instead they should have been moved to your Archive Database. We move contacts flagged for deletion to a database called GoldMine_Archive. In this query we check for GoldMine accountnos in an older backup that weve restored to an alternate MSSQL database GoldMine_20070806 against all the accountnos in both the current GoldMine and GoldMine_Archive.

  1.  
  2. SELECT
  3.  c1r.contact
  4. ,c1r.company
  5. ,c1r.city
  6. ,c1r.STATE
  7. ,c1r.key1
  8. ,c1r.key2
  9. ,c1r.key3
  10. ,c1r.key4
  11. ,c1r.key5
  12. ,c1r.accountno
  13. FROM
  14.  GoldMine_20070806.dbo.CONTACT1 c1r
  15. WHERE
  16.  c1r.accountno NOT IN
  17.  (SELECT accountno FROM contact1 UNION SELECT accountno FROM GoldMine_Archive.dbo.CONTACT1)
  18. ORDER BY
  19.  c1r.company
  20. ,c1r.contact
  21.  

May 18, 2008

Create table with GoldMine Field Usage Counts

by Doug Steinschneider @ 1:46 pm. Filed under MSSQL Query Analyzer

Description:
Run this only in Query Analzyer to create a table called ‘gm_userdef_usage_count’ which holds a row for each field and a count for how many times it has been used.

  1.  
  2. IF EXISTS(SELECT name FROM sysobjects WHERE name = 'gm_userdef_usage_count' AND type = 'U')
  3.      DROP TABLE gm_userdef_usage_count
  4.        
  5. CREATE TABLE gm_userdef_usage_count
  6. (    FieldName    VARCHAR(50),
  7.     RecCount    INT    )
  8.        
  9. --Declare cursor
  10. DECLARE curs CURSOR
  11. GLOBAL
  12. SCROLL
  13. STATIC
  14. FOR
  15. SELECT Field_Name,Field_Type FROM contudef WHERE dbfname ='CONTACT2' and field_name like 'U%'
  16. go
  17.        
  18. --Declare variables
  19. DECLARE @fldname VARCHAR(50)
  20. DECLARE @fldtype VARCHAR(1)
  21. DECLARE @temp VARCHAR(150)
  22. DECLARE @MAX INT
  23. DECLARE @counter INT
  24.        
  25. SET @counter = 1
  26. SELECT @MAX = COUNT(field_name) FROM contudef WHERE dbfname ='CONTACT2' and field_name like 'U%'
  27. --Open cursor and fetch record
  28. OPEN curs
  29. FETCH NEXT FROM curs INTO @fldname,@fldtype
  30.        
  31. --loop through them all
  32. WHILE @counter < = @MAX
  33. BEGIN
  34.     SELECT @counter = @counter+1
  35.     --print @fldname
  36.     IF @fldtype = 'C' or  @fldtype = 'D'
  37.         SET @temp = 'Insert into gm_userdef_usage_count Select ' + CHAR(39) + @fldname + CHAR(39) + ', count(accountno) from contact2 where ' + @fldname + ' is not null '
  38.     ELSE
  39.         SET @temp = 'Insert into gm_userdef_usage_count Select ' + CHAR(39) + @fldname + CHAR(39) + ', count(accountno) from contact2 where ' + @fldname + ' = 0 '
  40.     EXEC(@temp)
  41.     FETCH NEXT FROM curs INTO @fldname,@fldtype
  42. END
  43.        
  44. CLOSE curs
  45.        
  46. DEALLOCATE curs
  47.  

GoldMine SQL Tutorial:

General SQL Tutorials:

SQL Help Links:

Recommended Reading - SQL Books:

RSS:

Links:

Search For SQL Query:

GoldMine MS SQL Queries:

GoldMine dBase SQL Queries (LocalSQL):

21 queries. 0.110 seconds