Murray, Pam
2007-05-03T21:58:00Z
I have a view in SQL that shows the contacts (company address info) and multiple personnel records (name, phone#...) I can do a load all. I'm trying to synchronize bi-directional between outlook and sql. I've created a stored procedure to pass the variables from Outlook. It will update the SQL tables, but how do I pass updates from SQL back to Outlook? Is there a way to return the updated sql values once I determine the sql information is more current than Outlook? (I'm matching the Last ModificationTime to a field in SQL, so I would be able to determine which is more current)

Pam Murray
Sponsor
GeniusConnect documentation search (User Manual Downloads...)
Administrator
2007-05-04T10:52:00Z
Hello,

current version has no detection to trigger a load when DB record has been changed. TO load changes, you must use auto.sync scheduler.
Murray, Pam
2007-05-04T14:45:00Z
I do have that set up as well. I had wrote a stored procedure to update SQL because I'm using an SQLview of multiple tables. I'm letting the stored procedure do the work of updating. I have a call in the Assign Table / Options / SQL Commands:
{call GC_UpdateContPers('@Company','@BusinessStreetAddress','@BusinessStreetAddress2','@BusinessState','@BusinessPostalCode','@BusinessCountry','@BusinessCity','@FirstName','@LastName','@BusinessPhone','@FaxPhone','@Userfield1','@Title','@CarPhone','@HomePhone','@Categories','@UserName','@EMailAddr','@WebAddr','@Perskey','@Acc','@Secrtary','@Notes','@MiddleInitial','@BusinessPhone2','@SyncStampP')}

The stored procedure reads in the passed variables and compares the timestamp the the timestamp on the sql tables. If more recent, I do the update to SQL.

I have GCScheduler set up to run automatically. My scheduled job paramaters are: -p profilename -s -e.

I'm thinking I should let the stored procedure do the SQL updating and set the parameters in Genius to only LOAD ALL?

The glitch is employees are going to be doing updates and adds from their BlackBerry.

If this cannot be resolved through this avenue, is there someone I can work with on a consulting basis to set this up?
Administrator
2007-05-04T15:10:00Z
Hi,

if you set the Auto Save option, GeniusConnect detects changes made in Outlook Item and will automatically trigger your update stored procedure.

I assume the flow will be:
BlackBerry change-> Outlook - > GeniusConnect -> Database?
Murray, Pam
2007-05-04T15:45:00Z
Yes, that's correct, but I would also like to automate SQL -> Outlook.
Would having the auto-save require a session of their Outlook to be open at all times?

Pam Murray
Administrator
2007-05-04T15:57:00Z
Yes, are you using MS Exchange?
Murray, Pam
2007-05-04T16:27:00Z
Yes, I'm using Exchange. What # should I contact you at?
Murray, Pam
2007-05-04T20:05:00Z
Is there a way I can export my settings and send to you? THe latest is even though I update Outlook later that SQL, I get this error during Store Item: Modification time conflict. Item (contact name) database overrules outlook changes.
Administrator
2007-05-05T13:57:00Z
Check the time of PC , Database server, exchange server etc..

You can export the settings with Export button in Assign Table dialog.
Murray, Pam
2007-05-05T16:49:00Z
Error on manual store item:
Could not save to database (unknown error: Database returns an empty error message)
I've tested the stored procedure manually and it updates the db. After the error I see the current db values haveupdated Outlook.


Product edition:3-2000 Outlook version 11.0.0.5608

GCScheduler.exe -p profilename -s -e
******************************************
# Exported settings file of Genius@WORK OutlookConnect
# Comments are allowed on a single line only!

[Fields]
# Format: outlookfield=column,conversion
Account=Acc,
Assistant's Name=Secrtary,
Body=Notes,
Business Phone 2=BusinessPhone2,
Business Address City=BusinessCity,
Business Address Country=BusinessCountry,
Business Address Postalcode=BusinessPostalCode,
Business Address PO Box=BusinessStreetAddress2,
Business Address State=BusinessState,
Business Address Street=BusinessStreetAddress,
Business Fax=FaxPhone,
Business Phone=BusinessPhone,
Car (Phone)=CarPhone,
Categories=Categories,
Company (Name)=Company,
Email=EMailAddr,
First Name=FirstName,
Home Phone=HomePhone,
Job Title=Title,
Last Name=LastName,
Middle Name=MiddleInitial,
Web Page=WebAddr,
User field 1=Userfield1,
User field 2=UserName,
User field 3=SICCodes,
LastModificationTime=SyncStampP,

[PrimaryKeys]
# Format: column=newstatement
Perskey=select * from outlookcontactspam

[MandatoryKeys]
# Format: column=newstatement

[WhereFilter]
# Format: WHERE=statement
WHERE=

[OptionsWhereFilter]
# Format: WHEREOPTIONS=X,X 0 or 1 IgnoreForUpdate,IgnoreForDelete
WHEREOPTIONS=0,0

[FolderOptions]
# Format: OPTIONS=X,X,X. 0 or 1 values slash for advanced options in format:NOPRIVATE,NOOUTLOOKUPDATE,NODBUPDATE,,,DELOUTLOOK_ITEM,DELDB_RECORD,BLOCKDELETECONFIRM,DBTIMEOUT,BLOCKDB_DELETECONFIRM\USE_LASTMODIFICATION_TIME,CONFLICT_RESOLUTION
OPTIONS=0,0,0,,,0,0,0,30,0,0,0,0,0,0\1,3

# Format: InsertSQLCmd=statement,UpdateSQLCmd=statement,DeleteSQLCmd=statement
[InsertSQLCmd]
InsertSQLCmd=

[UpdateSQLCmd]
UpdateSQLCmd={call GC_UpdateContPers('@Company','@BusinessStreetAddress','@BusinessStreetAddress2','@BusinessState','@BusinessPostalCode','@BusinessCountry','@BusinessCity','@FirstName','@LastName','@BusinessPhone','@FaxPhone','@Userfield1','@Title','@CarPhone','@HomePhone','@Categories','@UserName','@EMailAddr','@WebAddr','@Perskey','@Acc','@SicCodes','@Secrtary','@Notes','@MiddleInitial','@BusinessPhone2','@SyncStampP')}

[DeleteSQLCmd]
DeleteSQLCmd=
*******************************************
SQL Stored Procedure:

CREATE PROCEDURE GC_UpdateContPers 

(@Company varchar(100),
@BusinessStreetAddress VARCHAR(100),
@BusinessStreetAddress2 VARCHAR(100),
@BusinessState VARCHAR(100),
@BusinessPostalCode VARCHAR(100),
@BusinessCountry VARCHAR(100),
@BusinessCity  VARCHAR(100),
@FirstName VARCHAR(100),
@LastName VARCHAR(100),
@BusinessPhone VARCHAR(100),
@FaxPhone VARCHAR(100),
@uSERFLD1 VARCHAR(100),
@Title VARCHAR(100),
@CarPhone VARCHAR(100),
@HomePhone VARCHAR(100),
@Categories VARCHAR(100),
@UserName VARCHAR(100),
@EMailAddr VARCHAR(100),
@WebAddr VARCHAR(100),
@Perskey   INT,
@Acc varchar(100),
@uSERFLD3 VARCHAR(100), 
@Secrtary VARCHAR(100),
@Notes VARCHAR(100),
@MiddleInitial VARCHAR(100),
@BusinessPhone2 VARCHAR(100),
@SyncStampP datetime
)

as
SET NOCOUNT OFF


DECLARE @VAcc INT
DECLARE @VSYNCSTAMPP DATETIME


DECLARE @return varchar(255)
DECLARE @error int --hold error value
DECLARE @errornew int
DECLARE @errdesc varchar(255)

select @return = ""
select @error=0
select @errornew=0


select @errornew=@@ERROR
if @errornew<>0
begin
	select @error = @errornew
end


BEGIN TRANSACTION 

/*  0 --COMMIT, -1 if ROLLBACK */
select @return = ""
select @error=0
select @errornew=0

SET ROWCOUNT 1

IF @PERSKEY IS NOT NULL AND @ACC IS NOT NULL BEGIN
	SELECT @VSYNCSTAMPP = (SELECT SYNCSTAMPP FROM TBLPERSONEL WHERE PERSKEY = @PERSKEY)
	/* never been updated */
	IF @VSYNCSTAMPP  is  NULL BEGIN 
		 UPDATE TBLPERSONEL SET SECRTARY = @Secrtary, SYNCSTAMPP = @SYNCSTAMPP WHERE PERSKEY = @PERSKEY
		 SELECT @ERRORNEW=@@ERROR IF @ERRORNEW <> 0 BEGIN SELECT @ERROR = @ERRORNEW END
	 END
	IF @VSYNCSTAMPP IS NOT NULL BEGIN
		IF @SyncStampP > @VSYNCSTAMPP BEGIN 
			UPDATE TBLPERSONEL SET SECRTARY = @Secrtary, SYNCSTAMPP = @SYNCSTAMPP WHERE PERSKEY = @PERSKEY
			SELECT @ERRORNEW=@@ERROR IF @ERRORNEW <> 0 BEGIN SELECT @ERROR = @ERRORNEW END
		 END
/* will do all personnel fields when done  testing */
	END
	SELECT @VSYNCSTAMPP = (SELECT SYNCSTAMPC FROM TBLCONTACTS WHERE ACC = @ACC)
/* will do all contat fields when done testing */
	/* never been updated */
	IF @VSYNCSTAMPP IS  NULL BEGIN
		 UPDATE TBLCONTACTS SET ADDRESS2 = SUBSTRING(@BusinessStreetAddress2,1,37), SYNCSTAMPC  = @SYNCSTAMPP WHERE ACC = @ACC  
	END	
	IF @VSYNCSTAMPP IS NOT NULL BEGIN
		IF @SYNCSTAMPP > @VSYNCSTAMPP BEGIN UPDATE TBLCONTACTS SET ADDRESS2 = SUBSTRING(@BusinessStreetAddress2,1,37), SYNCSTAMPC  = @SYNCSTAMPP WHERE ACC = @ACC  END
	END
END
/* add logic to insert new contacts into tblContactsRequest */

SET ROWCOUNT 0


IF (@ERROR <> 0)
BEGIN
    RAISERROR (50001,1,1)
    SELECT RET="ROLLBACK"
    ROLLBACK TRANSACTION


END

IF (@ERROR=0)
BEGIN
    SELECT RET="COMMITTED"

    COMMIT TRANSACTION
END 
Administrator
2007-05-06T13:21:00Z
Hi,

-the SQL Statement for primary key is wrong!
select * from outlookcontactspam, this will return all table records ,instead of a new, unique primary key value!

-SET NOCOUNT OFF
GeniusConnect will not be able to detect how many records has been updated.
Murray, Pam
2007-05-06T21:29:00Z
Can you help me out on the select statement format? Thank you.
Administrator
2007-05-06T23:34:00Z
I see your primary key is an Integer.

This will work:
select MAX(PERSKEY)+1 from ....

Please see also help topic:Mandatory keys
https://www.geniusconnec...aryandMandatorykeys.html