SQL Server – Same Query, Same Linked Server, Different Result

linked-serversql server

I have two instances (SQL2008R2_A and SQL2008R2_B), they have same linked server configuration to Oracle_C. I used the same query to select from Oracle_C. SQL2008R2_A returned the correct result but SQL2008R2_B get nothing.
And I tested linked server connection of both instances. They are all successfully.
Interesting…

Best Answer

Thank you for your response. Before post this question I already double checked the settings for both linked server and TNS name file. There is nothing different between them.

Fortunately, I resolved it by just re-create the linked server at Server B. It might need to refresh some cache.

Thanks again and have a great one:)

Related Solutions

Sql-server – Can’t create linked server SQL Server 2005

As one colleague pointed out, there's no SQL Server 2003 version. I suppose you're speaking about MSSQL 2000. Which was somehow related in time with VS 2003 :-).

As you're working with instances on the same server, do you have the service "SQL Server Browser" started and running?

Why you need it:

"The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

Browsing a list of available servers
Connecting to the correct server instance
Connecting to dedicated administrator connection (DAC) endpoints

Sql-server – Sql Server 2008 x64 ODBC Linked Server to Oracle Not Working

If I try to right click on a view name and select Script -> SELECT To.... I get a message

The GUI is probably trying to access an Oracle Data Dictionary View (in order to read the column names & data types) that it does not have permission to select from, hence the error message.

Ask the Vendors Oracle DBA to trace the ODBC session so that you can track down the exact cause.

Best Answer

Related Solutions

Sql-server – Can’t create linked server SQL Server 2005

Sql-server – Sql Server 2008 x64 ODBC Linked Server to Oracle Not Working

Related Question