ODBC SQL Connection - Moving App to new Server

HCL Domino Domino Forum
1

I posted this in the 8.5 forum but there’s been no answer. Sorry for repost but I hope someone can help.

I have a notes application on a Windows 2003 server (32bit), called DOMINOOLD, which contains an agent that runs on a nightly basis. The agent connects to another server, SQLSERV. I don’t know much about what was initially involved in setting up the sql connection but the agent script contains the following…

Uselsx “*LSXLC”
Function importdata(personID As String, importStartDate As NotesDateTime, systemStartDate As NotesDateTime)

Dim DbSession As LCSession
Dim DbConnection As LCConnection
Dim fields As New LCFieldList
Dim field As LCField
Dim sform As String
Dim sqlDBName As String

sForm=“formname”
sqlDBName=“dbname”
Set DbConnection = New LCConnection(“OLEDB”)
dbconnection.provider=“sqloledb”
Set DbSession = New LCSession

DbConnection.Server=“SERVER\INSTANCE”
DbConnection.UserID = “jdoe”
DbConnection.Password = “password”
DbConnection.Database=sqlDBName
DbConnection.metadata=sform
DbConnection.Connect

I now want to move the application to another server, DOMINONEW, which will be Windows 2008 (64 bit), with 64 bit domino installed.

What sort of things do I need to consider in order to maintain the server’s ability to connect to SQLSERV. Does the connection rely on anything at the OS level?

Thanks

2

Subject: Data Sources (ODBC)

Hi,

Thanks for your advice, especially the warning about the 32 bt / 64 bit oddities. I thought I was in the right area, and your answer confirmed that for me. The thing is, I already checked the Data Sources in System DSN on the old server; there are 2 listed but none of them relate to this sql database. I don’t understand how the agent is managing to succeed if there is no sign of the Data Source on the agent server.

Any ideas?

Thanks :slight_smile:

3

Subject: Yes! Use the System DSN’s in the ODBC Data Source Admin

Yes! It uses the DSN’s in the Administrative tools folder (that’s “Data Sources (ODBC)”). I suggest you look at the old server’s Data Sources, and get ready to create a new Data Source on the new server.

Don’t forget that on 64bit OS’s that “Data Sources (ODBC)” bit is 64bit, but Domino uses the 32bit ODBC so you have to run odbcad32.exe in the \Windows\SysWOW64 directory if it’s 64bit Windows. Happy happy joy joy.
The 32bit and 64bit ODBC Data Source Administrator are indistinguishable, so be careful. One way of telling them apart, is that there won’t be any System DSN’s in the 64bit one, when you expect there to be some. When that happens, think to yourself “am I running the 32bit version?”.
Assuming you have the correct ODBC Data Source Administrator running, we want the system DSN’s… Click on the System DSN tab. If you see stuff about Excel, and Access, you’re in the wrong place.

To Create a new System DSN, press the “Add…” button. Or, push the “Configure…” button.

You will be presented with a (long) list of possible drivers. Scroll all the way to the bottom, and choose “SQL Server”, then press “Finish”.

Now the next few windows relate to setting up the DSN connection to the SQL server… First up is the Name of the System DSN. I think, from your code, yours will be called “OLEDB” or “sqloledb”. Then press the “Next >” button.

Now we need to log into the SQL server. We need to change the authentication from Windows NT to SQL Server authentication, Then we enter the username and the password. Press the "Next >’ button.

Now we specify the SQL database we’re connecting to. Press the "Next >’ button.

Finally, press the “Finish” button. (We don’t change anything on this screen.)

Although, you can test what you’ve written in the next screen by pressing the “Test Data Source…”, otherwise press OK.

Regards

Tim