LSX Lotus Connectors Setup on MS Windows Server 2003

Mindwatering Incorporated

Author: Tripp W Black

Created: 09/18/2009 at 11:28 AM

 

Category:
Domino Upgrades / Installations
Software (Re)Configuration

Need:
Setup IBM Lotus Domino Server to connect to a remote Microsoft SQL Server 2005.

Solution:
There are two ways to accomplish this.
a. You can use the SQLOLEDB software in MS Windows w/o setting up a database icon in your 32ODBC.
b. You can setup an ODBC database instance using the 32ODBC control panel.

Notes.
The data source is the server, not the database. Think enterprise, think remote server.
If you do the ODBC control panel option to create the database, it has a Test button which is useful a testing point.
Either way, you can use NDCTEST (DCTEST on Linux/UNIX) to test from the Lotus (LC) Connector to confirm connectivity to the database.

LCX Sample Code:

Dim lcx As New LCconnection ( "oledb" ) ' connection to the server
Dim sqlquery As String ' selection SQL query
Dim lcLst As LCFieldList ' the columns and rows of data
...
' in real life, I tend to get from a setup document
servernm = "server.mindwatering.local"
sqlquery = "select # from mydb.mytable"
dbuser = "someidname"
dbpwd = "lotusnotesrules"
...
' make connection
lcx.provider = "SQLOLEDB"
lcx.Auth_Integrated = "SSPI"
lcx.server = servernm
lcx.database = "MySQLDbName"
lcx.userid = dbuser
lcx.password = dbpwd
lcx.Connect
If Not (lcx.IsConnected) Then
Print "Error: Unable to make connection to server, " & servernm & "."
Else
Print "Connected to server: " & servernm & "."
End If
...
' execute query to database and get result set
...

ODBC Setup Information on MS 2003 Server
1. Open up the Data Sources ODBC control panel on the Lotus Domino server:
Start --> Control Panel --> Administrative Tools --> Data Sources (ODBC)

2. Under Drivers tab, verify SQL Server entry exists.

3. Under System DSN tab, click the Add button on the right.

4. In the list of drivers, select the SQL Server driver in the list. Click Finish.

5. In the Create a New Data Source to SQL Server wizard/dialog, enter the following:
5a. Name: Enter a short name. This will be the connection name for this data source now.
5b. Description: Enter something useful to you if desired.
5c. Server: Enter the server's DNS name.
5d. Click Next.

6. On the next screen of the same dialog, choose either the Windows NT authentication using network login ID or the SQL Server authentication using a login ID and password.
(I typically have the admins doing the latter where they have given me a user ID and password to use.)
If the SQL user login option was chosen, the checkbox with the Login ID and Password fields is no longer dimmed out. These should be completed with the user name and password.
Click Next.

7. At this point the dialog/wizard will attempt to connect using the information you provided.
If you connected successfully, you can now use the database name as a source for your Domino server. If you failed, read the error message, and troubleshoot why.
The most common error is" SQL Server does not exist or access denied." It means you messed up either the server's name in the Server field (step 5c) or the authentication options.


previous page