Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-22-2014, 08:50 AM   #1
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,800
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Lightbulb MSSQLServer Linked Server to Oracle - Strange Behavior after updating SQL settings

Started a question. Got it working. This is now for the benefit of somone else using an Access Linked table to SQL Server view that consumes an Oracle DB using Linked Servers. (Does that count as a run-on sentence?)

The Access database has a DSN-Less Linked table to MSSQLServer for a query based on a Linked Server to Oracle. This requires the Oracle Client OLE DB ODBC driver. (See attachment). The attachment doesn't show the Access linked table. The view in SQL Server uses the Cast to change Oracle Text over to Integers or other data types.

This was all working fine until the IT dept notified me that the Oracle host was moved over a weekend. The UN/PW was also changed.
- Go into TNSName.ORA and edit the reference (see example below).
- Go to Linked Server in SQL and update the UN/PW

A link to one of three test DB worked perfectlly.
The links to production (and two other test) had an error. Error:
Can't Caculate a text field.

This is strange because all 4 instance of the SQL Server Databases are in one SQL Server. They all point to the exact same instance of a Linked Server. That is, a table in each SQL Server DB have the same view that consumes a Oracle Linked Server's table. (see the attachment)

It is stranger because even down to the Linked Table of Access, the Linked Table def had the header information and data types.
Just no data on the Production DB using the same linked table
While the Test DB using the same linked table had connection and the data.

The Test DB was a restored backup of the production DB.
A second Test DB behaved like the production - no data.

The lack of data was at the SQL Server view.
- Dropped and recreated linked table from Access - no change
- Scripted and created a 2nd SQL Server view - it behaved as the orginal view on production. The test 2nd view showed data as its parent did.
- Dropped and recreated the view of the linked table. No change.

Solution: About 5:30 PM, fustrated, took a 10 minute walk around downtown making a decision if to stop at the Tilted Kilt for a drink.
Returned to office at 5:30 to go through all steps again.
Clicked on the Access linked table to start at end documenting each error.
The production copy worked perfectlly! I didn't do a thing except stop using SQL Server Enterprise Manager for troubleshooting.
Has been working all night and all day now.

The 3rd Test DB on SQL Server this morning still had the exact same Linked Server error. Put that 3rd SQL Test dB into single user mode, stopped service, restarted. The Linked Server data worked perfectlly.
I started the question but now things are working.

Final Solution:
For anyone using SQL Server Linked Servers, Stop and restart the Database consuming the Linked Server before spending hours assuming something is wrong.
Had I stopped and restarted the entire SQL Server, all the DB would have probably refreshed. This indicates a property change in Linked Servers may not replicate across all SQL Server DB equally.


A TNSName.ORA file will resemble this:
Code:
MySQLReference.MyCompany.COM=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=MyHostName)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=MyOracleServiceName.MyCompany.COM)
    )
  )
Another very useful Blog: http://oraclequirks.blogspot.com/
This site is: "Annals of Oracle's Improbable Errors"
Attached Images
File Type: jpg Concept of SQL Server Linked Server to Oracle.jpg (71.3 KB, 99 views)

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!

Last edited by Rx_; 10-22-2014 at 09:13 AM.
Rx_ is offline   Reply With Quote
Reply

Tags
linked server , odbc , oracle , sql server

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Issue while running an update query using Oracle linked server data Rajaram Queries 3 06-27-2013 02:22 PM
strange behavior buratti Modules & VBA 1 10-27-2010 09:10 PM
Strange behavior sven2 Modules & VBA 0 06-15-2009 09:44 PM
a subform with a strange behavior odrap Queries 1 02-08-2009 03:31 AM
Strange behavior odrap Queries 5 02-03-2009 02:21 PM




All times are GMT -8. The time now is 11:36 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World