| GoldMine Sync stamp through SP | |
Posted: Mon Jun 29, 2009 3:16 am |
Points: 0 |
|
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 |
|
|
|
|
| | |
Posted: Wed Oct 07, 2009 8:58 am |
Points: 0 |
|
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... |
|
|
|
|
| | |
Posted: Wed Oct 07, 2009 10:39 am |
Points: 0 |
|
DougCastell |
GoldMine Guru |
|
|
Joined: 15 Jun 2006 |
Posts: 1639 |
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. |
|
|
|
|
Posted: Thu Oct 22, 2009 1:39 am |
Points: 0 |
|
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 |
|
|
|
|
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
|
|
|
Brought to you by Castell Computers, Doug Castell, Admin
Powered by phpBB © 2001-2004 phpBB Group Theme created by Vjacheslav Trushkin
|