quote:

---

Originally posted by glt101

Hi Jezemine,
Thanks for the suggestion!

Yes, there is one extended store procedure used for
Goldmine 7 (gm7sql.dll). How can I find out if it is
this xp that is at fault?

---

quote:

---

Originally posted by glt101


Not sure what a CLR proc is. Can you point me to a
description of that?

---

quote:

---

Originally posted by jezemine

ouch.

Q: what's worse than using a dreaded cursor in sql server?

A: using a cursor that calls a leaky xp. not only will it be slow, it will bring down your server!

---

elsasoft.org

---

OPEN fieldcursor

FETCH NEXT FROM fieldcursor INTO @fieldname, @value

WHILE @@fetch_status = 0
    BEGIN
        SELECT
			     @tablename = convert(sysname,o.name),
			     @datatype =	convert (sysname,case
				                when t.xusertype > 255 then t.name
		                        else d.NAME
			                    end) 
		    FROM
			     sysobjects o,
			             master.sys.types d,
			     systypes t,
			     syscolumns c
			     LEFT OUTER JOIN syscomments m on c.cdefault = m.id
				    AND m.colid = 1
		    WHERE
			     o.name in ('CONTACT1','CONTACT2')
			     AND o.id = c.id
		         AND t.xtype = system_type_id
			     AND o.type <> 'P'
			     AND c.xusertype = t.xusertype
			     AND c.name like @fieldname 

        IF @fieldname = 'MERGECODES'
            SELECT @tablename = CASE WHEN @gmversion = '4' AND @tablename IS NOT NULL
                                            THEN 'CONTACT2'
                                     WHEN @gmversion <> '4' OR @gmversion IS NULL
                                            THEN 'CONTACT1'
                                     ELSE NULL
                                END

        IF UPPER(@tablename) = 'CONTACT1'
            BEGIN
                IF UPPER(@fieldname) = 'OWNER'
                    SELECT @value = UPPER(@value)

                SELECT @setcmd1 = ' ' + @fieldname + ' = ' + CASE WHEN UPPER(@datatype) NOT IN ('FLOAT', 'INT',
                                                                      'REAL','NUMERIC','SMALLINT','TINYINT','DECIMAL') 
                                                                      AND @value IS NOT NULL
                                                                     THEN '"' + @value + '"'
                                                                     WHEN @value IS NULL THEN 'NULL'
                                                                     ELSE @value END +
                                                            CASE WHEN (UPPER(@fieldname) IN ('COMPANY','CONTACT','LASTNAME',
                                                                            'CITY','KEY1','KEY2','KEY3',
                                                                            'KEY4','KEY5') OR 
                                                                            (UPPER(@fieldname) IN ('STATE', 'COUNTRY')
                                                                            AND @gmversion <> 4)) AND @value IS NOT NULL
                                                                 THEN ', u_' + @fieldname + ' = "' + UPPER(@value) + '"'
                                                                 WHEN (UPPER(@fieldname) IN ('COMPANY','CONTACT','LASTNAME',
                                                                            'CITY','KEY1','KEY2','KEY3',
                                                                            'KEY4','KEY5') OR 
                                                                            (UPPER(@fieldname) IN ('STATE', 'COUNTRY')
                                                                            AND @gmversion <> 4)) AND @value IS NULL
                                                                 THEN ', u_' + @fieldname + ' = ""' ELSE "" END +
                                           ' WHERE recid = ' + CASE  
		 	                                                     WHEN @recid LIKE '%''%' THEN '"' + @recid + '"'
			                                                     WHEN @recid LIKE '%"%'  THEN '''' + @recid + ''''
			                                                     ELSE '''' + @recid + ''''
	     	                                                   END
                    EXEC('UPDATE contact1 SET ' + @setcmd1)
                    EXEC GMW_UpdateSyncLog 'contact1', @recid, @user, 'U', @fieldname
            END
      
        IF UPPER(@tablename) = 'CONTACT2'
            BEGIN
                /*this block of accountno and recid steps should only occur once the most*/
                IF @accountno IS NULL
                    SELECT @accountno = accountno FROM contact1 WHERE recid = @recid

                IF @c2recid IS NULL
                    SELECT @C2recid = recid FROM contact2 WHERE accountno = @accountno

                IF @c2recid IS /*STILL*/ NULL --there is no contact2 record for this contact
                    BEGIN
                        /*get a recid*/
                        EXEC master..xp_GMNewRecid @user, @c2recid OUTPUT
                        INSERT contact2 (accountno, recid) VALUES (@accountno, @c2recid)
                        EXEC GMW_UpdateSyncLog 'contact2', @c2recid, @user, 'N'
                    END
                
                SELECT @setcmd1 = ' ' + @fieldname + ' = ' + CASE WHEN UPPER(@datatype) NOT IN ('FLOAT', 'INT',
                                                                      'REAL','NUMERIC','SMALLINT','TINYINT','DECIMAL') 
                                                                      AND @value IS NOT NULL
                                                                     THEN '"' + @value + '"'
                                                                     WHEN @value IS NULL THEN 'NULL'
                                                                     ELSE @value END +
                                  ' WHERE recid = ' + CASE  
		 	                                           WHEN @c2recid LIKE '%''%' THEN '"' + @c2recid + '"'
			                                           WHEN @c2recid LIKE '%"%'  THEN '''' + @c2recid + ''''
			                                           ELSE '''' + @c2recid + ''''
	     	                                          END
                EXEC('UPDATE contact2 SET ' + @setcmd1)
                EXEC GMW_UpdateSyncLog 'contact2', @c2recid, @user, 'U', @fieldname
            END

        SELECT @tablename = NULL
        FETCH NEXT FROM fieldcursor INTO @fieldname, @value
                 
    END
       
CLOSE fieldcursor
DEALLOCATE fieldcursor