• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • MSSQL temporary tables (#) creation (AZURE) from Powerbuilder Web

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved MSSQL temporary tables (#) creation (AZURE) from Powerbuilder Web

  1. Misc
0
Votes
Undo
  1. Pierre Rene
    Pierre Rene
  2. PowerBuilder
  3. Wednesday, 17 February 2021 22:20 PM UTC

Hi there,

Powerbuilder 12.5 classic application has no issues creating temporary tables in an Azure MSSQL database (account dbo user) but for some unknown reason... a deployed web application from PB 12.5 using the same connection parameters (user name/password) suddenly does not have 'CREATE' permissions.

Tried it with an ADO.NET and SNC.... no joy ;(

Any suggestions ?

Thanks in advance

  1. ADO.NET
  2. Permission
  3. PowerBuilder (Appeon)
  4. SQL
Deploy PowerServer object Error: Illegal character...
PowerClient and richtext edit control versions
Responses (2)
Pierre Rene
Pierre Rene Accepted Answer Pending Moderation
  1. Wednesday, 17 February 2021 22:43 PM UTC
  2. PowerBuilder
  3. # 1

Hi Chris,

Yes, Autocommit is set to TRUE in the transaction object. (set via same INI file web/non-web)

It's an 'embedded' CREATE command (powerscript) 

// create temporary table
ls_temp = "CREATE TABLE #ipas_pension_summarization " + &
"(calendar_year decimal(4,0) NOT NULL, " + &
"contribution_rate decimal(10,4) NOT NULL, " + &
"effective_months decimal(10,4) NOT NULL, " + &
"months_to_distribute decimal(4,0) NOT NULL, " + &
"employer_code varchar(5) NOT NULL, " + &
"schedule_id decimal(10,0) NULL)"

EXECUTE IMMEDIATE :ls_temp;

Works fine from a local compiled app... but not when deployed as a web app. Does APPEON play with the autocommit at connection time ?

Can you elaborate on the #TempTable route ?

Thanks in advance,

Pierre

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Wednesday, 17 February 2021 22:51 PM UTC
Web app? So you are using PowerServer?
  1. Helpful
Pierre Rene
  1. Pierre Rene
  2. Wednesday, 17 February 2021 23:00 PM UTC
The MSSQL database is an Azure resource and I've deployed the 'web' application on a virtual machine (Azure) also.

I'm testing the access from outside the Azure 'environment' ... everything works except for these pesky 'temptables'
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 17 February 2021 22:28 PM UTC
  2. PowerBuilder
  3. # 2

Hi Pierre;

   Sounds like you are using the CREATE DML option vs the #TempTable route.

   For the Create DML ... did you set "AutoCommint = TRUE" in your App's Transaction Object?

Regards ... Chris

Comment
Pierre Rene
  1. Pierre Rene
  2. Wednesday, 17 February 2021 23:06 PM UTC
Hi Chris,



Yes, Autocommit is set to TRUE in the transaction object. (set via same INI file web/non-web)



It's an 'embedded' CREATE command (powerscript)



// create temporary table

ls_temp = "CREATE TABLE #ipas_pension_summarization " + &

"(calendar_year decimal(4,0) NOT NULL, " + &

"contribution_rate decimal(10,4) NOT NULL, " + &

"effective_months decimal(10,4) NOT NULL, " + &

"months_to_distribute decimal(4,0) NOT NULL, " + &

"employer_code varchar(5) NOT NULL, " + &

"schedule_id decimal(10,0) NULL)"



EXECUTE IMMEDIATE :ls_temp;



Works fine from a local compiled app... but not when deployed as a web app. Does APPEON play with the autocommit at connection time ?



Can you elaborate on the #TempTable route ?



Thanks in advance,



Pierre
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 18 February 2021 15:54 PM UTC
Hi Pierre;

I just realized that your were talking about a PowerServer WEB based application. PS does support the AutoCommit setting. So I am not sure why Azure's SQL Server would not comply. Could be a "Borg" issue! ;-)

I would suggest opening a Support Ticket for this issue. As a workaround though ... what about using a Stored Procedure instead to create the TempTable?

Regards ... Chris
  1. Helpful
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.