Transact SQL stored executed from AS/400 CL program.

20 pts.
Tags:
554 transaction failure
AS/400
CL Program
Microsoft SQL Server 2000
SQL Database
T-SQL
How can I execute a Transact SQL stored procedure (actually in the database) on a SQL 2000 database from an AS/400 CL program.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following