Devart Forums

Posted: **Tue 09 May 2006 07:15**

I'm getting this error

syntax error, permission violation, or other nonspecific error

on execution of TMSStoredProc with:
1. newest SDAC & Delphi 2006
2. SQL Server 2005 SP1
3. option "concat_null_yields_null" set to "off"

example:

Code: Select all

procedure TForm2.Button2Click(Sender: TObject);
var
  sp: TMSStoredProc;
begin
  msConnection1.ExecSQL('set concat_null_yields_null off', []);

  sp := TMSStoredProc.Create(nil);
  try
    sp.Connection := MSconnection1;
    sp.StoredProcName := 'sp_tables';
    sp.ExecProc; // here comes the error
  finally
    sp.Free;
  end;
end;

if I set concat_null_yields_null to "on" or on SQL 2005 without SP1 (or with SQL 2000), no error occurs... Could someone help me, please?

Posted: **Tue 09 May 2006 14:24**

Found it myself, it's a bug in sql2005 SP1. the procedure sp_procedure_params_rowset returns each record 2x under these conditions, what results in this error... If somebody looks for a solution - the simplest one is to write
sp.StoredProcName := 'dbo.someprocedure'
instead of
sp.StoredProcName := 'someprocedure'
which causes internal delivery of the 'dbo' to the procedure sp_procedure_params_rowset as 3rd parameter and then this procedure fails no more...