June 16, 2020 at 8:45 am
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:
June 16, 2020 at 1:11 pm
Based on the info, I can't give you a precise answer. However, the place to look is permissions on the linked server. There must be a difference there when you run the query. Or, permissions on the local server and JOIN criteria between the two is causing differences. Possibly a view or table you have permission to, or don't, is different. Something along these lines is the most likely explanation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2020 at 1:25 pm
I suspect the Oracle view, XXRCR.XXRCR_CUR_EMPS_EVER_V, has a non-deterministic way of obtaining ORGANIZATION_FULL_NAME. ie The problem is likely to be with how the Oracle view is defined and so has nothing to do with SQL Server.
June 16, 2020 at 3:45 pm
interesting part is that this only happens when I run that query under SA user. When execute that query under different name, then everything ok. I decided to firstly update oracle oledb driver as we are using the old driver. I will write my results here. Thank you.
June 16, 2020 at 5:57 pm
How is the linked server security defined? Is it a fixed username/password or do you have some type of mapping set up?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 16, 2020 at 7:38 pm
Are there any entries in the mapping section above this? It seems the issue is related to the user accessing the linked server - is it possible that the sa account is mapped to a different login in Oracle and that column in the view is defined to return a different value based on the user accessing the view?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 16, 2020 at 8:08 pm
mapping section is empty. I don't think so because when I execute that query under SA user, I get randomly 2 different result for that specific column. I can't predict in which execution I get A value or B value for ORGANIZATION_FULL_NAME column. I get either A value or B value randomly. It makes me crazy.
June 16, 2020 at 8:09 pm
Now it is midnight here and I am gonna update driver and see what happens
June 16, 2020 at 8:39 pm
I have updated OLEDB driver. The same thing( nothing has changed
June 16, 2020 at 9:15 pm
interesting part is that this only happens when I run that query under SA user. When execute that query under different name, then everything ok. I decided to firstly update oracle oledb driver as we are using the old driver. I will write my results here. Thank you.
What are the differences between the SA user and the different user(s) that you may use that don't have the problem?
June 16, 2020 at 9:30 pm
it is also an interesting question, but I don't know. The other user credentials which I tested is my own user and it has sysadmin privileges as well. Both of these users (SA user and my own user) use remote login and password as I stated before in order to access the view that is why I don't know how to compare these users as they both use the same credentials to access remote server. Moreover, I restarted SQL Server, but again nothing changed. I think if we can find out the that difference then maybe we can approach the solution that we are looking for.
June 16, 2020 at 9:32 pm
Tomorrow I will check out the oracle side including the pointed view and permissions and will share my results
June 16, 2020 at 9:37 pm
It looks to me like the problem is on the SQL Server side since all the users you try are using the same credentials on the Oracle side.
I would start looking at the permissions and groups to which the users belong looking for differences with the SA account. Something is different and that difference is what may be the problem.
June 16, 2020 at 9:40 pm
Also look at the connection settings for the users, there may be difference between the users there.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply