Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: FDStoredProc issue with StoredProcName



Permlink Replies: 0 Threads: [ Previous | Next ]
Dmitry Vavilov

Posts: 29
Registered: 9/23/17
FDStoredProc issue with StoredProcName
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 24, 2017 10:13 PM
Hi!
In the FDStoredProc component, in some cases, the parameter list is not loaded when specifying the function name (MS SQL) in the StoredProcName property. In order to load parameters in some cases a name is enough, in others it is necessary to specify a name in the format [<Name of the stored procedure>; 0]. As I recall, it was possible to specify only the name of the stored procedure.

If you specify the name [MyStoredProc;0], the parameters are loaded. But when trying to execute the procedure, an error occurs "[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'TESTDB.dbo.MyStoredProc;0'.";

As a result, you can not prepare parameters in RunTime and execute a stored procedure. The following code does not work
  FDStoredProc1.Params.Clear;
  FDStoredProc1.Prepared := False;
  FDStoredProc1.StoredProcName := 'MyStoredProc';
  FDStoredProc1.Prepare; //<--parameters are not loaded
  FillParamsValues(FDStoredProc1);
  FDStoredProc1.Execute;


if you specify the name of the stored procedure in square brackets:
  FDStoredProc1.Params.Clear;
  FDStoredProc1.Prepared := False;
  FDStoredProc1.StoredProcName := '[MyStoredProc;0]';
  FDStoredProc1.Prepare; //<--parameters successfully loaded
  FillParamsValues(FDStoredProc1);
  FDStoredProc1.Execute; //<--error: [FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'TESTDB.dbo.MyStoredProc;0'.


if you specify the name of the stored procedure in square brackets and after preparing the parameters, remove the square brackets from the name of the stored procedure:
  FDStoredProc1.Params.Clear;
  FDStoredProc1.Prepared := False;
  FDStoredProc1.StoredProcName := '[MyStoredProc;0]';
  FDStoredProc1.Prepare; //<--parameters successfully loaded  
  FDStoredProc1.StoredProcName := 'MyStoredProc'; //<-- parameters cleared
  FillParamsValues(FDStoredProc1);
  FDStoredProc1.Execute;


Edited by: Dmitry Vavilov on Sep 25, 2017 1:51 AM

Edited by: Dmitry Vavilov on Sep 25, 2017 1:54 AM
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02