asked on

VBScript calling SQL Server stored procedure with OUTPUT parameter

Hi - I have a VBS file that I use to execute a SQL Server stored procedure, but I want to enhance it by retrieving a "Status" indicator when the stored proc ends. I don't think I can use a RETURN value in the stored proc so I added an OUTPUT parameter.

The stored proc has two input parameters and one output parameter. When it had just the two input parameters I called in from VBS like this:

NOTE: cn is an ADODB connection object.

cn.Execute("MyProc 'N', 1)

That works fine. With the new output parameter (@RETURN_STATUS INTEGER OUTPUT), I am trying to call the stored proc like this:

Dim RetVal
RetVal = 99
cn.Execute("MyProc 'N', 1, @RETURN_STATUS = " & RetVal & " OUTPUT")

I get the following error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the OUTPUT option when passing a constant to a stored procedure.

NOTE: I tested the stored proc in SQL Server like this:

declare @rtn integer
exec MyProc 'N',1,@RETURN_STATUS = @rtn OUTPUT
print 'Return value = ' + cast(@rtn as varchar)

Can anyone tell me how I can call the stored proc using the connection object and get the value of the output parameter?
Do I have to use a command object?

Thanks!

ASKER CERTIFIED SOLUTION

azserrata

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wlevy

ASKER

Thank you azserrata that is exactly what I needed.

azserrata

Great!

pradeep D

Hello,

I am also facing the same issue as mentioned by Wlevy and the below suggested link was not working.

http://authors.aspalliance.com/stevesmith/articles/sprocs.asp

Can you please provide solution for this.

Stored Procedure:

create or replace procedure sproc_oraint02_send_email_test
(
ip_event IN EMAIL_DETAIL.EMAIL_EVENT%TYPE,
ip_sub_event IN EMAIL_dETAIL.EMAIL_SUB_EVENT%TYPE,
---- ip_file_dir IN VARCHAR2,
---- ip_file_name IN VARCHAR2,
op_error OUT VARCHAR2
)

IS
lv_attach varchar2(4000);
PROCEDURE_NAME VARCHAR2(40) := 'sproc_oraint02_send_email_test';
lv_string VARCHAR2(32000);
       email_detail_buf email_detail%ROWTYPE;
       lv_num_files PLS_INTEGER; -- IT_TASK_XXXXX

lv_file_name VARCHAR2(200); -- IT_TASK_32526

user_exception EXCEPTION;
BEGIN

BEGIN

SELECT *
into email_detail_buf
from email_detail
where email_event = ip_event
and email_Sub_Event = ip_sub_event;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE user_exception;
END;

lv_string := '(/bin/printf "%s\n" "'||email_detail_buf.body_line_1||'" " " "'||email_detail_buf.body_line_2||'" "'||email_detail_buf.body_line_3;
lv_string := lv_string||'" "'||email_detail_buf.body_line_4||'" "'||email_detail_buf.body_line_5||'" "'||email_detail_buf.body_line_6||'" " " "';
lv_string := lv_string||email_detail_buf.footer_1||'" "'||email_detail_buf.footer_2||'" "'||email_detail_buf.footer_3||'";)';
dbms_output.put_line(lv_string);
/* IT_TASK_32526 Start */
---IF email_detail_buf.ATTACH_YN ='Y' THEN
-- lv_attach := ' -a "'||ip_file_dir||'/'||ip_file_name||'"';--IT_TASK_34647
      /*IT_TASK_34791 Start */
       -- lv_num_files := orautils.num_entries(ip_file_name);
       --FOR i in 1 .. lv_num_files LOOP
--- lv_file_name := orautils.ENTRY(i,ip_file_name);
             --lv_attach := lv_attach||' -a "'||ip_file_dir||'/'||lv_file_name||'"';--IT_TASK_34647
-- END LOOP;
      /*IT_TASK_34791 End */
-- END IF;

/* IT_TASK_32526 End */
lv_string := lv_string||' | /bin/mailx -s "'||email_detail_buf.subject||'" "'||email_detail_buf.email_to||'"';

dbms_output.put_line(lv_string);


--sample command
--(/usr/bin/printf "%s\n" "Hi ," " " "Please find the attached output file of Silent Failure Logging." "" "" "" "" " " "" "Regards , " "CGI";) | /bin/mailx -s " Paycash Audit Log" -a "/data_uk/ukphxdv1/progress/log/test_ftp_goldmine.log" "vipin.nambiar@cgi.com,vipinnambiar7776@gmail.com"

ORAINT02.SPROC_ORAINT02_HOST_COMMAND(
lv_string
);

EXCEPTION
WHEN USER_EXCEPTION THEN
op_error := 'Configuration Missing in EMAIL_DETAIL , Please Check ';
WHEN OTHERS THEN
op_error := 'send mail failure ';
dbms_output.put_line('send mail failure');
            ---op_error := Oraerror.fatal_error(
-- PACKAGE_NAME,
--PROCEDURE_NAME,
--'0',
-- SQLCODE
--- );


END sproc_oraint02_send_email_test;

Executed below code in Oracle SQL Developer it worked fine.

--- Calling the procedure---
declare
ip_event varchar2(20) :='44720' ;
ip_sub_event varchar2(20) :='1';
op_error varchar2(20);

begin

sproc_oraint02_send_email_test(ip_event,ip_sub_event,op_error);

end;

Vb Script Code I am trying:

conStr="Driver={Microsoft ODBC for Oracle}; " &"CONNECTSTRING=(DESCRIPTION=" &"(ADDRESS=(PROTOCOL=TCP)" & "(HOST="& HOST &")(PORT="& PORT &"))" & "(CONNECT_DATA=(SERVICE_NAME="& SERVICENAME &"))); uid="& USER &";pwd="& PASSWORD &";"
Set cn = CreateObject("ADODB.Connection")
cn.Open conStr
Set cmdObj=createobject("adodb.command")
With cmdObj
.activeconnection=cn
.commandtype=1
.commandtext="sproc_oraint02_send_email_test"
End with
param1=44721
param2=1
cn.Execute("CALL sproc_oraint02_send_email_test('" & param1 & "','" & param2 & "',:op_error)")

I am getitng below error

"ORA-01008: not all variables bound"

Please check the attached file for the issue.
EmailSPIssue.JPG