ODBC SQL Login issues

Ola!!

I have a database that is connecting to an SQL server/database using LotusScript. The datasource is a DSN type connection with username & password authenticating using login ID and password entered by user. User/password is also specified in the code.

For the most part, when the scheduled agent runs, it processes the data ok, then every once in a while, maybe once a week or every other week, the agent will hang. When I log into the windows machine, I notice an MS SQL server login error saying the server does not exist or access denied. If I click ok on that, another login box pops up with the ODBC datasource user name and password already in the fields. I click ok, and then the agent starts to process as normal.

When this occures, I also find the following TCIP memory errors in the log…

05/03/2005 07:45:47 AM Error connecting to server NCIAPP01/NCI: The TCP/IP protocol stack reported that it ran out of memory. Consult your network documentation to increase configured memory, or reduce Notes connections by limiting clients (see SERVER_MAXSESSIONS parameter in …

The SQL admin says that everything looks good on his server. Why would the agent run fine, and then one day stop because it wants a manual log in??

Subject: ODBC SQL Login issues

Xie,

You didn’t specify which type of DSN you are using, but it has to be a System DSN, not a User DSN.

Ken

Subject: RE: ODBC SQL Login issues

It is a system DSN. As I said, it does work most of the time. It’s strange with the TCPIP memory errors, I wonder if that is causing the login issue, or if the login issue is causing the TCPIP memory errors - don’t know.

Subject: RE: ODBC SQL Login issues

Xie,

Two other things:

o If you are using connection pooling and you are updating a table in SQL Server (inserting or deleting), you must commit the changes or you will hang the connection.

In LotusScript: Call con.Action(LCACTION_COMMIT)

In Java: con.commit();

…where “con” is the variable representing the connection object.

o Your agent might simply be timing out. Right click on the agent in Designer and choose Log to view the agent log. If you see a message indicating time limit exceeded, you need to increase the defaults for the daytime and/or nighttime agent time limits specified in the Domino server doc. The default time is 10 or 15 minutes which may not be long enough. In our case it wasn’t. We increased it to 3 hours.

Ken