Lotus Notes and SQL Server 2008, ODBC

I have been googling how to connect to Lotus Notes via SQL Server and I am totally confused.  Is there software that has to be installed on the Lotus Notes server ?  I have no idea where that server is, would it be a windows server or a unix server ?

Legacy applications exist on our Lotus Notes.  The concept of having applications installed on an email server is a foreign one to me.   So I am trying to access the data in those applications.  Not email data.
DemosthenesAsked:
Who is Participating?
 
Sjef BosmanGroupware ConsultantCommented:
There are 2 different client installation packs: one contains only the Notes client, the other contains the Notes client, the Domino Designer and the Domino Admin clients. It is a different download, from the IBM site. But you should already have it, because most admin chores are done using the Admin client, so it must have been installed on one of your PCs I guess...
0
 
Matt VCommented:
Notes has it's own internal database structures.  Any application written in Notes/Domino would be storing it's data inside a Notes database.  It will have a different structure than an email database, but is essentially treated the same way.
0
 
Sjef BosmanGroupware ConsultantCommented:
Let's first deal with the widespread misconception about Lotus Notes: in short, it is not a mail server. In fact, it is much more: the server (Lotus Domino) is a distributed database server with many functions. It can replicate databases to other servers, keeping both ends up to date. It allows clients (Lotus Notes) a secure access to the server and the databases. One type of database is a mail database, and the Domino server supports sending and receiving mail, where mails are stored in or fetched from a database. It allows mail clients based on POP3 and IMAP. It has connectors to legacy systems (SAP, Oracle, ODBC). And finally: every database is programmable to a great extent, where the code and design elements are stored inside the database, and both Notes and Domino have a COM API accessible from VB. Java can also be used. Very often, a database IS an application, and databases are portable between various Operating Systems like Windows, Linux, AIX, Solaris, AS/400, etc.

>  The concept of having applications installed on an email server is a foreign one to me.
In other words: it's the other way around. A Domino server is an application server that services mail as well.

Now, about your question. If I understand you correctly you want an external application to connect to the Domino server or a Notes client to open a database and get some data. The good news: that's possible. The not-so-good news: it'll take some study and development, because you have to get acquainted with the Domino Object Model.

There are lots of examples on the Internet, too many actually, but if you can give us a description of what your goals are, we could pick a few appropriate ones.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
ThomasMcA2Commented:
Most of @Sjef's suggestions are spot-on, but in this case his suggestion is a little overkill. ;)

You can access the Lotus Notes data from SQL Server via ODBC using the NotesSQL tool. NotesSQL must be installed on the Domino server, and a Notes client must be installed (and configured, IIRC) on the SQL Server. Your ODBC code will then use the ID and Password of the Notes user/account that was installed on the SQL Server.

We use this process to pull Lotus Notes data into our BI (Business Integration) server.
0
 
Sjef BosmanGroupware ConsultantCommented:
Ehhhhhhh.... Now why... :-$

A Windows-only solution, apparently. With a lot of potential (I hate that word!). Good suggestion!
0
 
DemosthenesAuthor Commented:
I believe I got the ODBC driver after what seemed like a great many clicks on the IBM site.  I have no idea if I downloaded the right thing.  I try to set up a 32 bit ODBC connection I get "The setup routines for the Lotus Notes SQL Driver (*.nsf) ODBC driver could not be loaded due to system error code 193".  I try 64 bit and I get "The setup routines for the Lotus Notes SQL Driver (*.nsf) ODBC driver could not be loaded due to system error code 126: The specified module could not be found. (C:\NotesSQL\nsqlc32.dll)."  A lot of googleing seems to talk about a notes.ini file.  I have no notes.ini file anywhere on my system.  I have several Notes2.ini, notes2.ini files in very strange directories like "C:\Program Files (x86)\IBM\Lotus\Notes\framework\rcp\eclipse\plugins\com.ibm.rcp.base_6.2.3.20110915-1350\linux\x86" and "C:\Program Files (x86)\IBM\Lotus\Notes\framework\rcp\eclipse\plugins\com.ibm.rcp.base_6.2.3.20110915-1350\macosx\x86".
0
 
Sjef BosmanGroupware ConsultantCommented:
Your system?? NotesSQL is supposed to be installed on the same system as where the Domino server is installed.
0
 
DemosthenesAuthor Commented:
Sjef Bosman.  I was trying to set up a linked server in SQL server, or an ODBC connection from "my system" a server with SQL server and SharePoint and several other things, to connect TO Lotus Notes.  Is this not the right thing to do ?  I have no idea how to get to the Domino Server.  What is that ?  Is it on Unix or Windows or can it be either ?
0
 
Sjef BosmanGroupware ConsultantCommented:
Can be on either, so you do have to ask your IT department to help you. No ways around that, I'm afraid.

Otherwise, you're back on square 2 (square 1 being reserved for hopeless cases), where you have to do some development when ODBC access to Domino is out of the picture.
0
 
DemosthenesAuthor Commented:
I am in IT.  Our Lotus Notes person retired.  I got on the server and installed NotesSQL8.5.3_w32.   I then try to set up an ODBC connection using that driver and I get, "The setup routines for the Lotus Notes SQL Driver (*.nsf) ODBC driver could not be loaded due to system error code 126."
0
 
DemosthenesAuthor Commented:
I fixed the path in control panel to point to notes.ini (found this from google).  Now I have an ODBC connection set up, on the server, pointing to the App, or DB that I am interested in.  I am kinda stumped on where I go now.  Should I install SQL Server on this machine ?  And then link the DBs via the ODBC connection ?  Or can I use another SQL server to now connect to this machine ?
0
 
Sjef BosmanGroupware ConsultantCommented:
Ok, sorry. Can you find the notes.ini file on the server, and is there indeed a server process for the Domino server running? Just checking...

Here a hint that suggests to include the directory with the notes.ini file to the PATH variable: http://www-10.lotus.com/ldd/nd8forum.nsf/22bda8373a3a2e798525752e00727c7b/2d18515322e2dd388525748f00400a93?OpenDocument
0
 
Sjef BosmanGroupware ConsultantCommented:
Ah, good. When it runs, you should be able to use e.g. Access or Excel to open a database using the ODBC connection.

Here's a tutorial: http://www.techrepublic.com/article/application-integration-create-an-odbc-connection-to-a-lotus-notes-database/
0
 
ThomasMcA2Commented:
My apologies, guys. It's been a long time since I setup my NotesSQL environment. I have NotesSQL installed on both servers, but I don't think it is needed on the Domino server. Having it installed on both confused me when I researched my own configuration.

Once NotesSQL is installed on your SQL server, you can use the normal Windows ODBC Administrator to add an ODBC connection to your Notes/Domino server. The documentation mentions the "NotesSQL Authentication List Manager" but I don't think I used that.
0
 
DemosthenesAuthor Commented:
Capture.JPGI dont undetstand the NotesSQL Authentication List Manager.  It asks for a path to notes.ini, I found that.  Then you have to "Add user...".  Then you do that it says Location of Notes ID file for this User.  Then Notes user name for this User.  What ??  Can anyone explain, or just tell me what username and password I need to type in when I connect to the ODBC.  It requires a password.
0
 
ThomasMcA2Commented:
Just to clarify, ODBC needs drivers to talk to each back-end database server. For example, Windows has default drivers that let you use ODBC to connect to text files, Excel workbooks, and Access databases. In order to connect to any other database engine (such as Oracle, DB2, or even Lotus Notes/Domino) you need a driver installed that knows how to "talk" to that particular database engine.

Knowing that, you may have realized that something needs to be installed on your SQL Server so it knows how to "talk" to Notes/Domino. That "something" is NotesSQL. Did you install that on the SQL Server?

Let's skip the ALM thing for now. Once NotesSQL is installed on the SQL Server, open the ODBC Administrator and create a new config that points to your Notes database.ODBC Setup
0
 
DemosthenesAuthor Commented:
I dont think this is going to work without a user.
Capture1.JPG
I then go to SQL server and create a linked server, and then try to look at any of the tables, and it shows no data.
Capture.JPG
Do you know how to add a user ?  When I try to add a user it comes up with a really strange screen and I do not know what to put in the fields.  It first asks for a Notes ID file.  I point it there, then it fills in the "User name:" field.  Then it puts you in the "Password:" field.  I dont know what the password would be, the user = a file on the disk ?  And the password would be ..... what ?  I do not understand the concept of selecting  a file as a user id.
Capture2.JPG
0
 
ThomasMcA2Commented:
I do not understand the concept of selecting  a file as a user id.

Notes stores the credentials for an account in an encrypted file. The default file name is user.id. When a user logs into a Notes client, Notes compares the entered password to the value that is stored within the ID file. This lets Notes authenticate a user locally, before it even tries to communicate with a server.

When you clicked on the drop-down next to Domino server, did it prompt you for a Notes password? That is the password that you put in the Password field below.

Note that you may want to adjust the 2 "Max length" fields at the bottom. Those define the maximum column width (i.e., how many characters) Domino will send for each column. Any column/field that contains more than those maximum values will get truncated during ODBC retrieval.
0
 
DemosthenesAuthor Commented:
No it didnt prompt me for a password.  The dropdown is able to find all our Lotus Notes servers.  The name of our server is EANOTES01.  I guess there is a user who is named EANOTES01 ?  And that user has a file, and I am guessing in that file is an encrypted password ?  I  may be able to contact someone and find out what that password is, if EANOTES01 is indeed a user.
0
 
Sjef BosmanGroupware ConsultantCommented:
EANOTES01 is most likely a server name. You need to use a user id.
0
 
Sjef BosmanGroupware ConsultantCommented:
Or generate a new one, using the Domino Admin client. To do that, you need a certifier id-file (cert.id usually) and its password.
0
 
DemosthenesAuthor Commented:
I dont see the Domino Admin client anywhere.  Is it something I have to download ?
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.