Transact SQL stored executed from AS/400 CL program.
20 pts.
0
Q:
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.
ASKED: Jun 8 2009  2:39 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
0
370 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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 Answered: Jun 10 2009  5:37 PM GMT by SBatSI   370 pts.
Latest Contributors: Jcorzine   20 pts.
0
0
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



0