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>

• Install MS SQL Server Management Studio Express (free download from Microsoft).
• Connect to database CRMSERVER\oncontact
• Perform a database backup.

• If the data in the Oncontact database looks fine, but there are still issues, then start by comparing this data:

    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.

• The dedup utility only targets oncd_company.account_number and oncd_company.s_ship_to (assuming that this is the same criteria as the import).

• Verify that that the cross ref table is not empty when running the import.

• Perform the following queries, but test completely on the test database first. Before running this against your production database, be sure to back it up just as a safeguard.
• Fix the integrity issue. Perform this query to update the data:

    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)

• Verify the count. When the update is completed, run the following to see how the row count has changed:

    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.

• Remove the '/ /' and '\ \' from around some text in the notes field, so that the import works correctly.
• Verify that there are no spaces before the '/ /', in case the system sees it as a “Tab”.

Verifying the data:

• Look at the 'Loading file..' window. It is a good way of checking to see if the data is even making it to the import, and import agents.

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.