Board index » delphi » SQL Server and GENERAL SQL ERROR

SQL Server and GENERAL SQL ERROR

So,
I have a tstoredprocedure component hooked to my sql server stored
procedure.
So,
80 times out of 100 it works fine
So,
20 times out of 100 the exec raises a General Sql Error with no further
information
So,
Execute it again - without doing anything and it works fine
So,
what now? I remember somebody having a similar thing with Sql Server
and have searched the old news to no results

Using delphi 2.0 developer , using odbc driver 2.65.0236
Using BDE with fat32 patch
Using Sql Server 6.0 - patch level 2
Code for CodeHeads

Function TMyContId.Change: boolean;
Begin
    Result := true;
    If fcontidId <> '' then
         With fUpdateProc do
            Begin
               If not Prepared then Prepare;
               ParamByName('@ID').AsString := fcontidId;
               ParamByName('@ContId').AsInteger := fContId;
               ParamByName('@Honorific').AsString := fHonorific;
               ParamByName('@FirstName').AsString := fFirstName;
               ParamByName('@MiddleInit').AsString := fMiddleInit;
               ParamByName('@LastName').AsString := fLastName;
               ParamByName('@Suffix').AsString := fSuffix;
               ParamByName('@Salutation').AsString := fSalutation;
               ParamByName('@Title').AsString := fTitle;
               ParamByName('@EMail').AsString := fEMail;
               ParamByName('@WorkPhone').AsString := fWorkPhone;
               ParamByName('@Fax').AsString := fFax;
               ParamByName('@MobilePhone').AsString := fMobilePhone;
               ParamByName('@Pager').AsString := fPager;
               ParamByName('@HomePhone').AsString := fHomePhone;
               ParamByName('@HomeFax').AsString := fHomeFax;
               ParamByName('@OtherPhone').AsString := fOtherPhone;
               ParamByName('@Note').AsString := fNote;
               ParamByName('@PrimaryEmpl').AsString := fPrimaryEmpl;
               ParamByName('@OfficeLocation').AsString := fOfficeLocation;
               ExecProc;      //sometimes raises the {*word*193} General Sql
Error
                                    // form using this function off the
apply button just waits
                                    // hit the old apply btn again and its
ok
               GetResults;
               If (ParambyName('RETURN_VALUE').AsInteger < 0)
                  and (parambyname('return_value').asInteger > -150) then
                  Result:= false;
                  close;
           end
    else
       Result := false;
end;

--
Please post replies - I've been spammed
enough

 

Re:SQL Server and GENERAL SQL ERROR


Quote
Jill Marquiss wrote:

> 80 times out of 100 it works fine
> So,
> 20 times out of 100 the exec raises a General Sql Error with no further
> information

Does this look familiar?

General SQL error.  ORA-04031: unable to allocate 96 bytes of shared
memory ("unknown object","library cache","kglob").

We have the same type of bug floating around.  If you figure out
something, please send me a note to bl...@sequel.com.

Bill

Re:SQL Server and GENERAL SQL ERROR


This happens with TCP/IP, but not other drivers. Try surrounding the
ExecProc with a  
NumRetries := 0;
bError := False;
Repeat
  try
   Sp.ExecProc;
  except
    on exception do begin
      inc(NumRetries);
      if NumRetries > 1 then
       raise;
     end;
   end; {try}
 until (NumRetries=0);

Jill Marquiss <SaveMeFromS...@nowhere.com> wrote in article
<01bc0ae9$ce8d0f60$0b29eace@gopher>...

Quote
> So,
> I have a tstoredprocedure component hooked to my sql server stored
> procedure.
> So,
> 80 times out of 100 it works fine
> So,
> 20 times out of 100 the exec raises a General Sql Error with no further
> information
> So,
> Execute it again - without doing anything and it works fine
> So,
> what now? I remember somebody having a similar thing with Sql Server
> and have searched the old news to no results

> Using delphi 2.0 developer , using odbc driver 2.65.0236
> Using BDE with fat32 patch
> Using Sql Server 6.0 - patch level 2
> Code for CodeHeads

> Function TMyContId.Change: boolean;
> Begin
>     Result := true;
>     If fcontidId <> '' then
>          With fUpdateProc do
>             Begin
>                If not Prepared then Prepare;
>                ParamByName('@ID').AsString := fcontidId;
>                ParamByName('@ContId').AsInteger := fContId;
>                ParamByName('@Honorific').AsString := fHonorific;
>                ParamByName('@FirstName').AsString := fFirstName;
>                ParamByName('@MiddleInit').AsString := fMiddleInit;
>                ParamByName('@LastName').AsString := fLastName;
>                ParamByName('@Suffix').AsString := fSuffix;
>                ParamByName('@Salutation').AsString := fSalutation;
>                ParamByName('@Title').AsString := fTitle;
>                ParamByName('@EMail').AsString := fEMail;
>                ParamByName('@WorkPhone').AsString := fWorkPhone;
>                ParamByName('@Fax').AsString := fFax;
>                ParamByName('@MobilePhone').AsString := fMobilePhone;
>                ParamByName('@Pager').AsString := fPager;
>                ParamByName('@HomePhone').AsString := fHomePhone;
>                ParamByName('@HomeFax').AsString := fHomeFax;
>                ParamByName('@OtherPhone').AsString := fOtherPhone;
>                ParamByName('@Note').AsString := fNote;
>                ParamByName('@PrimaryEmpl').AsString := fPrimaryEmpl;
>                ParamByName('@OfficeLocation').AsString :=
fOfficeLocation;
>                ExecProc;      //sometimes raises the {*word*193} General Sql
> Error
>                                     // form using this function off the
> apply button just waits
>                                     // hit the old apply btn again and
its
> ok
>                GetResults;
>                If (ParambyName('RETURN_VALUE').AsInteger < 0)
>                   and (parambyname('return_value').asInteger > -150) then
>                   Result:= false;
>                   close;
>            end
>     else
>        Result := false;
> end;

> --
> Please post replies - I've been spammed
> enough

Other Threads