0

I have setup linked servers on my SQL Server and choose Oracle as a linked server.

SOMETIMES, When I run the query below under the SA username, I get different result. However when I run under my own username I get correct result. I Can't understand this anomaly. The below query returns only one row. The difference occurs in ORGANIZATION_FULL_NAME column only.

SELECT * FROM OPENQUERY(HR_ORACLE,'
            SELECT e.CHILD_ORG_IND_MGR_POS_PER_ID,e.CURRENT_EMPLOYEE_FLAG,e.EMAIL_ADDRESS,
            e.ENTERPRISE_MANAGER_PERSON_ID,e.ENTERPRISE_MANAGER_POSITION_ID,e.FIRST_NAME,e.LAST_NAME,e.FULL_NAME,e.GENDER,e.JOB_CATEGORY_CODE,e.JOB_CATEGORY_NAME,
            e.JOB_ID,e.JOB_NAME,e.JOB_SUBCATEGORY_CODE,
            e.JOB_SUBCATEGORY_NAME,e.MOBILE_PHONE_NUMBER,ltrim(rtrim(e.NATIONAL_IDENTIFIER)) NATIONAL_IDENTIFIER,e.ORG_CURATOR_PERSON_ID,e.ORG_STR_DEDUCTED_MGR_PERSON_ID,e.ORGANIZATION_FULL_NAME,e.ORGANIZATION_ID,
            e.ORGANIZATION_SHORT_NAME,e.PARENT_ORG_IND_MGR_POS_PER_ID,e.PARENT_ORG_IND_MGR_POSITION_ID,e.PATRONYMIC,e.PERSON_ID,e.PERSON_IS_CURATOR_FLAG,e.PERSON_IS_ENTERPRISE_MGR_FLAG,
            e.POS_ORG_MGR_POSITION_FLAG,e.POSITION_ID,e.L3_PARENT_ORGANIZATION_ID
            FROM XXRCR.XXRCR_CUR_EMPS_EVER_V e
            WHERE e.EMAIL_ADDRESS like (''%EMAIL@EMAIL.COM'')  ')

These are my linked server settings:

enter image description here

enter image description here

and view definition:

enter image description here

  • 2
    Please provide a definition of XXRCR.XXRCR_CUR_EMPS_EVER_V. It may be a view with a row level security... – Piotr Jun 16 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.