Test Data Manager

Using Datamaker, creating data for a Data Pool with datapainter tool for valuing specific cell. Getting an error executing a stored procedure. The stored procedure will read in 2 parameters and output 2 parameters. Upon completion of the SP, the first output parameter will be value that I want to be assigned to that cell.

Within the data painter UI, the following text is entered as the function. NextID is the output parameter I want valued and assigned at publish time for that cell.

@execsqlproc(PSOARIAN SC_1832_TDM SHARED,Arc_sp_FetchNextID,HfcId,IN,622,Radix,IN,1,NextID,OUT,0,MaxID,OUT,0,NextID)@

When I click the validate check box I get the following SQL error returned in the popup dialog.

"[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot use the OUTPUT option when passing a constant to a stored procedure." What I think this means, is that either it thinks 'NextID' is a constant or that '0' is a constant.

Tried the following with no success:

* I've tried replacing the '0' with a variable with a default value of 0. so command looks like this.
@execsqlproc(PSOARIAN SC_1832_TDM SHARED,Arc_sp_FetchNextID,HfcId,IN,622,Radix,IN,1,NextID,OUT,~SQLIntegerDefault~,MaxID,OUT,~SQLIntegerDefault~,NextID)@

* tried not passing a value for that space and left only 'NextID,OUT,,MaxID,OUT,,'

* tried passing OUTPUT instead of OUT

Documentation example reads which is very similar to my syntax:
@execsqlproc(PTravelX,dbo.extractCounter,paramin1,in,’Test’,paramin2,in,100,paramout1,output,0,paramout2,output,0,paramout2)@

Questions:
1. what is behind the scenes executing "execsqlproc". Is this command specific to the TDM tool (internal tool development), or is this some native form of SQL, or specific to a SQL server driver. Where could I find detailed documentation on the syntax (the DocOps help page has very few details).
2. How would you make the parameter name a variable. is it complaining about the parameter name or the value - which one does it this is a constant
3. When I execute the same command through the TDM portal UI _ with datapainter and using the check box to validate the call works properly and returns the correct result. I'm reading the SQL is interprated different from the Datamaker UI vs. the portal. Why would that be the case?

4.  The stored procedure I'm calling is used in production and not modifiable by myself without impact to others.  

Hi Mann, 

I haven't had a lot of time to look into this yet, but here are some of my findings for this error message:

• Normally, Output parameters can be used in both directions (Input and Output).
• So, when we access the stored procedure, Output keyword is required only to return the data through the Output parameters. If we don't want to return any data through the Output parameters then, The Output keyword should not be used.
• If we want to return any data through the Output parameter, the Output keyword should be used along with parameter.
• If we don't want to return any data through the Output parameter, the Output parameter should not be used when passing constant value instead of variable.
• Output keyword should not be used when we pass the constant value to the stored procedure directly instead of a variable.
• Output keyword should be used when the value should be returned from the stored procedure through the variable.

1. What is behind the scenes executing "execsqlproc"? Is this command specific to the TDM tool (internal tool development), or is this some native form of SQL, or specific to a SQL server driver? Where could I find detailed documentation on the syntax (the DocOps help page has very few details)?

• execsqlproc is just a command for execution the sql procedure. 
• You can read more about this here: Execute a Stored Procedure | Microsoft Docs 
• Our documentation states the same thing saying execsqlproc is to 'execute a stored procedure against a specified connection with a list of parameters, and return the result'.
• Data Generation Functions and Parameters - CA Test Data Manager - 4.4 - CA Technologies Documentation 

2. How would you make the parameter name a variable? Is it complaining about the parameter name or the value - which one does it this is a constant?

• You can read about creating variables here: Create and Manage Variables - CA Test Data Manager - 4.4 - CA Technologies Documentation 
• We can look over the specific error more in a support case.

3. When I execute the same command through the TDM portal UI _ with datapainter and using the checkbox to validate the call works properly and returns the correct result. I'm reading the SQL is interpreted different from the Datamaker UI vs. the portal. Why would that be the case?

• Sometimes there are discrepancies between how Datamaker and the Portal handle functions. 
• We would have to investigate this further over a support case to see if this is a config issue or a defect. 

4.  The stored procedure I'm calling is used in production and not modifiable by myself without impact to others. 

• This would be good to add in the business impact section of your support case.  

For cases like this and to investigate this further, I recommend opening a support case. You can do this by going to Contact CA Support - CA Technologies. 

The syntax that finally worked was the following:  

@execsqlproc(PSOARIAN SC_1832_TDM SHARED,dbo.Arc_sp_FetchNextID,HfcId,IN,622,Radix,IN,1,NextID,OUT,,MaxID,OUT,,NextID)@

Where only a comma is left as the output variable value.  I removed the 0 (zero) which seems to have been the constant it was complaining about.  I have this working in both Datamaker and Portal.  

Regarding the question on the execsqlproc syntax.  I understand that this is the SQL way to execute a stored procedure.  But the fact that TDM needs the @ sign followed by 'execsqlproc' followed by left paren and Target profile name then comma separated SP name and input/output variable.  

This is the syntax I assume is proprietary to CA TDM tooling.  And the fact that not providing the output variable value (using the ',,' syntax instead of ',0,' ) was the solution, is there any other CA documentation that explains why ',,' works the way it does. 

Thank you

I have found that there might be something that was overlooked.  Dahman wanted me to add that "dbo." was added to the table.  This is required. 

So we started out with this in this posting:

@execsqlproc(PSOARIAN SC_1832_TDM SHARED,Arc_sp_FetchNextID,HfcId,IN,622,Radix,IN,1,NextID,OUT,0,MaxID,OUT,0,NextID)@

The SQL example form was:

@execsqlproc(PTravelX,dbo.extractCounter,paramin1,in,’Test’,paramin2,in,100,paramout1,output,0,paramout2,output,0,paramout2)@

This was correctly changed to include, among other items, the "dbo." and this is the resulting working form:

@execsqlproc(PSOARIAN SC_1832_TDM SHARED,dbo.Arc_sp_FetchNextID,HfcId,IN,622,Radix,IN,1,NextID,OUT,,MaxID,OUT,,NextID)@

In addition to this whole posting, here is another start and ended up to be example:

Started here:

@execsqlproc(PTDM_Internal,GetTitleRow,Silo,IN,'P',out_seq,OUT,,out_seq)@ 

Mid point transition:

@execsqlproc(Pordersdsmless,dbo.GetTitleRow,Silo,IN,'5',out_seq,OUT,,out_seq)@ 

And ended with:

Please use double quotes 
@execsqlproc(Pordersdsmless,dbo.GetTitleRow,Silo,IN,"5",out_seq,OUT,,out_seq)@

Please feel free to post additional examples if you find something working for you!

Cheers!
Les