20 pts.
 Transact SQL stored executed from AS/400 CL program.
How can I execute a Transact SQL stored procedure (actually in the database) on a SQL 2000 database from an AS/400 CL program.

Software/Hardware used:
ASKED: June 8, 2009  2:39 PM
UPDATED: June 10, 2009  5:38 PM

Answer Wiki:
I figured out one way to do it so I thought I would pass it on for everyone else. To execute either a DTS Package or a Stored Procedure, you have to set up a batch file on the server to call and it only has to have one record in it that is explained below. The AS/400 command stays the same and is as follows: RUNRMTCMD CMD('c:TEST_BATCH) RMTLOCNAME('111.111.1.111' *IP) RMTUSER(*NONE) The c:TEST_BATCH is the batch file you need to set up. Change the 111.111.1.111 to the IP address of the server to run it on. To run a DTS package, the batch file will need to look like the following: dtsrun /S "TESTSERVER" /U "TESTUSER" /P "TESTPWORD" /N "TEST_DTS" Change TESTSERVER to the name of your server. Change TERSUSER to a valid user id. Change TESTPWORD to the password for the user. Change TEST_DTS to the name of the DTS Package. Pay attention to the case on the /S, /U, /P, and /N. Make sure you save this as a .bat file not a .txt file. To run a Stored Procedure, the batch file will need to look like the following: osql -S "TESTSERVER" -d "TESTDB" -U "TESTUSER" -P "TESTPWORD" -q "exec dbo.TEST_STPROC" Change TESTSERVER to the name of your server. Change TESTUSER to a valid user id. Change TESTPWORD to the password for the user. Change dbo.TEST_STPROC to the name of the Stored Procedure. Pay attention to the case on the –S, -d, -U, -P, and –q. Make sure you save this as a .bat file not a .txt file. +++++++++++++++++++ Thanks for sharing your work with us. I have added your post to my bag of tricks because it may be useful in the future. Regards, Steve B
Last Wiki Answer Submitted:  June 10, 2009  5:37 pm  by  Jcorzine   20 pts.
All Answer Wiki Contributors:  Jcorzine   20 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _