|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss SalesLogix synchronization and remote database management. View the code of conduct for posting guidelines.
|
|
|
|
Added Accounts etc. not synced to Remote Office
Posted: 18 Jul 06 7:55 AM
|
fiogf49gjkf0d We are running V6.2 SP3 and have our main SLX DB in the UK with a Remote office in the US. We have synchronisation set up to run every 30 minutes between 0600-1900 UK time, and it seems to work fine for Accounts, Contacts etc entered by users or imported from Scribe.
However, we have recently implemented an interface (which is a VB6 Application running daily at 2300) from our Subscriptions system. The VB app uses 'legal' SLX techniques (using the SLX OLEDB provider) to add Accounts, Addresses, Contacts etc. but none of the added records are synced to the US office.
I suppose I have two questions:
1. If we run the interface at 2300, outside of the sync period, should it catch up when it next syncs at 0600?
2. If it should, where should we look for the reason the new records don't sync?
Thanks in advance for any advice.
|
|
|
|
Re: Added Accounts etc. not synced to Remote Office
Posted: 18 Jul 06 9:12 AM
|
fiogf49gjkf0d Yes the SalesLogix sync components should catch up at 0600
Can you post the Connect String that the exteranl interface uses to connect to the SalesLogix DB?
What is synced to the Remote Office is drive by OwnerShip and SubScription rules. Do the records that the external interface add meet the OwnerShip and SubScription rules for the Remote Office?
Does the Extranal Interface create log files in the WGLogs folder when it runs? |
|
|
|
Re: Added Accounts etc. not synced to Remote Office
Posted: 18 Jul 06 9:55 AM
|
fiogf49gjkf0d Thanks for the reply!
The connect string is this:
Provider=SLXOLEDB.1;User ID=admin ; Password=*****;Data Source=UK01DB10;Initial Catalog=SalesLogix;Extended Properties=Address= ; Port=1706;LOG=ON;CASEINSENSITIVEFIND=ON;
[I had to add some spaces in the above, as some smilies appeared in it  ]
We believe the rules are set up correctly, our only doubt is that the CREATEUSER is admin, and wondered if this might be a problem?
No it does not seem to create log files as far as I can see
|
|
|
|
Re: Added Accounts etc. not synced to Remote Office
Posted: 18 Jul 06 10:32 AM
|
fiogf49gjkf0d The connect string is this:
Provider=SLXOLEDB.1;User ID=admin ; Password=*****;Data Source=UK01DB10;Initial Catalog=SalesLogix;Extended Properties=Address= ; Port=1706;LOG=ON;CASEINSENSITIVEFIND=ON; |
|
This looks OK. The LOG=ON is the key thing I wanted to verify.
| We believe the rules are set up correctly, our only doubt is that the CREATEUSER is admin, and wondered if this might be a problem? |
|
Unless CreateUser is references in the SubScription rules it should not matter.
| No it does not seem to create log files as far as I can see |
|
If WGLogs are not created then nothing will sync out to the reports!!!
Are there any errors in the NT Event Application Log?
How is the Extranal interface updating the DB (Literal SQL, RecordsSets, etc..)?
How is the external interface formatting dates? |
|
|
|
Re: Added Accounts etc. not synced to Remote Office
Posted: 18 Jul 06 11:55 AM
|
fiogf49gjkf0d Thanks again for looking at this.
Date formatting is being done by SQL Server, I give it unambiguous dates like '13 JUL 2006'
I realise that if the Sync log isn't updated then nothing will be synced, question is why isn't it writing them?
Which server's Event log should I look in?
The interface is creating a ADODB Command Object:
Set adoCommand = New ADODB.Command
adoCommand.ActiveConnection = m_adoConnection2
adoCommand.CommandType = adCmdText
and then doing this:
With adoCommand
' Add an ACCOUNT Row
' ------------------
sSQL = "INSERT INTO SYSDBA.ACCOUNT (ACCOUNTID, ADDRESSID, SECCODEID, "
sSQL = sSQL & "ACCOUNT, ACCOUNT_UC, STATUS, TYPE, MAINPHONE, FAX, TOLLFREE, DIVISION, IMPORTSOURCE, LEADSOURCEID, "
sSQL = sSQL & "CREATEUSER, MODIFYUSER, CREATEDATE, MODIFYDATE ) "
.Parameters.Append .CreateParameter("ACCOUNTID", adChar, adParamInput, 12, sAccountID)
.Parameters.Append .CreateParameter("ADDRESSID", adChar, adParamInput, 12, sAddressId)
.Parameters.Append .CreateParameter("SECCODEID", adChar, adParamInput, 12, sSecCodeId)
.Parameters.Append .CreateParameter("ACCOUNT", adVarChar, adParamInput, 128, sAccount)
.Parameters.Append .CreateParameter("ACCOUNT_UC", adVarChar, adParamInput, 128, UCase(sAccount))
.Parameters.Append .CreateParameter("STATUS", adVarChar, adParamInput, 64, sAccountStatus)
.Parameters.Append .CreateParameter("TYPE", adVarChar, adParamInput, 64, sAccountType)
.Parameters.Append .CreateParameter("MAINPHONE", adVarChar, adParamInput, 32, sPhone)
.Parameters.Append .CreateParameter("FAX", adVarChar, adParamInput, 32, sFaxnbr)
.Parameters.Append .CreateParameter("TOLLFREE", adVarChar, adParamInput, 32, sEighthundred)
.Parameters.Append .CreateParameter("DIVISION", adVarChar, adParamInput, 32, sOAGOffice)
.Parameters.Append .CreateParameter("IMPORTSOURCE", adVarChar, adParamInput, 24, sImportSource)
.Parameters.Append .CreateParameter("LEADSOURCEID", adVarChar, adParamInput, 12, sLeadSourceID)
.Parameters.Append .CreateParameter("CREATEUSER", adChar, adParamInput, 12, sUserId)
.Parameters.Append .CreateParameter("MODIFYUSER", adChar, adParamInput, 12, sUserId)
.Parameters.Append .CreateParameter("CREATEDATE", adDate, adParamInput, 8, myDate & " " & myTime)
.Parameters.Append .CreateParameter("MODIFYDATE", adDate, adParamInput, 8, myDate & " " & myTime)
sSQL = sSQL & addParameters(adoCommand)
.CommandText = sSQL
.Execute
|
|
|
|
Re: Added Accounts etc. not synced to Remote Office
Posted: 18 Jul 06 12:11 PM
|
fiogf49gjkf0d | Date formatting is being done by SQL Server, I give it unambiguous dates like '13 JUL 2006' |
|
The Slx Provider is fussy about dates.
| Which server's Event log should I look in? |
|
I check all systems involved --DB, System where Provider is running, and system where the program that does the inserts runs.
The interface is creating a ADODB Command Object:
Set adoCommand = New ADODB.Command
adoCommand.ActiveConnection = m_adoConnection2
adoCommand.CommandType = adCmdText
and then doing this:
With adoCommand
' Add an ACCOUNT Row
' ------------------
sSQL = "INSERT INTO SYSDBA.ACCOUNT (ACCOUNTID, ADDRESSID, SECCODEID, "
sSQL = sSQL & "ACCOUNT, ACCOUNT_UC, STATUS, TYPE, MAINPHONE, FAX, TOLLFREE, DIVISION, IMPORTSOURCE, LEADSOURCEID, "
sSQL = sSQL & "CREATEUSER, MODIFYUSER, CREATEDATE, MODIFYDATE ) "
.Parameters.Append .CreateParameter("ACCOUNTID", adChar, adParamInput, 12, sAccountID)
.Parameters.Append .CreateParameter("ADDRESSID", adChar, adParamInput, 12, sAddressId)
.Parameters.Append .CreateParameter("SECCODEID", adChar, adParamInput, 12, sSecCodeId)
.Parameters.Append .CreateParameter("ACCOUNT", adVarChar, adParamInput, 128, sAccount)
.Parameters.Append .CreateParameter("ACCOUNT_UC", adVarChar, adParamInput, 128, UCase(sAccount))
.Parameters.Append .CreateParameter("STATUS", adVarChar, adParamInput, 64, sAccountStatus)
.Parameters.Append .CreateParameter("TYPE", adVarChar, adParamInput, 64, sAccountType)
.Parameters.Append .CreateParameter("MAINPHONE", adVarChar, adParamInput, 32, sPhone)
.Parameters.Append .CreateParameter("FAX", adVarChar, adParamInput, 32, sFaxnbr)
.Parameters.Append .CreateParameter("TOLLFREE", adVarChar, adParamInput, 32, sEighthundred)
.Parameters.Append .CreateParameter("DIVISION", adVarChar, adParamInput, 32, sOAGOffice)
.Parameters.Append .CreateParameter("IMPORTSOURCE", adVarChar, adParamInput, 24, sImportSource)
.Parameters.Append .CreateParameter("LEADSOURCEID", adVarChar, adParamInput, 12, sLeadSourceID)
.Parameters.Append .CreateParameter("CREATEUSER", adChar, adParamInput, 12, sUserId)
.Parameters.Append .CreateParameter("MODIFYUSER", adChar, adParamInput, 12, sUserId)
.Parameters.Append .CreateParameter("CREATEDATE", adDate, adParamInput, 8, myDate & " " & myTime)
.Parameters.Append .CreateParameter("MODIFYDATE", adDate, adParamInput, 8, myDate & " " & myTime)
sSQL = sSQL & addParameters(adoCommand)
.CommandText = sSQL
.Execute
|
|
If you pass the dates as string, which it looks like you are, you need to make sure the dates are formatted as "YYYYMMDD HH:NN:SS". |
|
|
|
Re: Added Accounts etc. not synced to Remote Office
Posted: 19 Jul 06 7:22 AM
|
fiogf49gjkf0d Thanks for all the ideas, but I'm not much closer to finding the cause of this. I don't think its the dates, because the manually added (Using the SalesLogix Client) accounts have the same format. In fact I added an Account manually which looks almost exactly the same as the ones added by the Interface, and it WAS synced OK.
I am suspecting that it is the way I am connecting to the DB and adding the rows that is somehow at fault.
I made a mistake earlier with the connect string BTW, it should have ;RWPass=******; on the end, the one I posted was to do the intial RO connection so I can get the RW password using slx_RWPass()
The RW connection string is:
Provider=SLXOLEDB.1;User ID=admin; Password=******; Data Source=UK01DB01;Initial Catalog=Saleslogix;Extended Properties=Address=; Port=1706;LOG=ON;CASEINSENSITIVEFIND=ON;RWPass=******;
Could you tell me, is RWPass an Extended Property? If it's not, could it be that adding it at the end causes the LOG=ON to be ignored?
Also, the Sync process (as shown in the Monitor Console) is running every three minutes, not thirty as I said.
|
|
|
|
Re: Added Accounts etc. not synced to Remote Office
Posted: 19 Jul 06 8:27 AM
|
fiogf49gjkf0d Yes the Read/Write password is an extended property. I have never included it in my connect strings.
BTW, in your connect string the extend properties need to be enclosed in quotes like this.
Provider=SLXOLEDB.1; Password=pwd; Persist Security Info=True;
User ID=usr; Initial Catalog=database;
Data Source=SlxServer; Extended Properties="PORT=1706;LOG=ON"
|
|
|
|
Re: Added Accounts etc. not synced to Remote Office
Posted: 19 Jul 06 8:40 AM
|
fiogf49gjkf0d Thanks, I did know about putting extended properties in quotes but they have somehow disappeared, probably in the VB IDE. I will give this a go tomorrow and post result here. |
|
|
|
Re: Added Accounts etc. not synced to Remote Office
Posted: 21 Jul 06 4:56 AM
|
fiogf49gjkf0d I have found a few new things.
In this interface I am adding rows to ACCOUNT, ADDRESS, CONTACT, CONTACT_LEADSOURCE, OPPORTUNITY, OPPORTUNITY_CONTACT, OPPORTUNITY_PRODUCT, ACTIVITY and USER_ACTIVITY.
In the WGLogs directory I have seen files containing the ACCOUNTID and ADDRESSID of rows I have added, so I know that logging is going on. I cannot see any errors associated with these rows.
And in the Remote database, all the ACTIVITY and USER_ACTIVITY rows are being added OK, but none of the other tables are being updated.
Any ideas?
|
|
|
| |
| |
|
Re: Added Accounts etc. not synced to Remote Office
Posted: 21 Jul 06 8:56 AM
|
fiogf49gjkf0d The accountsummary table is what drives synchronization so this could explain some of what we seen.
Are you calling slx_setlogging to set the accountid before your issue your insert/update statements? |
|
|
|
Re: Added Accounts etc. not synced to Remote Office
Posted: 21 Jul 06 9:02 AM
|
fiogf49gjkf0d Originally posted by Frank Chaffin
The accountsummary table is what drives synchronization so this could explain some of what we seen. |
|
Seems I have much to learn. Now I'm updating the ACCOUNTSUMMARY table, the new rows seem to be syncing.
| Are you calling slx_setlogging to set the accountid before your issue your insert/update statements? |
|
No, I'm just getting an ID using slx_DBids() and then adding the row. What difference would slx_setlogging make?
Thanks to both of you for the help, this is much appreciated.
|
|
|
|
Re: Added Accounts etc. not synced to Remote Office
Posted: 21 Jul 06 9:10 AM
|
fiogf49gjkf0d Originally posted by Stuart
No, I'm just getting an ID using slx_DBids() and then adding the row. What difference would slx_setlogging make. |
|
If you use slx_logging to set the accountid then the SalesLogix synchronization will only send the account to the remote users/remote offices that are subscribed to the account. If you don't set the globalid then the records will be sent to all remotes. Which may mean that some remotes get a record that you do not want them to have. |
|
|
|
Re: Added Accounts etc. not synced to Remote Office
Posted: 21 Jul 06 9:25 AM
|
fiogf49gjkf0d | If you use slx_logging to set the accountid then the SalesLogix synchronizations will only sent the account to the remote users/remote offices that are subscribed to the account. If you don't set the globalid then the records will be sent to all remotes. Which may mean that some remotes get a record that you do not want them to have. |
|
I see, thank you for the explanation.
I have just done an Interface run, and it added 54 new Accounts, all of them were synced to the remote DB, so I think we have a result!
Thank you again  |
|
|
| |
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|