Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Lotus Notes and SQL Server 2008, ODBC

Posted on 2014-08-18
22
Medium Priority
?
1,622 Views
Last Modified: 2014-09-09
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.
0
Comment
Question by:Demosthenes
  • 9
  • 8
  • 4
  • +1
22 Comments
 
LVL 22

Expert Comment

by:Matt V
ID: 40268493
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 40268918
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
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40271619
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
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.

 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 40271950
Ehhhhhhh.... Now why... :-$

A Windows-only solution, apparently. With a lot of potential (I hate that word!). Good suggestion!
0
 

Author Comment

by:Demosthenes
ID: 40273227
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 40273249
Your system?? NotesSQL is supposed to be installed on the same system as where the Domino server is installed.
0
 

Author Comment

by:Demosthenes
ID: 40273259
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 40273422
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
 

Author Comment

by:Demosthenes
ID: 40274226
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
 

Author Comment

by:Demosthenes
ID: 40274293
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 40274299
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 40274313
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
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40274545
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
 

Author Comment

by:Demosthenes
ID: 40276186
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
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40277944
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
 

Author Comment

by:Demosthenes
ID: 40293096
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
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40293195
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
 

Author Comment

by:Demosthenes
ID: 40293298
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
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 40293501
EANOTES01 is most likely a server name. You need to use a user id.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 40293809
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
 

Author Comment

by:Demosthenes
ID: 40312061
I dont see the Domino Admin client anywhere.  Is it something I have to download ?
0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 2000 total points
ID: 40312143
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
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

810 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