AnsweredAssumed Answered

Error executing sql stored procedure "Cannot use the OUTPUT option when passing a constant to a stored procedure"

Question asked by Nonnie Mann on Jan 5, 2018
Latest reply on Mar 29, 2018 by dovle01

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.  

Outcomes