March 5, 2011 at 9:25 pm
I'll start this off by saying that this has worked for months, and just started failing with no changes on either the Oracle (11g) or SQL Server (2008 R2 CU5) side. What is weird is that it works depending on the current DB selected.
This statement:
USE LIBRARIES;
GO
SELECT TOP 1
EXTRACT_DATE
FROM EOracle.dbo.clm_edit_wq_clm
GO
returns this error:
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "EOracleSrv" supplied inconsistent metadata for a column. The column "EXTRACT_DATE" (compile-time ordinal 2) of object "Select * from CLEAR.CLM_EDIT_WQ_CLM" was reported to have a "DBCOLUMNFLAGS_ISFIXEDLENGTH" of 16 at compile time and 16 at run time.
But this:
USE EOracle;
GO
SELECT TOP 1
EXTRACT_DATE
FROM EOracle.dbo.clm_edit_wq_clm
GO
works just fine. EOracle.dbo.clm_edit_wq_clm is a view that either uses the <linked server>..<schema>.<object> or OPENQUERY method to select data from the linked Oracle server.
I have no idea why it suddenly stopped working, why it thinks 16 is a change from 16, or why the default database for the connection matters.
Any ideas?
March 6, 2011 at 12:44 am
I haven't seen the problem before but have you tried the easy button of rebuilding the linked server and/or view to force a refresh of any metadata it may think exists?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 7, 2011 at 9:43 am
I didn't rebuild the linked server, but I did change a parameter and then change it back, and I have re-created the view multiple times. (Trying OPENQUERY vs. four part naming, etc.)
I tried restarting the SQL Server as well, so far no luck.
March 7, 2011 at 10:37 am
UMG Developer (3/7/2011)
I didn't rebuild the linked server, but I did change a parameter and then change it back, and I have re-created the view multiple times. (Trying OPENQUERY vs. four part naming, etc.)I tried restarting the SQL Server as well, so far no luck.
I have experienced this issue with our Oracle (11g, I think) linked server on our SQL Server 2005 instance. Our DBAs tell me that it is a result of changes to how SQL Server processes queries against non-SQL Server linked servers. Anyone have any more specific information on this error?
Using OPENQUERY instead of SELECT against the linked server tables has avoided the error for us.
Jason Wolfkill
March 7, 2011 at 10:53 am
OK, I figured it out, and it wasn't at all what I expected. Someone had changed the compatibility level on the Libraries DB to 100, while the EOracle DB still had a compatibility level of 90. Changing the Libraries DB back to 90 resolved the problem.
Now why the compatibility level being different between DBs causes this problem I have no idea, but at least I know, and maybe someone else that runs into this in the future will be able to find this and save themselves some time.
March 7, 2011 at 1:05 pm
March 7, 2011 at 1:30 pm
The "Select *" statement is what is contained in the view. Extract_Date is an Oracle DATE column, so it is similar to the SQL DATETIME data type.
March 7, 2011 at 2:53 pm
UMG Developer (3/7/2011)
OK, I figured it out, and it wasn't at all what I expected. Someone had changed the compatibility level on the Libraries DB to 100, while the EOracle DB still had a compatibility level of 90. Changing the Libraries DB back to 90 resolved the problem.Now why the compatibility level being different between DBs causes this problem I have no idea, but at least I know, and maybe someone else that runs into this in the future will be able to find this and save themselves some time.
Great feedback!
Somebody working similar issue in the middle of the night will be grateful to you forever 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 11, 2011 at 12:13 am
OK, I figured it out, and it wasn't at all what I expected. Someone had changed the compatibility level on the Libraries DB to 100, while the EOracle DB still had a compatibility level of 90. Changing the Libraries DB back to 90 resolved the problem.
Now why the compatibility level being different between DBs causes this problem I have no idea, but at least I know, and maybe someone else that runs into this in the future will be able to find this and save themselves some time.
Great tip,
I just had the same error message with the same scenario, and your'e right...
Changing the comp. level to 90 does help, but...
it seems that upgrading from 2k5 to 2k8 is more tricky than I though it would be. I seems to have lots of error and different behaviors between 2 versions. unfortunately, the solution is to set the comp. level 90 every time.
not my favorite solution though...
Does anyone knows why it behaves like it does?
Thanks anyway... It helped me a lot!
Oz
April 11, 2011 at 12:39 am
UMG Developer (3/5/2011)
I'll start this off by saying that this has worked for months, and just started failing with no changes on either the Oracle (11g) or SQL Server (2008 R2 CU5) side. What is weird is that it works depending on the current DB selected.
UMG Developer (3/7/2011)
OK, I figured it out, and it wasn't at all what I expected. Someone had changed the compatibility level on the Libraries DB to 100, while the EOracle DB still had a compatibility level of 90. Changing the Libraries DB back to 90 resolved the problem.Now why the compatibility level being different between DBs causes this problem I have no idea, but at least I know, and maybe someone else that runs into this in the future will be able to find this and save themselves some time.
Note to self... always assume that something changed without my knowledge and look for it right up front.
2nd note to self... find the person who made the change without discussing it and invite him/her out for a high velocity pork chop dinner.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2011 at 10:11 am
Jeff Moden (4/11/2011)
Note to self... always assume that something changed without my knowledge and look for it right up front.
I had assumed something changed and started looking on the Oracle side, and checking with the Oracle DBAs. I was looking on the SQL Server side and couldn't find anything, but the compatibility level wasn't one of the things that came to mind to check right away.
2nd note to self... find the person who made the change without discussing it and invite him/her out for a high velocity pork chop dinner.
I've seen mention of this a number of times, and I still don't understand...
April 12, 2011 at 12:16 pm
UMG Developer (4/11/2011)
2nd note to self... find the person who made the change without discussing it and invite him/her out for a high velocity pork chop dinner.
I've seen mention of this a number of times, and I still don't understand...
To make a very long story shorter, some manager posted a few years ago about an out-of-control developer and asked what to do about it. I made several "politically correct" suggestions and the manager kept whining about why he couldn't do this and that, etc, etc, none of which many any sense to me. I got a bit fed up and told the manager how to take control so I suggested something close to the following...
"Take the developer out to dinner. Tie him to the chair and feed him pork chops... at point blank range with a "Wrist Rocket". That will at least get his attention."
For those that don't know what a "Wrist Rocket" is, it's a particularly powerfull slingshot.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 13, 2011 at 2:37 pm
Odd that it fails with a compatibility mode of 100 - I wonder if it's something to do with mapping from Oracle's dates to the new date/datetime2 data types.
Would you mind letting me know what version of the Oracle OLEDB driver you're using and what's the Oracle data type for EXTRACT_DATE?
I've just upgraded a reporting database to SQL 2008 and it uses OPENQUERY views to Oracle - currently it's still in 90 compatibility mode, but that's something to watch out for, thanks for sharing
April 13, 2011 at 2:58 pm
HowardW (4/13/2011)
Odd that it fails with a compatibility mode of 100 - I wonder if it's something to do with mapping from Oracle's dates to the new date/datetime2 data types.Would you mind letting me know what version of the Oracle OLEDB driver you're using and what's the Oracle data type for EXTRACT_DATE?
I've just upgraded a reporting database to SQL 2008 and it uses OPENQUERY views to Oracle - currently it's still in 90 compatibility mode, but that's something to watch out for, thanks for sharing
Howard,
The problem isn't the compatibility mode itself, the problem was that two SQL server DBs were involved and they had a different compatibility mode. It works if both of them are set the same. (i.e. both DBs set to 90 or 100.)
But to answer your questions the EXTRACT_DATE column is an Oracle DATE type, and we are using the "Oracle Provider for OLE DB 10.2.0.4.0" provider. (Someday we should upgrade to the 11g version.)
April 14, 2011 at 1:52 am
Ah, sorry, I misunderstood. So EOracle is a SQL Server DB and clm_edit_wq_clm is a view on it that uses a linked server to Oracle?
Yeah, that driver is a little old - I seem to remeber that one had a load of bugs converting numeric data to SQL Server types. Having said that, each Oracle driver has it's own unique set of bugs to treasure, so while it works for what you're doing it's probably not worth the effort in upgrading 🙂
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply