C# – Lotus NotesSQL Driver install, configure and connect to server through ODBC

Lotus Notes and Lotus Domino are the client and server of a collaborative client-server software platform from IBM. The ecosystem provides business collaboration functionality, including email, calendars, to do lists, contacts management, discussion forums, file sharing, instant messaging, blogs, and user directories. Like other business applications, Lotus system conducts a database system behind to manage all data objects for sharing data between internal applications. To make the system itself more friendly to 3rd applications, Lotus also provides a driver – Lotus NotesSQL – so that 3rd applications can query data from Domino server. With Lotus NotesSQL, users and application developers can integrate Domino data with their applications through ODBC. IT professionals can enable their existing ODBC-enabled applications to access data stored in a Domino database. It allows ODBC-enabled data reporting tools, database tools, and application development tools to read, report, and update information that is stored in Domino databases (NSF files).

Although Lotus is a popular system and long-time developed application, but I really don’t understand why the product is somehow really unstable. For example, for the installation of Lotus NotesSQL, I can’t find anywhere the instructions on IBM website and when I tried myself, I encountered so many errors that I couldn’t believe that a simple installation can cause so much trouble. It takes me 5 hours long to download, install, search for errors, fix them and configure this driver until it works. What a hard work! Therefore I would like to write down the steps I made for this installation. Maybe they will help you when you have to install this driver.

1. Prerequisites

I successfully made the Lotus NoteSQL work at my computer but I didn’t test my steps on other environment. Therefore the following points are not really the prerequisites but are the boundary conditions.
Operating system: Windows 7 Professional 64 bit
Lotus Client: Lotus Notes 8.5.3FP2

2. Download

The Lotus NotesSQL can be download officially from IBM website

http://www14.software.ibm.com/webapp/download/nochargesearch.jsp?k=ALL&S_TACT=104CBW71&status=Active&q=notes+sql

For getting the download link, you have to register for IBM ID and fill out tons of formula until you get what you need. So if you want to skip these unnecessary steps, you can download Lotus NotesSQL 8.5.3 from the direct links below.

You have to download the version which is same with your Lotus Notes client, not operating system. That means, if you’re using Lotus Notes 32 bit then you have to download the 32 bit version of Lotus NotesSQL. It has nothing to do with your operating system

Mirror: http://pastebin.com/38mw5NnB

3. Install

After downloading the setup files, just run it. If you receive this message

Setup cannot locate a supported version of the required Notes or Domino software.
Please visit http://www.lotus.com/NotesSQL for the product’s software requirements.

that means you are installing the false version, just choose the another.

4. Add Authentication

– Open Start menu –> Browse to Lotus Applcations –> Notes SQL –> Notes SQL Authentication List Manager –> Start it

Notes SQL Authentication List Manager

– In TextBox Path to NOTES.ini file…, browse to the notes.ini file which usually locates under the folder

C:\Users\<YOUR_USER_NAME>\AppData\Local\Lotus\Notes\Data

– Click on Display list –> Turn off the pop up dialog –> Click on Add user… –> In Add New User dialog, browse to the user.id which usually locates under the folder

C:\Users\<YOUR_USER_NAME>\AppData\Local\Lotus\Notes\Data

Add Authentication

– Click on Save list and close the dialog

5. Add environment variables

– Lotus NotesSQL has to read the notes.ini file and somehow ‘communicates’ with Lotus Notes Client. Therefore we have to add the path of Lotus NotesSQL, notes.ini and Lotus Notes Client into environment variables so that they can be resolvable from anywhere in the system.

– In Explorer, right click on your PC icon –> Properties

Properties of PC

– Click on Advanced system settings

Advanced system settings

– Go to Advanced tab of System properties dialog –> Environment Variables…

Environment Variables

– Under System variables, find Path and add path values to it. For example, in my case, paths of

notes.ini : C:\Users\nguy\AppData\Local\Lotus\Notes\Data
Lotus Notes Client : C:\Program Files (x86)\IBM\Lotus\Notes
Lotus NotesSQL : C:\NotesSQL;

Add them all to your Path environment variable and remember to separate them with ‘;’ character. You need to restart your computer so that the variables can be loaded.

Environment variables

6. Test with Microsoft ODBC Administrator

– If you have 64 bit Lotus Notes Client and install Lotus NotesSQL 64 bit driver for your 64 bit operating system, then you just simply go to Control Panel –> Administrative Tools –> ODBC Data Sources –> User DSN –> Add a new one –> you’ll find an entry for Lotus Notes Server.
– If you have a 32 bit Lotus Notes Client and a 64 bit operating system like mine, you won’t find this entry in your list because ODBC Data Sources will automatically load only the 64 bit driver. Therefore you have to start the ODBC Data Sources 32 bit from following path

c:\windows\sysWOW64\odbcad32.exe

– Then you can add your User DSN with Lotus NotesSQL. For user name option, you can use your user.id file mentioned above

Lotus Notes SQL Driver

Lotus Notes SQL Driver information

If you have following error when you try to add a User DSN

The setup routines for the Lotus NotesSQL Driver (*.nsf) ODBC driver could not be loaded due to system error code 126

Then check step 5 again, you have to add paths of notes.ini, Lotus NotesSQL and Lotus Notes client into Path variable.

7. Use Lotus NotesSQL in .NET application

– We have successfully configured the Lotus Notes SQL Driver. Through ODBC Administrator we can also build a User DSN to server. Now we try to connect to server from our application through OdbcConnection.

OdbcConnection odbcConnection = null;
try
{
	odbcConnection = new OdbcConnection();				
	odbcConnection.ConnectionString = "Driver={Lotus Notes SQL DRIVER (*.nsf)};Server=YOUR_DOMINO_SERVER;Database=YOUR_NSF_FILE;Uid=YOUR_USER_NAME_FROM_USER.ID_FILE;Pwd=YOUR_PASSWORD;MaxVarcharLen=1024";

	odbcConnection.Open();
	DataTable dataTable = odbcConnection.GetSchema("Tables");
	dataTable = odbcConnection.GetSchema("Views");

	OdbcCommand odbcCommand = odbcConnection.CreateCommand();
	odbcCommand.CommandText = "SELECT * FROM YOUR_CUSTOM_VIEW";

	DataSet dataSet = new DataSet();
	using (OdbcDataAdapter adapter = new OdbcDataAdapter(odbcCommand))
	{
		adapter.Fill(dataSet);
	}

	Console.WriteLine("Success");
}
catch (Exception e)
{
	Console.WriteLine(e);
}
finally
{
	if (odbcConnection.State == ConnectionState.Open)
	{
		odbcConnection.Close();
	}
	Console.ReadLine();
}

The parameter MaxVarcharLen is optional because my fields of table in Domino have length longer than 255 bytes (default value), I have to set this value so that I can read all content out. The code above just demonstrates how we can build a connection to Domino server and read data out such as which tables and views are available in database. I have also made a SELECT query to a view and fill data to a DataTable. That means, you can do anything what a ODBC connection allows.

8. The end

I hope that this post will help you to survive 🙂 through the installation of Lotus NotesSQL. Like other ODBC driver, Lotus NotesSQL allows you to make a SQL query, get schema,… with your code to read data out. The installation is too complex so that we can use Lotus NotesSQL for our clients, but for developing and testing purposes it is also a good candidate.

49 thoughts on “C# – Lotus NotesSQL Driver install, configure and connect to server through ODBC”

  1. @ehsan: I’m willing to help you but I don’t know where you’re stuck? Your question? If you want post code then you can use syntax below

    For posting code, you can use the syntax below

    [code lang=java]
    Your code here
    Then close your code block with (all together no space, I have to space them so that editor won't parse them)
    [/code ]
    
  2. your post is very useful. i want to access this odbc driver in lotus notes using lotus script to generate run time crystal report with parameter.Report is designed in crystal report 9 and i have window install on my system. if you have any solution please help me..

  3. @irfan Riaz: I really want to help you but I can’t because I have no idea of Lotus Note script. As you can see, I use C# for accessing ODBC Driver.

  4. Hi,
    I have problem with NotesSQl 8.5.3 x64 and MS SQL 2012 x64 (on MS SQL 2008 x64 worked correct).
    When I create Linced server on SQL MS receives a message error:
    “Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “xxxxxxx”.
    OLE DB provider “MSDASQL” for linked server “xxxxxx” returned message “[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”. (Microsoft SQL Server, Error: 7303)”

    Can you help my.

    Best regards
    Piter

  5. @Piter: That error indicates that you have a conflict between 32 and 64 bit version (client vs server or vice versa). You should check again if you’re using the correct version. Section “6. Test with Microsoft ODBC Administrator” of post above may help.

  6. @Admin
    hi I got this issue and tried your solution.
    It is really a detail one.
    However, I can not find the notes.ini under the personal folder, it only exists in C:
    otes(I installed in this folder.)
    So I use this directory as the variables, also configured this as Notes SQL Authentication List Manager.

    However, I still got an issue: ODBC error code 126, C:NotesSQL
    sqlc32.dll could not be found.

  7. @Julian: It seems to be that you don’t have problem with your path but you’re using wrong ODBC client version. Please read Section 6. in post again and check if you’re using correct ODBC version (32 bit vs 64 bit)

  8. @admin
    Thanks for your quick response.
    However, I carefully checked this before I install it.
    I just upgraded notes to 9.0.1 and the notes should be 32bit version.
    As you stated, the NotesSQL shall use a same 32 or 64 bit with your Notes, not your OS.

    So…
    I really got crazy for this.
    Any further idea you think I shall check?

  9. @Admin

    I am trying to connect Notes with the NotesSQL to generate report in Hyperion.
    Without NotesSQL, all data is locked.
    Do you have any idea if I can get structured data from Notes without NotesSQL?
    I was a developer but no any development tool now.
    : (
    Thanks again~

  10. @Julian: Yes there is another way to get data from Lotus Notes. You can make a reference to Lotus Notes Domino Interop, initialize a NotesSession –> NotesDatabase –> NotesView then read data out from a view. This method requires Lotus Notes client must be installed at same computer and an authorization is also required. I don’t have any detailed example right now but the code should look like following

    if (notesSession == null)
    {
    	notesSession = new NotesSession();
    
    	try
    	{
    		if (!string.IsNullOrWhiteSpace(Password))
    			notesSession.Initialize(Password);
    		else
    			notesSession.Initialize(&quot;&quot;);
    		return new InitializeNotesResult() { ErrorCode = InitializeNotesErrorCode.OK };
    	}
    	catch (Exception ex)
    	{
    		notesSession = null;
    		return new InitializeNotesResult() { ErrorCode = InitializeNotesErrorCode.Failed, Exception = ex };
    	}
    }
    
    ...
    
    NotesDatabase notesDatabase = notesSession.GetDatabase(LotusServer, LotusDatabase);
    
    ...
    
    NotesView notesViewCompany = notesDatabase.GetView(LotusViewCompany);
    NotesViewEntryCollection notesViewEntryCollectionCompany = notesViewCompany.AllEntries;
    NotesViewEntry notesViewEntryCompany = notesViewEntryCollectionCompany.GetFirstEntry();
    while (notesViewEntryCompany != null)
    {
    	NotesDocument notesDocument = notesViewEntryCompany.Document;
    }
    				
    
  11. thks for the great hints to get the Lotus NotesSQL 8.0 working on Win7 64bit

    Now I am still getting the LogOn window for Seagate Crystal Reports RunTime 7.0 (32 bit) (distributed reports) asking for the LogOn Parameters :Server* , database name*, username and password(values in there) despite i am passing the username and SuppressUID=”1″

    Any Idea ?

  12. @Paul: How do you connect Crystal Report through Lotus NotesSQL? Through an ODBC connection string? I think if you’re using a normal ODBC connection string, all necessary login data are already given in connection string (including password), you won’t be asked for LogOn Parameter any more.

  13. Thanks for this helpful article.
    Is it also possible to write to an nsf file and if so: what does the InsertCommand look like (CommandText and Parameter list)?
    I did some tests but have random memory exceptions as well as strange behaviour when trying to insert data (reading data seems to work fine).
    Thanks!

  14. @Michaela: I don’t know if we can write to .nsf file but I think if ODBC is supported, the standard Insert-Command will be also supported. I have never inserted data too so I can’t help that much. :).

  15. like @piter i get this error with sql server 2008 and mysql 9.0.

    “Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “xxxxxxx”.
    OLE DB provider “MSDASQL” for linked server “xxxxxx” returned message “[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”. (Microsoft SQL Server, Error: 7303)”

    i instaled lotus client 9.0 32 bit.
    Notesql driver 32 bit.
    and created a 32 bit sql node.

    so normaly everithing is 32 bit?? someone know why i can’t fix this error?.

  16. @zwan: In section 6. of post above, we have 2 options for creating connection by LotusNotes SQL. Try both of them, one of these options will work for you.

  17. I read those solution but i use driver with sql server 2008 (linked server) not c#.
    in fact i think i need use a 64 bit version of notes wich don’t exist.
    even setting a sql node 32 bit i got handle error 7303 . like it considere my sql install isn’t 32 bit.
    an other test importing data with excel 32 bit give the error “sqlallochandle on sql_handle_dbc”
    my odbc dns is in windows/syswow64/odbcad32.exe.

    i’m all 32 bit that crazy! this seem a waste of time unless being all 64bit (notes,os,driver,sqlserver?)

  18. @zwan: If you’re sure that you have
    Notes Server 32 bit (Domino Server AND Lotus Notes Client),
    LotusNotes SQL 32 bit Driver installed and
    you start ODBC with odbcad32.exe

    Then it’s really weird. I really don’t know why.

  19. Hmm i’m not sure domino administrator instaled a 32 bit server maybee its 64.
    client side i only have 32 lotus client.
    but if 32 bit client can’t conect a 64 bit server welll…
    i quit IBM and java stufff 😉

  20. @zwan : If you can access Domino Server with your 32 bit Lotus client then it should work with Lotus Notes SQL 32 bit too. I can’t understand either. Maybe try you with a fresh installation of test machine and check if it works.

  21. Hi
    When I try to add a new system DNS the following error message appears:
    “The setup routines for the Lotus Notes SQL Driver (*.nsf) ODBC driver could not be loaded due to system error code 21: The device is not ready. (C:\NotesSQL\nsqlc32.dll).
    I have tried all solutions that I found in internet, I have deleted and reinstalled NotesSQL, tried with another version, etc. but the same error message appears.
    ¿May you help me?

  22. @Carlos: I don’t have this error during installation. I already searched your error to see if it is popular. Maybe you’re the only one who has it :(. Try with a fresh same Windows OS + Lotus Notes client installation on test machine to see if you can reproduce the error.

    If the error still comes, then it is the problem of Lotus Note SQL Driver.

    If it won’t come, it’s the problem of your current computer.

  23. Dear admin,
    This works very fine but I have a little problem in my case.
    The data in my NotesDB is Unicode, when I select them using this method I got fews characters that cant be done: “Công ty nhi·t ·i·n ·ông Tri·u”
    I’m using version 9.0 on windows 8.1.

    Looking forward to hearing from you.
    Thank you!

  24. Thank you very much , This is very helpful article for Notes SQL.
    I solved my odbc driver problem using your path variables advise

  25. @tican : I don’t have a solution for you too. I tried with German and it works. For Vietnamese you should contact IBM NotesSQL support team, they can help you for sure. I think you have to configure something to read data out correctly.

  26. [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
    OLE DB error trace [OLE/DB Provider ‘MSDASQL’ IDBInitialize::Initialize returned 0x80004005: ].
    Msg 7399, Level 16, State 1, Line 1
    OLE DB provider ‘MSDASQL’ reported an error.

    I have this error when I tried to run my SQL query using SQL management studio

  27. @Marj : I’m not sure if we can connect to Lotus Notes ODBC Server with SQL Management Studio. Can you connect to Server through Microsoft ODBC Administrator as in section 6 in post above?

  28. Hi, I have this working up to a point – in SQL I can see forms for the notes database I have connected to but no views. I need to see the views to pull in the data! Any ideas? I have tried several databases including names.nsf!

  29. Your post is very useful.
    I have made the Lotus NoteSQL work at my computer:
    – Windows 7 Professional 32 bit
    – Lotus Notes 8.5.3 32 bit
    – Lotus NoteSQL 8.5.3 32 bit
    However, it doesn’t work at the server:
    – Windows Server 2008 R2 Standard 64 bit
    – Lotus Notes 8.5.3 32 bit
    – Lotus NoteSQL 8.5.3 32 bit
    And the error message is “ERROR [IM005] [Microsoft][ODBC Driver Manager] Driver’s SQLAllocHandle on SQL_HANDLE_DBC failed”.
    If you have any ideas please help me.

  30. @thc: Check if your ODBC Driver works with other data sources like normal Microsoft SQL Server or Access database. It seems that the ODBC driver doesn’t work.

  31. Dear Admin,
    Thanks for your post, its very useful .
    But i have some problem. In your example of .net code, you use “insert into” syntax, but how about update the value of the document’s field inside the ntf ? because i already tried to use “Update” syntax, but the document’s field become empty or blank.

    Looking forward to hearing from you.
    Thanks.

  32. Helpful article! There is no 64 bit Notes client though (except on the Mac but that’s not much use for the ODBC setup.

  33. Hi, i get the error message in section 3, no matter which version of the driver i use.
    I have windows 7 64 bit and Notes 9 social edition 32 bit, no matter which of the driver installers i use i get the error message
    “Setup cannot locate a supported version of the required Notes or Domino software”
    I tried changing Path value, as described on other websites, i tried pretty much everything and nothing works
    Any ideas?

Leave a Reply

Your email address will not be published. Required fields are marked *