1. Linda Kelty (Dudzic)
  2. PowerBuilder
  3. Thursday, 16 April 2020 00:22 AM UTC

As a workaround for the problem with embedded SQL calling a stored procedure with an OUTPUT parameter (which I reported earlier this week), I used a remote procedure call. I am using PB19 on W10, SQLNCLI11 and SQL Server 2016.

The remote procedure call gives the error: SQLdbcode = 999, SQLerrtext = “Can not get parameters of procedure dbo.proc_get_default_tax_cd. 

I have reduced this to a minimal example of a stored procedure and a PB 2019 application that invokes the two different methods. (Our server is down; I'll attach later.)

This error occurs in the most critical scenario for the application. It prevents my testing through the application to ensure that I've fixed all the changes that were necessary to the RAISERROR syntax in numerous triggers and stored procedures. I can work around it during testing by commenting out the code and just entering the necessary value manually. Can't go live this way.

Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 16 April 2020 01:05 AM UTC
  2. PowerBuilder
  3. # 1

Hi Linda,

this rings a very far away bell.

Have a look at BindSPInput or RPCRebind in the powerbuilder help file. They are dbparm values, meanwhile I'll see if I can reproduce your problem.

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 16 April 2020 01:40 AM UTC
  2. PowerBuilder
  3. # 2

Hmmm, I think I could - by accident - reproduce your problem, but not by using these dbparm parameters I suggested before.

I got the exact same error as you did in my sample application and it was due to ...

Not having set the database name in the connection profile

:)

Could it be you have the same problem?

This is my (now working) profile:

// Profile SNC-profile
SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"
SQLCA.LogPass = "some password"
SQLCA.ServerName = "DELL-XPS8300\sqlexpress"
SQLCA.LogId = "sa"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Provider='SQLNCLI11',Database='test-miguel',BindSPInput=1"

Comment
  1. Linda Kelty (Dudzic)
  2. Thursday, 16 April 2020 04:27 AM UTC
OK, figured it out! "Execute" permission on the problem stored procedure was missing, whereas the "Execute" permission had been granted on the procedure in the other database. One time, having intentionally omitted the OUTPUT keyword from the embedded procedure call, PB did give the correct database error message about permissions. Both embedded procedure call and RPC now work correctly in my test case, and the RPC method now works in my live application, but the embedded procedure call still gives the #179 error regarding OUTPUT parameter in the live application.
  1. Helpful
  1. Miguel Leeuwe
  2. Thursday, 16 April 2020 04:33 AM UTC
Gosh! That's a very silly error you got then, complaining about parameters ... To be true I got that same silly error when I wasn't even connected to a database !!

As for the embedded procedure, can't you just change it to RPCFunc too?

Also: I noticed in you other post that in your transaction object, you declare the stored procedure as

FUNCTION long .......alias....

if this is a stored procedure which doesn't do a RETURN officially you should do the local external function declaration like this:

SUBROUTINE ....... alias ....

(without the "long" since it doesn't return anything. you get the values by ref )

  1. Helpful
  1. Miguel Leeuwe
  2. Thursday, 16 April 2020 04:36 AM UTC
Have you tried the BindSPInput parameter for the "embedded" call?
  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 16 April 2020 04:56 AM UTC
  2. PowerBuilder
  3. # 3

For the remaining embedded call which still gives you an error. I got this when calling an stored procedure the "embedded way", when I accidentally hadn't used the correct parameter name (I'm using @parm1 twice). After changing the second @parm1 to @parm2 ... everything suddenly worked. So ... could you maybe have a wrong or misspelled parameter name?

Comment
  1. Miguel Leeuwe
  2. Thursday, 16 April 2020 04:58 AM UTC
gonna get some sleep, it's daylight already. 3 hours left.

Good luck!
  1. Helpful
There are no comments made yet.
Ken Guo @Appeon Accepted Answer Pending Moderation
  1. Thursday, 16 April 2020 10:16 AM UTC
  2. PowerBuilder
  3. # 4

Hi Linda,

If you can also reproduce this issue using your small case, it would be very helpful for us to track and analyze this issue if you could go to the Appeon Support ticket system (https://www.appeon.com/standardsupport/newbug) to report a bug and also upload your test case including PBL and SP syntax. Thanks in advance.

Regards,
Ken

Comment
There are no comments made yet.
yann dulondel Accepted Answer Pending Moderation
  1. Monday, 11 December 2023 09:36 AM UTC
  2. PowerBuilder
  3. # 5

I have the same issue with MSOLEDBSQL server.
Using SQLNLCI11 all works fine , but  when i switch on new ms driver ,all RPC crash

The version MSOLEDBSQL19 is 19.3.0.0 and 19.2.0.0 have the same issue.

 

 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.