GroupWise Adress book and MS-SQL

Posted on 2005-05-13
Medium Priority
Last Modified: 2008-01-09
Is there a way to update (Add/Delete/Update) GroupWise address beek from MS-SQL e.g. Stored Procedure or DTS?
Question by:talal
  • 6
  • 4
  • 3
  • +2

Author Comment

ID: 14001362
of course... beek = book :P
LVL 34

Expert Comment

ID: 14003304
What's DTS?

What VERSION of GroupWise?

When you say "Address Book" do you mean an individual user's Personal Address Book, or are you referring to the GroupWise System Address Book?

Are you looking for real-time updates, or updated being performed in batch at a regular time?

What other tools do you have in your environment? For example, are you using Novell's Identity Manager to unify disparate systems (like eDirectory and AD)?
LVL 34

Expert Comment

ID: 14003305
BTW, this Question is probably best-asked in the GroupWise TA --> http://www.experts-exchange.com/Applications/Groupwise/

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 14003662
DTS is a Data Transform Service in MS-SQL server.
GroupWise 6.5

I have multiple User Defined Addressbooks that needs to be updated when the corresponding record in the database updates. I will take care of TRIGGERS to the table. But i need a way to insert/Update/Delete from GroupWise' addressbook from MS-SQL.

This question can be asked in MS-SQL section OR/AND GroupWise section. Is there a way to MOVE it or copy it there?
LVL 34

Expert Comment

ID: 14004768
You'd have to open two separate Questions in order to get them into two separate TAs. However, you can open a "pointer" Question that just contains a link to your "real" Question.

You can post a (free) Question in the Community Support TA (http://www.experts-exchange.com/Community_Support/) and ask for this Question to be moved to the GroupWise TA.
LVL 34

Accepted Solution

PsiCop earned 1500 total points
ID: 14004776
You haven't answered my query as to if you have Novell Identity Manager or some other meta-directory tool that allows you to integrate disparate data sources.

Your response to my query regarding which Address Book(s) you want to update is ambiguous. In GroupWise, there are basically two types of Address Books within the system - the GroupWise System Address Book, built by the MTA, and not alterable by the users. The users also have Personal Address Books, including Frequent Contacts (which is built automatically by the client software). Which one are you looking to update?

Also, can your SQL processes update a directory accessible via an LDAP v3-compliant interface? If so, then instead of trying to import the data in real-time to GroupWise, why not simply configure the GroupWise client to use the LDAP data source as another Address Book?
LVL 35

Expert Comment

ID: 14005695
Have you checked into the GroupWise programming tools available from Developernet?

http://developer.novell.com/groupwise/   is the "Develop to GroupWise" page.

http://developer.novell.com/ndk/gwsdk.htm  is the page for the GroupWise SDK

Since you're using GroupWise 6.5 and you want to update personal address books, you'll probably want to use C++ for the program so you can make it a trusted app.

You could also check into the Advansys Formativ products at http://www.advansyscorp.com   I believe they have an ADO method, and I know Formativ can be used to do all sorts of address book manipulation.  Since it's really made to enhance the GroupWise client, it would probably require the GroupWise client installed on your MSSQL server box.

If you want a 2-way sync between your database and your personal address books (if one is updated, so is the other) then your answer to PsiCop's query regarding Novell Identity Manager would be helpful.  XML can do lots of things, and IM is made for 2-way sync between disparate databases, as PsiCop mentioned.

Author Comment

ID: 14006737
Hello ShineOn;
Although http://www.advansyscorp.com seem to be interesting, however i am not familiar with how it works so i am a bit hesitant to install on client env.

I asked the client about Novell Identity Manager and i will post as soon as i get a reply.

I am, mostly, a database developer so my ideal solution would be to "LINK" or have an update mechanism to GroupWise' address book. XML, ADO, etc or anything that enables me to update GroupWise' addressbook records. The requirement is one way update.

Thank you

Expert Comment

ID: 14007497
Identity Manager is the tool that you want, it is a multi-database connector that can synchronize the GW Address Book with a myriad of other sources, including SQL, via XML.


LVL 19

Expert Comment

ID: 14009200
If you do not have Novell Identity Manager, you can implement LDAP Address Book (you will need help from your sys adminso on this). Once LDAP address book has been implemented you can perform those updates from SQL server rather easily.

Author Comment

ID: 14013493
I stayed up all night but finally i got it to work.
I ended up using a DTS package - > ActiveX VB.

It is working like a charm. I am testing it now .. when done i will post the solution here so others can use it too.

The reason i chose this solution is because:
1. Nothing new to buy/install for the client
2. COMPLETE fluxibility for mapping fields or processing logic.

It solved my problem because i can easily schedule it and it will not be a bother to te user

Thanks for everyone for your help however PsiCop put alot of time and efford on this matter. So i will give the points to him.

PsiCop just give me a day or so to make sure everything works :P i might need your help still :)

Thank you,
LVL 35

Expert Comment

ID: 14013956
'Scuse me, but if you solved it yourself, after you post the solution, you *should* ask a moderator to close it with a refund.  If you *really* want to give away points, you should do it for a *good* reason.  

I don't like to be picky, but I feel like it today.  I won't complain to the mods if you do whatever you want, but I'm feeling a bit grumpy today so I will speak my mind here.

I actually pointed you toward several programming options.  You obviously chose a programming option.   PsiCop essentially asked a bunch of questions, some of which you answered.  I don't see how that rates giving all the points to PsiCop.

Like I said, do whatever you want.

Author Comment

ID: 14014034
:-S The question i sked was help on Stored Proc or DTS and that was the direction i ended up going.

I appreciate your pointers specially http://www.advansyscorp.com. I may use them in future projects.

Anyways. I will wait a day or so to make sure that my solution WORKED for the client first then i will decide on this question.

Thank you,
LVL 35

Expert Comment

ID: 14014540
"The question i sked was help on Stored Proc or DTS and that was the direction i ended up going."

Don't see any advice in either direction from any of us.

Still grumpy :P  Nothing personal.

Author Comment

ID: 14063470
Here is the code i ended up using (of course this is reduced to show the point only, i removed the bigger pivture of the application.. But will demo the addition/deletion of GroupWise from MS-SQL DTS:

      Set objApplication = CreateObject("NovellGroupWareSession")
      Set GWRootAccount = objApplication.MultiLogin(UserName, "/ntservice", Password, 1)

      ' **** 1. DELETE ****'
      set GWAddressBook = GWRootAccount.AddressBooks(trim(rsTemp("BookName")))
      Dim oEntry
            For each oEntry in GWAddressBook.AddressBookEntries
      ' **** ADD THE RECORDS ****'
      sSqlTemp="SELECT * FROM vw_GroupWise order by BookName, DisplayName"
      set rsTemp = CreateObject("ADODB.Recordset")
      rsTemp.Open sSqlTemp, conn, 3, 1

      While not rsTemp.EOF
         'Loop through the Address books
         If sActiveBook <> rsTemp("BookName") & "" Then
            sActiveBook = rsTemp("BookName") & ""
            set GWAddressBook = Nothing
            set GWAddressBook = GWRootAccount.AddressBooks(trim(sActiveBook))
         End If

         sDisplayName = trim(rsTemp("DisplayName") & "")
         sEmailAddress = trim(rsTemp("EmailAddress") & "")
         sFirstName = trim(rsTemp("FirstName") & "")
         sLastName = trim(rsTemp("LastName") & "")
         sAddress = trim(rsTemp("Address") & "")
         sCity = trim(rsTemp("City") & "")
         sState = trim(rsTemp("State") & "")
         sOfficePhone = trim(rsTemp("OfficePhone") & "")
         sFaxNumber = trim(rsTemp("FaxNumber") & "")
         sOrganization = trim(rsTemp("Organization") & "")
         sDepartment = trim(rsTemp("Department") & "")
         sTitle = trim(rsTemp("Title") & "")
         Set objAddrEntry = GWAddressBook.AddressBookEntries.Add(sDisplayName, sEmailAddress, "MAPI", 1)

         With objAddrEntry.Fields
            .Add "First Name", 1, sFirstName
            .Add "Last Name", 1, sLastName
            If sAddress <> "" Then
               .Add "Address", 1, sAddress
            End If
            If sCity <> "" Then
               .Add "City", 1, sCity
            End If
            If sState <> "" Then
               .Add "State", 1, sState
            End If
            If sZip <> "" Then
               .Add "ZIP Code", 1, sZip
            End If
            If sOfficePhone <> "" Then
               .Add "Office Phone Number", 1, sOfficePhone
            End If
            If sFaxNumber <> "" Then
               .Add "Fax Number", 1, sFaxNumber
            End If
            If sOrganization <> "" Then
               If blnUseBuiltInOrganizationField = 1 Then
                  .Add "Organization", 1, sOrganization
                  '.Add sGWOrgField, 1, sOrganization
                  'SET THIS FIELD REGARDLESS (See below)'
               End If
               '.Add sGWOrgField, 1, sOrganization ' Talal: removed to speed things up
            End If

            If sDepartment <> "" Then
               .Add "Department", 1, sDepartment
            End If
            If sTitle <> "" Then
               .Add "Title", 1, sTitle
            End If
         End With


Hope this helps someone someday

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
We aren’t perfect, just like everyone else.  Check out the email errors our community caught and learn the top errors every email marketer should avoid.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question