Devart Forums

Hello!

Why does TMSQuery able to run this SP and TMSStoredProc does not ?
Is TMSStoredProc disabled to run extended stored procs or is it a bug ?

I tried with MSSQL 2000, MSSQL 2005 and the result is the same.

Thanks

//-----------------------------------------------------------
// xp_fileexist calling with TMSStoredProc is NOT working.
// Errormessage:
// "Usage: EXECUTE xp_fileexist [, OUTPUT]"
//-----------------------------------------------------------

MSStoredProc1.ParamCheck := False;
MSStoredProc1.StoredProcName := 'master..xp_fileexist';

MSStoredProc1.Params.CreateParam(ftString,'filename',ptInput);
MSStoredProc1.ParamByName('filename').AsString := 'c:\autoexec.bat';

MSStoredProc1.Params.CreateParam(ftInteger,'file_exists',ptOutput);
MSStoredProc1.ParamByName('file_exists').AsInteger := 0;

MSStoredProc1.ExecProc;

//-----------------------------------------------------------
// xp_fileexist calling with TMSQuery is working.
//-----------------------------------------------------------

MSQuery1.ParamCheck := True;
MSQuery1.SQL.Text := 'EXEC master..xp_fileexist :filename, :file_exists OUTPUT';

MSQuery1.ParamByName('filename').ParamType := ptInput;
MSQuery1.ParamByName('filename').AsString := 'c:\autoexec.bat';
MSQuery1.ParamByName('file_exists').ParamType := ptOutput;
MSQuery1.ParamByName('file_exists').AsInteger := 0;

MSQuery1.Execute;
ShowMessage('MSQuery: '+MSQuery1.ParamByName('file_exists').AsString);

The problem is that xp_fileexist is System Extended Stored Procedure and for its execution SQL Server requires usage of EXEC statememt, but the TMSStoredProc component uses CALL statememt for stored procedure execution. Therefore you should use TMSQuery for executing this procedure.