Hello. I’m posting this message in attempt to help a co-worker accomplish a task for a common goal we share. I have provided a stored procedure on a MS SQL Server 2000 box which returns data to a LotusScript Agent for a generated report. He is able to run the report via ODBCConnection Objects from his local Windows Machine. However he cannot get the Agent to run scheduled from our Domino Server, which is AS400.
I’ve done some browing on the web to determine the best method for quering a SQL Server from the AS400, but most of the searches return how to run a stored procedure over an as400 rather than vice versa.
I’ve seen evidence that one can use ADODB objects in the lotus script but there was a mention that you must install ADO on the server. I have not found an installable version of ADO for the AS400, nor would I readily think that there would be such a driver. I’m well versed in ADO with VBScript and have used the Domino COM objects in the past, but always from a windows environment.
Bottom Line, Can I use lines such as:
Set objConneciton = CreateObject(“ADODB.Connection”)
from a piece of LotusScript running as an agent on the AS400? I do not have the time to research it myself but want to confirm it’s possible before sending my co-developer off on a wild goose chase. If it can be done are the ADO objects readily aviallable or do I have to install something on the AS400, and is it free?
If not, is it possible to use ODBCConnection to get results from a SQL Server Stored Procedure?
Thanks!
Subject: Domino Server on iSeries and LotusScript Agent with ADO
It is possible to get the results from an SQL stored procedure using ODBC. I have not done it on MS SQL, though I am sure it is similar to SQL400. Just use the execute method of the ODBCResultSet. “flag = resultSet.ExecProcedure(“Library.ProcedureName”,InputParameters, OutPutParameters)”
Subject: Domino Server on iSeries and LotusScript Agent with ADO
I’m not sure about using ADO from a Notes server based on an AS400 but I believe it will have the same problems your finding about trying to use ODBC from an AS400.
Another avenue to possibly explore is using JDBC and Java. I was in a similar situation to yours: how to get a scheduled LotusScript agent to run on a Notes server based on an AS/400 to interface with a MS SQL 2000 server? I coded the LS agent with no problem and can pull all the data I need from SQL server into my Notes app but only if I ran it interactively from my Windows desktop machine using ODBC as the connector.
From reading posts in this forum, I found that a possible solution to my problem was to use JDBC/Java. Not having coded Java outside of a classroom and knowing nothing about JDBC, I did my due diligence, reading up on available resources to see how this was done.
My company managed to procure a JDBC driver for SQL Server (cost about $200) which I incorporated into my Notes database (the quick & dirty way as opposed to setting it up properly on the server, but it works!). Then converting my LS agent into Java, which was not as difficult as I thought it would be. Now my Java agent runs scheduled on an AS/400, interfacing with MS SQL Server with no problem.
Hope that helps.