Invalid schema definition
-
Hi,
I'm hoping for some help. In Query Analyzer, i'm trying to execute the following:
SELECT wo.WORKTYPE, wo.WONUM, es.DOWNTIME, es.CHANGEDATE,
wo.WOEQ9, wo.WOEQ2
FROM EMAXDEC1..MAXIMO.WORKORDER AS wo
INNER JOIN EMAXDEC1..MAXIMO.EQSTATUS AS es ON wo.WONUM=es.WONUM
WHERE es.DOWNTIME<>0 AND es.CHANGEDATE
Between '7/28/2005' AND '8/25/2005' AND wo.WOEQ9 Like 'AUBG%'
AND (wo.WOEQ2)='C'
When I do this, I get
Server: Msg 7317, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned an invalid schema definition.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid schema definition.].
EMAXDEC1 is an oracle server. So, i went into SQL Plus and tried to excecute the query in SQL Plus against EMAXDEC1 directly (as opposed to it being a linked server in SQL Server) and I get
FROM MAXIMO.WORKORDER AS wo
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
This is really frustrating me. I've tried OPENQUERY and OPENROWSET in an attempt to work around this, and then I get error messages about the FROM keyword not being where expected.
HELP! Please!!
Jen
-
This may sound like a silly question, does the schema you are trying to use exist in the database you are connecting to? What happens if you name the database explicitly?
David
If it ain't broke, don't fix it...
-
Your query contains:
EMAXDEC1..MAXIMO.WORKORDER AS wo
EMAXDEC1..MAXIMO.EQSTATUS AS es
The fully-qualified format is SERVER.DATABASE.OWNER.TABLE
You have left out the database name. The Schema is MAXIMO.
Try including the database name between the ..
-
The database name is Maximo. When I don't use the WORKORDER table, I connect just fine. The WORKORDER table is the issue because it's just huge in size. 217 columns across. Unfortunately, I don't own the database and only have read privileges. However, all of the data i need to pull and crunch is contained in there.
-
Sorry, I don't use Oracle, but I searched BOL with this string "ole db provider for oracle". According to BOL:
Each Oracle database instance has only one catalog with an empty name. Tables in an Oracle linked server must be referenced using a four-part name of the form OracleLinkedServerName..OwnerUserName.TableName. For example, this SELECT statement references the table SALES owned by the Oracle user MARY in the server mapped by the OrclDB linked server:
SELECT *FROM OrclDB..MARY.SALESIf I compare that to your query, I infer from that that Maximo is the owner of the table. -
Yes, that's correct. Maximo is both the database name on the EMAXDEC1 server and owner. Ok, thanks for the info--back to the drawing board.
-
Another user on this forum received the same error message as you do. The cause was a descending order index on the oracle table. Apparently, that is not supported by the MSDAORA OLE DB provider. In order to access the table, the index had to be dropped.
-
I would definitely pursue the OPENQUERY route, in order to reduce:
1. confusion about what the middleware is actually doing.
2. likelyhood of above index problem interfering.
3. excess data clogging the network and tempdb.
As I recall, Oracle has 2 dialects of SQL, plain ol' queries, and PL/SQL for programming. Dunno which SQL Plus uses - Toad could use either. It will probably be easier to get the query right in Oracle first, then slap it into:
select .... from OPENQUERY(EMAXDEC1, 'select...')
That way OLE DB doesn't need to care about the Oracle table and/or index definitions, just the column names and data types returned (which may need some conversion, esp. dates - cast as varchar in the oracle query and convert(datetime, substr(...) + substr(...)), etc., in the SQL Server select list portion.)
-
Fred--SQL Plus is the interface packaged with Oracle (at least the version we've been given to play with) and allows me to interface with the Oracle db directly.
SELECT wo.WORKTYPE, wo.WONUM, es.DOWNTIME, es.CHANGEDATE, wo.WOEQ9, wo.WOEQ2
FROM MAXIMO.WORKORDER AS wo, MAXIMO.EQSTATUS AS es
wo.WONUM=es.WONUM
WHERE (wo.WONUM=es.WONUM) AND ((es.DOWNTIME)<>0) AND ((es.CHANGEDATE)
Between '7/28/2005' And '8/25/2005') AND ((wo.WOEQ9)Like @EQ9)
AND (wo.WOEQ2)=@EQ2;
That's the query I'm trying against Oracle directly. I'm getting an error that states the SQL command isn't properly ended and references line #2--which ends up being the FROM line. Can't figure it out.
-
Line 3 in your most recent post, wo.WONUM=es.WONUM looks extraneous to me. If that is what you were actually trying, try taking line 3 out.
Do you have a local Oracle expert to help you get the Oracle query right? Once you get that right, you are 3/4 of the way home.
-
No unfortunately we contracted the design of this particular system out to a company. I actually need line 3. When i first had it in SQL Server, I had an INNER JOIN between the WORKORDER table and the EQSTATUS table. the WORKORDER table i'm "nicknaming" wo, and EQSTATUS is es. The kind of data i'm pulling is for metric reports.
In Oracle--there isn't a join keyword. So i have to remove the statment "INNER JOIN MAXIMO.EQSTATUS AS es ON wo.WONUM=es.WONUM" and stick the wo.WONUM=es.WONUM in the WHERE clause. If you can help me get it working in OPENQUERY or just working period in SQL server that would be fabulous.
I'm much more comfortable in SQL server than Oracle. Thanks
-
Like Fred said, take out extra code. You've already moved the join condition to the WHERE clause. It should read:
SELECT wo.WORKTYPE, wo.WONUM, es.DOWNTIME, es.CHANGEDATE, wo.WOEQ9, wo.WOEQ2
FROM MAXIMO.WORKORDER AS wo, MAXIMO.EQSTATUS AS es
WHERE (wo.WONUM=es.WONUM) AND ((es.DOWNTIME)<>0) AND ((es.CHANGEDATE) Between '7/28/2005' And '8/25/2005') AND ((wo.WOEQ9)Like @EQ9) AND (wo.WOEQ2)=@EQ2;
-
This is an old original post but i have a similar problem.
I also experianced this problem and also managed to get around it with openquery command however im using sql server enterprise manager and wanted to create some functions however i am unable to create functions whilst connecting in this manner and passing a parameter. I can do it in query analyzer and pass into a tempoary table but functions wont allow tempoary tables?
This is a problem of mine for some time does anyone have any suggestions?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply