I believe this problem first occured after installing SP5a for MS SQL
Server 6.5

One of my apps calls a stored procedure using a TQuery.  The app
generates the error below.

Message: Operation not applicable.
   Category: 42
   Error Code: 10756
   SubCode: 4
   Native Error: 0

I created dummy app that just queries using the stored proc and I get
the same error whether I use a TQuery or TStoredProc.  I pasted the SQL
into the TQuery and it works fine.

Any ideas would be appreciated.

John

On Fri, 07 May 1999 15:08:44 -0700, "John C. Noland" <jnol...@remove.firstam.com> wrote:

You are sure that before SP5 everything was OK?

Can you post the SP code and the way you launch it in delphi ?

tomi

Yes, the app has been working for months before I updated to SP5a (which is not the same as
SP5).

The procedure that creates the query and executes the MSSQL stored procedure follows:

procedure TfrmSecurity.GetRightsList(dbName, userName, password: string; UserRights:
TUserRights);
var
   aQry: TQuery;
   StringIndex: Integer;
begin
   UserRights.RightsList.Clear;
   if (userName = '') then Exit;

   aQry := TQuery.Create(nil);
   try
    with aQry do begin
     DataBaseName := dbName;
     RequestLive := false;
     SQL.Add('usp_security_rights :user, :password  ');
     Prepare;
     ParamByName('user').AsString := userName;
     ParamByName('password').AsString := password;
     Open;
     UserRights.ContactID := FieldByName('lContactID').AsInteger;  {jcn}
     UserRights.GroupID   := FieldByName('lGroupID').AsInteger;    {jcn}
     while not eof do begin
       StringIndex := UserRights.RightsList.IndexOf(Fields[0].Text);
       if (StringIndex = -1) then begin
        UserRights.RightsList.Add(Fields[0].Text);
        UserRights.RightsVal.Add(Fields[1].AsString);
       end else
        if (StrToInt(UserRights.RightsVal.Strings[StringIndex]) < Fields[1].AsInteger) then
         UserRights.RightsVal.Strings[StringIndex] := Fields[1].AsString;
      Next;
     end;
    end;
   except
    on E: EDBEngineError do begin
     LogError(E, 'Unable to retrieve your access rights');
    end;
   end;
   aQry.Free;
end;

Hi John!

Well can't say anything from your code, but I suggest you to find the
line that causes the trouble. When I have such a problems I just put
debug information here and there in a code and then I locate line that
is causing the trouble. You can also use debuger if you like.

So, first find out what instruction is making trouble and than we can
discuss more, or you can see what can be in relation to that
instruction etc. Sometimes you have to guess even.

So far I'am not of much help, anyway please write if you consider I
can help you somehow.

tomi.

The line that causes the problem is "OPEN" and the same code works in
ISQL.  It also works if I don't use a stored procedure and put the query
in a TQuery.

The procedure that creates the query and executes the MSSQL stored
procedure follows:

procedure TfrmSecurity.GetRightsList(dbName, userName, password:
string; UserRights:
TUserRights);
var
   aQry: TQuery;
   StringIndex: Integer;
begin
   UserRights.RightsList.Clear;
   if (userName = '') then Exit;

   aQry := TQuery.Create(nil);
   try
    with aQry do begin
     DataBaseName := dbName;
     RequestLive := false;
     SQL.Add('usp_security_rights :user, :password  ');
     Prepare;
     ParamByName('user').AsString := userName;
     ParamByName('password').AsString := password;
     Open;
     UserRights.ContactID := FieldByName('lContactID').AsInteger;
{jcn}
     UserRights.GroupID   := FieldByName('lGroupID').AsInteger;
{jcn}
     while not eof do begin
       StringIndex :=
UserRights.RightsList.IndexOf(Fields[0].Text);
       if (StringIndex = -1) then begin
        UserRights.RightsList.Add(Fields[0].Text);
        UserRights.RightsVal.Add(Fields[1].AsString);
       end else
        if (StrToInt(UserRights.RightsVal.Strings[StringIndex]) <
Fields[1].AsInteger) then
         UserRights.RightsVal.Strings[StringIndex] :=
Fields[1].AsString;
      Next;
     end;
    end;
   except
    on E: EDBEngineError do begin
     LogError(E, 'Unable to retrieve your access rights');
    end;
   end;
   aQry.Free;
end;

Hi John!

I have some ideas what may be wrong.

On Tue, 11 May 1999 11:43:10 -0700, "John C. Noland"

When you create TQuery.SQL statement with parameters than you have to
specify the type for parameters because TQuery can not know that from
scratch. I guess that when you assign params with As prefix that then
their type is automaticaly determined but I am not sure.

In this case here I would add four more lines:
  ParamByName('user').DataType := ftString;
  ParamByName('user').ParamType := ptInput;
  ParamByName('password').DataType := ftString;
  ParamByName('password').ParamType := ptInput;

Also I would suggest not to use that Prepare command here, or not to
use it at all - because if you Prepare manualy like you did than you
have to UnPrepare also. Otherwise Open will prepare/unprepare
automaticaly for you. It is obvious that you do not get it right when
to use Prepare, there was some discussion on this in this news group.

please try changes I suggested and tell how it goes ...

tomi.

That did not help.  However, I have narrowed to the problem down to passing
parameters!

If I do

 SQL.Add('usp_Security_Rights "userid", "password");

it works fine.  If I do

  SQL.Add('usp_security_rights :user, :password  ');

and then set the parm values, I get not a valid operation error.  Obviously,
I could change all my code not to use parameters but that is not acceptable.
I am only experiencing this problem on one computer.  My setup is a little
odd, so let me give you some more details.

I have a development network with an NT Server and a few Win95 clients.  When
I run the program from my development workstations it works fine.

Now, my NT Server is connected to the internet via a cable modem.  If I use
my NT server as a work station, I can connect to my customers production
machine and run the program against their NT server.  Its from my server that
I am experiencing the problem.

Its after I updated my NT server with SP3 and my SQL Server with SP5a that I
started to experience the problems.  I have reinstalled my old version of SQL
Server and ODBC but I still experience the problem.

Sorry John, i can not help you on this one :-(