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
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
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
