PB and MS Azure SQL database

[Former Member]

Does anyone have experience connecting a PowerBuilder application to a Microsoft Azure SQL database?  I am using the SNC driver and PB 12.5.2.  The connection appears to work but I am getting an error when executing an in-line stored procedure call.

“Microsoft SQL Server Native Client 10.0
Cannot use the OUTPUT option when passing a constant to a stored procedure.”

The stored procedure does indeed have two output parameters.  This same application works fine when connecting to a database hosted on a local SQL Server instance.

Here is how I am setting the DBParm:

SQLCA.DBParm = "Provider='SQLNCLI10'"
SQLCA.DBParm += ", AppName='" + gs_appname + "'"
SQLCA.DBParm += ", Database='" + ls_db_name + "'"
SQLCA.DBParm += ", Identity='IDENT_CURRENT()'"
SQLCA.DBParm += ", TrimSpaces=1"
SQLCA.DBParm += ", StaticBind=0"
SQLCA.DBParm += ", TrustedConnection=0"
SQLCA.DBParm += ", Encrypt=1"
SQLCA.DBParm += ", TrustServerCertificate=0"

[arnd_schmidt · Accepted solution]

Hi Ken,

can you try setting "DisableBind=0". Does this work?

hth

Arnd

[Former Member · Accepted solution]

Thank you for your reply, Arnd.

I experimented with that including examining the TRACE output.  It looks to me like binding is off by default but that I can turn it on by specifying:

DisableBind = 0

BindSPInput = 1

When I do that, I get a different error:

2812 - SQLState = HY000

Microsoft SQL Server Native Client 10.0

The stored procedure required to complete this operation could not be found on the server.  Please contact your system administrator.

My stored procedure does exist and the login does have permission to execute it.  The error message might refer to some system stored procedure which the driver is attempting to execute and which is not present in Azure SQL databases.  I suspect this because the same thing happens when you try to connect a PowerBuilder application to an Azure SQL database using OLE-DB.

[Former Member]

Hi Ken,

Perhaps trying to Bind the SP is what you need to do.  It is a separate option.

SQLCA.DBParm = "BindSPInput=1"

Randy

[Former Member]

An alternative approach is to define the procedure as a RPC - which may or may not fix the problem but it will make the procedure easier to call in PB. 

[Former Member]

Thank you for the idea, Scott.

I guess I will have to start considering application changes if I fail to get the database connection to work the way it is supposed to.  This application has more than a few in-line stored procedure calls so I was hoping to avoid that.