How to Execute a Stored Procedure from within a Test.

[markbain]

I am wondering how to execute a stored procedure from within a test script.



Is it as simple as puting a line in the test script like this:



    DDT.ADODriver.(<connection string>, <sql syntax to execute the stored procedure>);

[YMinaev]

Hi,



You can find an example here.

[markbain]

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

[YMinaev]

Hi,



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

[markbain]

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

[YMinaev]

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.

[markbain]

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?

[YMinaev]

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?

[markbain]

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.

[stref]

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