Verify the configuration file: C:\Program Files\Oncontact Software\bin\oncontact.cfg
:
<Oncontact> <profiles> <current_profile>oncontact</current_profile> <profile_list>oncontact</profile_list> <profile profile_name="oncontact"> <connection_type>MSSQL</connection_type> <vendor>MSS</vendor> <connect_string>server=CRMSERVER\ONCONTACT;uid=sa;pwd=orlando;database=oncontact;application name=oncontact;pooling=true;</connect_string> <lock>RU</lock> </profile> </profiles> <environment> <application_authentication>Oncontact</application_authentication> <login_dialog_user>jgrabbe</login_dialog_user> </environment> <parms> <log_exceptions>Y</log_exceptions> <serial_suffix>CUS</serial_suffix> <agents_dir>agents</agents_dir> <agents_clear>true</agents_clear> </parms> <reportprofiles> <reportprofile profile_name="oncontact"> <connection_type>OLDEDB</connection_type> <connection_provider>SQLOLEDB</connection_provider> <server>CRMSERVER\ONCONTACT</server> <database>oncontact</database> <credentials_source>db</credentials_source> <userid>sa</userid> <password>orlando</password> </reportprofile> </reportprofiles> </Oncontact>
CRMSERVER\oncontact
SELECT onci_xref_W5NQN3QCUS.s_id, oncd_company.account_number, onci_xref_W5NQN3QCUS.s_ship_to, oncd_company.ship_to, onci_xref_W5NQN3QCUS.t_company_id, oncd_company.company_id FROM oncd_company, onci_xref_W5NQN3QCUS WHERE oncd_company.company_id = onci_xref_W5NQN3QCUS.t_company_id
Or for a specific account:
SELECT onci_xref_W5NQN3QCUS.s_id, oncd_company.account_number, onci_xref_W5NQN3QCUS.s_ship_to, oncd_company.ship_to, onci_xref_W5NQN3QCUS.t_company_id, oncd_company.company_id FROM oncd_company, onci_xref_W5NQN3QCUS WHERE oncd_company.company_id = onci_xref_W5NQN3QCUS.t_company_id AND oncd_company.account_number LIKE 'GA7%'
Check for invalid record:
SELECT * FROM onci_xref_W5NQN3QCUS WHERE s_id = 'ID' AND s_ship_to = 'Ship-To' ORDER BY s_ID
Delete it if bad record exists:
DELETE FROM onci_xref_W5NQN3QCUS WHERE s_id = 'ID' AND s_ship_to = 'Ship-To'
Run the previous statements to compare the fields s_ship_to
, account_number
and company_id
between the main company table oncd_company
and the cross reference table onci_xref_W5NQN3QCUS
. The only thing that might look unusual is the leading 0's in oncd_company
that are not in onci_xref_W5NQN3QCU
, but it does not seem to matter. 0 = 0000, 0024 = 24, and so on, so if account_number
and s_ship_to
match, the company_id
is updated in onci_xref_W5NQN3QCUS
to match oncd_company
.
oncd_company.account_number
and oncd_company.s_ship_to
(assuming that this is the same criteria as the import).UPDATE onci_xref_W5NQN3QCUS SET t_company_id = (SELECT MAX(company_id) FROM oncd_company WHERE onci_xref_W5NQN3QCUS.s_id = oncd_company.account_number AND onci_xref_W5NQN3QCUS.s_Ship_To = oncd_company.ship_to)
SELECT * FROM oncd_company WHERE 0 = (SELECT COUNT(t_company_id) FROM onci_xref_W5NQN3QCUS WHERE oncd_company.company_id = onci_xref_W5NQN3QCUS.t_company_id)
Or by identifying a specific account number:
SELECT * FROM oncd_company WHERE 0 = (SELECT COUNT(t_company_id) FROM onci_xref_W5NQN3QCUS WHERE oncd_company.company_id = onci_xref_W5NQN3QCUS.t_company_id) AND oncd_company.account_number LIKE 'GA76%' ORDER BY company_name_1
Note: The result of this statement should be smaller but it may not be 0. Companies can be entered manually in oncd_company
, and may not be in the cross reference table.
Removing bad characters from the file \\FILESERVER\Act\Weekly Updates\weeklyupdate.txt
.
notes
field, so that the import works correctly. Verifying the data:
NOTE: It is possible to increase the column length of the source columns in the data source to be equal to that of the target OnContact columns. Example: Increase your source columns A/R_Comments
and Sales_Comments
, both, from 100 char to 250 char, since the target custom OnContact comments
columns are both nchar 250.
From: Adam W. Howell [mailto:ahowell@audina.net] Sent: Tuesday, March 18, 2008 1:50 PM To: Wessel, Elizabeth Subject: RE: weeklyupdate file
Wow I feel really silly now. I never caught that. It makes total since now that I think about it too b/c every time they ask me to fix something it is always an account number that starts with a letter after P from what I can recall. I ran the import with the file and got 4015 rows. Thanks again for all your help. I will do some investigating into that record to see what the problem is. I have a feeling it may have something to do with the '/ /' and '\ \' around that phone number. I will let you know.
From: Wessel, Elizabeth [mailto:Liz@oncontact.com] Sent: Tuesday, March 18, 2008 2:29 PM To: Adam W. Howell Subject: weeklyupdate file
Hi Adam,
I tested this file and found that there was a 'bad' row of data in the file that appeared to prevent ~ 1300 rows from importing. When I tested here, I noticed that the initial 'Loading file..“ window showed that it only loaded 2738 / 4016 rows (please see screenshot below) loaded. So, the problem lies not with the import, but the data.
I then let the import run the 2738 rows in, then found where the import stopped import (on the PA3272 records) by selecting from oncd_company and order by updated_date. 'Better Hearing Centers (2083)' was at the top, so I found it in the text file, and removed the row under it, “PA3272”,”AUDIOLOGY & HEARING (H1013)“
The specific row is:
"PA3272","AUDIOLOGY & HEARING (H1013)","AID DISPENSERS","642 BROAD STREET","CLIFTON","NJ","07013","0418","PAT BIONDI","02","9737775335","12/27/07","","REC. BY MIKE POOLE //(973)777-0011\\","","N30"
I am not entirely sure what is wrong with this row. If you DTS this file to a SQL table, you'll see that the rows are off-kilter at some point.
Please re-run the attached file, and let me know if you still are not able to import all 4015 (I removed the 'bad' one) or not, and specifically the row with account WA8600. Please also let me know if you have anyt questions.