Log in Register FAQ Memberlist Search ContactReview Forum Index
A GoldMine Discussion and Support Community

ContactReview Forum Index » GoldMine Coders » GoldMine Sync stamp through SP
Post new topic  Reply to topic View previous topic :: View next topic 
GoldMine Sync stamp through SP
PostPosted: Mon Jun 29, 2009 3:16 am Points: 0 Reply with quote
sukh007
GoldMine Journeyman
Joined: 11 Feb 2009
Posts: 33




Hi Guys

I have a SQL job that runs nightly that updates one of my fields. The SQL update statement is below.

update contact2
set ustatus='Expired'
where urenewal<getdate() and ustatus='current'

This script runs fine and updates the relevant record however it does not sync stamp it for remote users. I have the sync log SP on my database already as I use it on insert statements for other tables which works fine.

EXEC gmw_updatesynclog 'CONTACT2',@recid,'MASTER','U','ustatus'

I know I need to use the above query however I am not sure how to write the SQL script for multiple records as I am guessing I would need to use a loop statement.

Thanks
View user's profile Send private message

PostPosted: Wed Oct 07, 2009 8:58 am Points: 0 Reply with quote
JeffPGMT
n00b
Joined: 05 Jun 2007
Posts: 3
Location: Valley Glen, CA




how'about some psudo code...
-- get all the recs to update
select status = 0 ,c2.accountno ,c2.urecid into #t
from goldmine_sales_and_marketing..contact2 c2
where c2.ustatus = 'Current' and c2.urenewal<getdate()
-- declare vars...
declare @accountno varchar(20) ,@recid varchar(15) ,@status int
-- init @accountno to an empty string (not null)
set @accountno = ''
while @accountno is not null begin
-- loopin getting the lowest alphanumeric accountno greater than ''
set @accountno = (select top 1 min(accountno) from #t where status = 0 and accountno > @accountno)
-- test that you have a valid accountno, perhaps some other test?
if len(isnull(nullif(@accountno,' '),''))=20 begin
update goldmine_sales_and_marketing..contact2
set ustatus = 'Expired' where accountno = @accountno
--catch an error
set @status = @@error
set @status = case when @status = 0 then 1 else @status end
-- increment your #t recs
update #t set status = @status where accountno = @accountno
exec goldmine_sales_and_marketing..gmw_updatesynclog
'contact2',@recid,'master','U','ustatus'
set @status = @@error
-- maybe roll back your process if the update synclog fails?
if nullif(@accountno,'') is null begin break end
end

HTH
JeffP...
View user's profile Send private message

PostPosted: Wed Oct 07, 2009 10:39 am Points: 0 Reply with quote
DougCastell
GoldMine Guru
Joined: 15 Jun 2006
Posts: 1631
Location: Los Angeles, CA




Yeah, the first thing you need to do is get that statement running one record at a time so that it has a context and has a single recid to feed to the syncstamp thing. Right now, you're just running a blanket update of all records that fit the bill.

I suppose you could get around that by writing a trigger on the contact2 table that would generate a sync stamp if the updated field is your target field, but that would be cheating. Wink

_________________
Doug Castell

GoldMine Sales and Support:
http://www.castellcomputers.com/
office: (310)601-4738
View user's profile Send private message Visit poster's website AIM Address Yahoo Messenger MSN Messenger ICQ Number

PostPosted: Thu Oct 22, 2009 1:39 am Points: 0 Reply with quote
sukh007
GoldMine Journeyman
Joined: 11 Feb 2009
Posts: 33




Thanks for the feedback guys.

I decided to leave this as I just got Inaport to do this for me. I guess you could call that cheating Very Happy
View user's profile Send private message

GoldMine Sync stamp through SP
  ContactReview Forum Index » GoldMine Coders
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
All times are GMT - 7 Hours  
Page 1 of 1  

  
  
 Post new topic  Reply to topic  


Brought to you by Castell Computers, Doug Castell, Admin
RSS Feed
Powered by phpBB © 2001-2004 phpBB Group
Theme created by Vjacheslav Trushkin