GroupWise Adress book and MS-SQL

Is there a way to update (Add/Delete/Update) GroupWise address beek from MS-SQL e.g. Stored Procedure or DTS?
talalAsked:
Who is Participating?
 
PsiCopConnect With a Mentor Commented:
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?
0
 
talalAuthor Commented:
of course... beek = book :P
0
 
PsiCopCommented:
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)?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
PsiCopCommented:
BTW, this Question is probably best-asked in the GroupWise TA --> http://www.experts-exchange.com/Applications/Groupwise/
0
 
talalAuthor Commented:
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?
0
 
PsiCopCommented:
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.
0
 
ShineOnCommented:
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.
0
 
talalAuthor Commented:
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
0
 
waybadmojoCommented:
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.

http://www.novell.com/products/nsureidentitymanager/index.html?sourceidint=productscatmenu_nsureidentitymanager

-Mojo
0
 
RanjeetRainCommented:
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.
0
 
talalAuthor Commented:
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,
0
 
ShineOnCommented:
'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.
0
 
talalAuthor Commented:
:-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,
0
 
ShineOnCommented:
"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.
0
 
talalAuthor Commented:
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
               oEntry.Delete
            Next
     
      ' **** 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
               Else
                  '.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

         rsTemp.MoveNext
      Wend


Hope this helps someone someday
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.