Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Weird error when selecting from view. Expand / Collapse
Author
Message
Posted Monday, April 11, 2011 10:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Jeff Moden (4/11/2011)
[quote]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...
Post #1091565
Posted Tuesday, April 12, 2011 12:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1092320
Posted Wednesday, April 13, 2011 2:37 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 1,232, Visits: 9,672
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
Post #1093191
Posted Wednesday, April 13, 2011 2:58 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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.)
Post #1093208
Posted Thursday, April 14, 2011 1:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 1,232, Visits: 9,672
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
Post #1093354
Posted Thursday, April 14, 2011 10:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
HowardW (4/14/2011)
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


Yes, EOracle is a SQL Server DB and clm_edit_wq_clm is a view linked to the remote EOracle Oracle server.. (Don't ask.)

We have occasionally hit the problem with converting numeric data, but at the time we couldn't find a newer driver or any notes on the Oracle site about it. The Oracle DBAs are pushing people to upgrade to the 11g client, so we will have to at some point...
Post #1093673
Posted Thursday, May 8, 2014 4:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 9, 2014 2:50 AM
Points: 2, Visits: 4
I am running a select query on a 2008 sql server that works fine.

We are currently upgrading to sql server 2012 and when i run the same query from the 2012 MS SQL Server Management Studio i get the
" reported to have a "DBCOLUMNFLAGS_ISFIXEDLENGTH" of 16 at compile time and 16 at run time." error. Even though the connected sql server on the 2012 server is the 2008 sql server. All servers have compatibility level 100, even the DB2 server i select from through openquery

What can i do about that?
Post #1568814
Posted Thursday, May 8, 2014 4:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 5,014, Visits: 10,517
I suggest that you start a new thread. Very few people monitor old threads.

--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1568822
Posted Thursday, May 8, 2014 5:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 9, 2014 2:50 AM
Points: 2, Visits: 4
ok thx :)
Post #1568824
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse