Search code examples
sql-serveroraclesql-server-2008-r2oledblinked-server

SQL Server not geting rows from OraOLEDB linked server

I have two Windows 2008 Server 64x machines. One running Oracle 10.2.x.x.x (Express Edition) and other SQL Server 2008 R2 with ODAC 12c (12.1.0.2.4).

I had created linked server, tested connection and it passes. I can see all Oracle tables, but when I query them, for example:

SELECT *
FROM ORACLE..USER.PERSON

All I get back are columns without no rows. If I run query from Oracle SQL Developer I get around 13000 rows.

What could be problem? I thought it was problem with backward compatibility, but according to this link it is not. It could be something with permissions/security?

Solution

  • Well it was nothing to do with linked server configuration. I didn't know that after every insert I need to commit changes.

    SQL> commit;