How to Execute a Stored Procedure from within a Test.

Hi,



You can find an example here.

Given your example I have the following code:

SProc = ADO.CreateADOStoredProc();

SProc.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CEA_NEIC;Data Source=<IP Address>";

SProc.ProcedureName = "CEA_NEIC.dbo.P_update_RPT_PI_Profile_Summary_Data_job";

SProc.ExecProc();

Log.Message("Result: " + SProc.Parameters.Items(1).Value);



and the test fails with a "JScript Runtime Error" and the following:



Query timeout expired

Unit: "Create_New_PI" Line: 25 Column: 3.



where line: 25 Column: 3 is the "SProc.ExecProc()'" line above.



Any help here?

Do I have to increase the timeout value for queries or something?

The actual Stored Procedure runs about 2min

Hi,



Did you declare your procedure's parameters as it is shown in the example?

My Procedure does not have any parameters.  Do I still have to define them?

Hi,



Your procedure has at least one out-parameter which you use here:

Log.Message("Result: " + SProc.Parameters.Items(1).Value);

You need to declare it.

I noticed that after the comment above.  When I tried to add a parameter for 'output' the system said the procedure did not have any parameters.  I have subsequently changed the Log line.  Also note that the error is failing on the line before the Log line- the SProc.ExecProc;



However, my issue still remains.  For example if the Log line is commented out, I still receive the same error.

Adding Parameters does not fix my issue.



Other thoughts?

Hi Mark,



I recommend that you try specifying a timeout for your procedure (SProc.CommandTimeout = <value>). Where <value> is a timeout in seconds.



Does this help?

Thank you.  That seemed to work.

I first tried to put in a value of 2min (as the procedure runs under 2 min) but that did not work. So I increased the time to 3min, and it returned successfully.



Again, thank you.

We get this question a lot during TestComplete trainings and we now have a video on it available here.





-Steve

Register Now! TestComplete Keyword Online Training April 12-14th

Thanks Steve, that was helpful.  I did notice that the code was slightly different than what was given in an earlier post.

(Steve if you could give reasons for using ADOCommand vs ADOCreateStoredProcedure objects?)



One of the things that was suggested was to put in a 'wait' for the process to complete.

My question is will the test continue once the procedure is finished or wait the full 'wait time' before continuing.



EX:

If the procedure takes 2 min run, and I have set the wait time to 5 min,

    a) Will the test continue after 2 min, when the procedure completes?

    b) Will the test wait the ful 5 min before continuing?



I would like the test to continue once the procedure completes.  There is no return values from the procedure to validate against.