I have a Lotusscript agent that builds and executes a SQL query. This agent invokes a library script that runs successfully when run interactively from a button action on a form. I needed to make it run as a background agent due to it taking (and locking the client for) a long time. The agent doesn’t run the script to a successful completion. Through print statements to the notes log, I believe that I’ve narrowed the problem down to the SQL statement that is created when the agent runs the script. It appears that the SQL statement is being truncated at 473 characters, making it incomplete and causing an error in the module that calls the SQL-building script.
I’ve searched the designed help, Agent FAQ, and this forum but can’t find any reference to an agent-caused limitation in the allowable length of a string variable or a limitation on the length of a print statement generated by an agent.
Anybody have any ideas along this (or any other) path?
Yes, I’ve hit limits with ODBC accessing a back-end MS SQL Server 2000. The solution that worked for me was to rewrite these agents in Java and use JDBC.
I don’t believe that its a problem with ODBC/SQL because the query works when run interactively. It seems to be an issue with the agent building/running the query.
I would be surprised if there were no limit on the length of the print statement output. If you suspect this may be the problem, why not change the statement to print the beginning and end of the string, and its length, rather than the whole string?Of course, this would not cause your agent to not work, but it might lead to a misdiagnosis of the problem. Did you not use On Error to trap the error and display the line number?
Yes, I did have error traps in all scripts EXCEPT the one that generated the error. I was getting an error report from one of the scripts calling out the line that called the offending script. Now, with an error trap in the script that builds the SQL query (along with your suggestion of printing the length of my SQL string - it’s 764 characters - thank-you-very-much) the error is “Error 91 : Object variable not set at line # 323”. Line 323 is “Set query.Connection = con”, apparently related to my doing something in an agent that’s not allowed (the code works when run interactively from a button - how I designed it to work before I realized that it sometimes takes more than 30 minutes to complete the query and process the data that it returns - WAY too long for someone to wait while their Notes client is locked up).
I found, empirically, that what I’m doing to make a connection to the back-end database on our AS400 doesn’t work in “agent land”, so I was running that code before I call the agent. Now I’m thinking that I’ll have to change to a scheduled agent since I’m finding that the ‘runonserver’ method doesn’t free up the client, as I had hoped…
Can you shed any light (tips, tricks, hints) on using ODBC and SQL in a scheduled agent?
From what I’ve gleaned from your posts, it sounds like you set up an ODBC data source on your desktop machine to connect to a DB2 table on the AS/400 and your agent ran fine locally. But then you ran the same agent on the Domino server and it blew up because you couldn’t establish a connection (based on the object variable not set error).
I believe the problem has to do with the fact that you may not have set up the same ODBC data source on the server. Try setting up the same name ODBC data source on your server (you might need a Notes Admin or networking person to do that for you) and see if that resolves the object variable not set error.
Actually, this started (and worked) as Lotusscript subroutine(s) that were called from a button on a form. When I saw how long the process could take (while locking up the Notes client), I decided to create an agent to run the subroutines. Unfortunately, its never worked when run from the agent (either on the workstation or the server). I’ve been able to resolve other errors primarily by removing the use of UI classes and moving the code that creates the connection to the AS400 to run before the agent is called.
It still may be an ODBC configuration issue, but I figured I should clarify…
If anything else comes to mind, please let me know.
Object variable not set means that something you’re using as an object variable – i.e. with a “.” after it – has the value Nothing. That would be the variable query. So you need to look earlier in your script, where you’re assigning query (or more precisely where you’re not assigning it), and see why that’s not working.
It looks like you’re using the ODBC classes – are you sure there’s an ODBC driver on the server? You might consider using the LC LSX instead. If there’s a “connector” for the specific type of database you’re using, it would probably be faster than ODBC.
Also, I don’t know how much your task requires transferring data between the rdb and Notes – you might see whether there’s a way you can write a stored procedure or other script in the database, which would do the bulk of the work.
Maybe I’m missing something - ‘Set query.Connection = con’ should put the value of ‘con’ into the ‘Connection’ property of the odbcquery object ‘query’ . No? ‘con’ is set to “New ODBCConnection”, so I don’t see where ‘Object variable not set’ pertains unless the “New ODBCConnection” is somehow invalid when used in an agent, but I haven’t found anything in the help or forums that explain it’s so. Again, this code does run (and return query results) when used as an interactive action from a button on a form.
Thanks for taking the time to respond. If you think of anything that may, at the least, explain this “behavior”, please let me know.
You are saying in another post that this has not worked when it’s in an agent run from the workstation even though a button works from the same workstation. This means the problem is not in the ODBC configuration (or at least that could not be the only problem) but rather in the code of the agent itself.
The code which you have not shown us.
As I said, “Object variable not set” refers to the variable query in this case. That is the variable that is not set.
If something doesn’t work in an agent run from the workstation, you can step through it in the LotusScript debugger. Have you not done this? See how query gets assigned.
This has dragged on because you didn’t do any of your own debugging and because you didn’t provide us complete information from the start. See CRISPY link below.
In fact, I DID do my own debugging, as I indicated in my original post. I used error traps and print statements to narrow the problem down. (BTW, the Lotusscript debugger does NOT work in agents.) I searched the forums for posts regarding agents and ODBC. I did all this BEFORE I posted.
I’ve solved the problem by rewriting the agent to include all of the code in the script library that does the work instead of just calling the scripts with the agent.
While I don’t appreciate your chastisement, your guidance has proved useful to me. For that, I thank you.