I know BCM 2007 does not support contact->account linking when using flat
files, so I gather there are 2 workarounds:

1) Create a .bcm XML formatted input file. I see this file uses GUID
linking - if I create one, how should I assign the GUID? Can I just make up
a unique 32 char hex id, or do I need a specific algorithm?

2) Import contacts and accounts separately then link them in the db via SQL.
Can anyone supply sample SQL for this, or point me to a reference?

Thanks!

Another option is to do this programmatically, if you know a .NET developer
they should be able to modify this code to do what you want.
http://officepoint.blogspot.com/2008/07/outlook-business-contact-manager-bcm_6853...

This does not directly touch the SQL database. I have to imagine that
modifying the SQL database for BCM would be an unsupported operation.

hello,

I was in same situation a few month ago, i had to import contacts and
accounts from an existing Access database in my company, it was not very
difficult to discover how it is linking but i had to test to be sure that
modifying the SQL Database directly won't affect BCM. So i did it using SQL,
and until now there is no problem so i think it is a good method even if i
did not explore entirely what happened when the link is created by BCM.

I wanted to write a post or a blog but i was not sure that it will help
somebody, i was wrong :)

So i will be happy to help you,

Hi Farouk,

How did you do it in SQL? I have been looking at it with Access and SQL
Server Management Studio Express for a little while now.

I have figured out which fields I need to update but I am still having
trouble figuring out where to pull it from. I have about 18000 contacts and
1700 accounts I need to get linked together, no way will I do them manually.

I have the linking information in a separate excel file which I have managed
to import into a SQL table as someone else suggested this would be the
easiest way to do the update.

My SQL knowledge is very limited so I'm not sure where go from here.

Any help you could give me would be greatly appreciated.

Thanks in advance,
Nancy

"farouk drif" wrote:

hello,

First, have you already import your data in BCM ? if it's done, i hope you
stored the linking information in a user field or somewhere else. Which step
are you ?

The method is to use a temporary key, stored in a non-BCM table, or in the
BCM table with the "user fields" , this key will be used to operate the link
between contact and account to link them by updating some fields in the
principal BCM table, BCM will believe that he did the link.

Could you answer my questions and i'll be able to guide you.

Hi Farouk,

Thanks for your reply.

The accounts and contacts are in the BCM but we did not store the linking
information in a user field, I have this in my original excel spreadsheet.

If necessary I can start fresh to add this information however it takes
several hours for the import to complete so I would need to leave it running
at the end of the day today.

Alternatively, I can get the contents of the excel file into a separate SQL
table, that is not a problem. Should it be in the BCM db or in a different
one?

Thanks again,
Nancy

"farouk drif" wrote:

sorry to answer only now but the newsgroup did not notify me of replies,
just to say that i'm writing a procedure to explain how to do it step by
step, because it seems that it could help a lot of people. i will release it
in a few days.

Great, I appreciate your help.

Nancy

"farouk drif" wrote:

Hi Nancy

I had to do the same thing. One of our salesmen loaded accounts and contacts
in on 22/8/2008 so I used my rudimentary SQL skills to associate the right
contacts with the right accounts, but only for the records loaded on the
22/8/2008.
This was how I did it using 3 Views to compensate for my lack of SQL skills
- you can adjust to suit. You will need admin access to the SQLserver
database.

1) Set your BCM to work Offline, and make sure no-one else is using BCM.
2) Open SQL Management Studio and connect to the BCM database server -
usually this is in the SQLServer instance called MSSMLBIZ...

A correction to my previous!

Item 12) should be

update NHUpdate set Parenttype=2, parententryid=AccGUID,
ParentContactServiceID=AccId where parenttype is null;

This method assumes that every contact has a CompanyName entry which matches
an Account Full Name. this is how it cross-references contacts with accounts.