So I'm now working in Oracle, but yet still working in SQL Server. Fun times (no sarcasm, I'm loving it)! But I ran into an issue today when trying to get information about Data Guard from Oracle into my SQL Server so I can report on latency.
I have the linked server set up (make sure you have the correct driver on your SQL box, edit the TNSNAMES.ORA file, then create the linked server, tons of info out there on how to do that). What I was having an issue with was getting the V$ views returned from a query without an error.
For instance, this throws an error:
SELECT * FROM <your linked server>..SYS.V$DATAFILE
You'll get:
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "<your linked server>" does not contain the table ""SYS"."V$DATAFILE"". The table either does not exist or the current user does not have permissions on that table.
While this syntax works:
Select * from openquery (<your linked server>,'SELECT * FROM V$DATAFILE')
WOOHOO!
And obviously linked server names have been replaced.
Don't bother doing a Google search; I found a lot of nothing! Hence my post. Hope this helps!
No comments:
Post a Comment