Error executing a compiled single shot join query with SQL DataServer when executed with another user

single shot query in MSS dataserver errors while executing a query with a join with SQL DataServer when compiled with one user and executed with another user

Mar 9, 2020
Knowledge

Title

URL Name

Error-while-executing-a-compiled-single-shot-query-with-SQL-DataServer-when-compiled-with-one-user-and-executed-with-another-user

Article Number

000155341

Environment

Product: OpenEdge Version: 11.6, 11.7.1, 11.7.1, 11.7.2 OS: Windows Other: Data servers

Question/Problem Description

Single shot query in MSS dataserver fails when compiled with one user and executed with another user.
SQL DataServer single shot query with a join fails when executed by user who did not compile the ABL code

Steps to Reproduce

Compile the following single shot query: FOR EACH customer, FIRST order OF customer: DISP customer.cust-num order.order-num. END. Run the compiled r-code with another schemaholder having the same name but different MSS user or DB.

Clarifying Information

It works in dynamic queries, or while running un-compiled code.

Error Message

08004: [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal "<>" is not able to access the database "<database_name>" under the current security context.

Defect Number

ODIA-1078

Enhancement Number

Cause

This is a specific use case of single-shot query.

Resolution

Upgrade to OpenEdge 11.7.3 or later where Single shot changes support different for-owner at compile-time and run-time

This is a change in dataserver compiler layer for single-shot query (e.g. FOR EACH customer, FIRST order OF customer) in 11.7.3. If an application uses single-shot query and is compiled with 11.7.3, it should be run with 11.7.3 and above (both client and server).
It is advisable to upgrade to at least 11.7.4: 11.7.3 clients crash executing a single-shot query

Workaround

Force the join to be performed on the OpenEdge client so the sorting is not done at the server side using the
-nojoinbysqldb parameter or the NO-JOIN-BY-SQLDB query-tuning option in the application logic, to override single-shot feature

For example: During compilation and at run-time

$DLC/bin/_progres -nojoinbysqldb

In ABL - server side sorting can be disabled on a query by query basis:

FOR EACH customer, EACH order OF customer QUERY-TUNING(NO-JOIN-BY-SQLDB):

Notes

References to Other Documentation:

OpenEdge Data Management: DataServer for Microsoft SQL Server, Additional Features to Enhance DataServer Performance -
Join by SQL DB
https://docs.progress.com/bundle/openedge-microsoft-sql-dataserver/page/Join-by-SQL-DB.html

Keyword Phrase

Last Modified Date

10/4/2021 2:59 PM

Error executing a compiled single shot join query with SQL DataServer when executed with another user

Files(0)

Was this article helpful?

Was this article helpful?

Error executing a compiled single shot join query with SQL DataServer when executed with another user

Information

Related Articles