March 30, 2009 at 3:51 pm
I installed the oracle client, set up the linked server with the remote ID/PWD. I can see the tables but cannot query them without useing "openquery"
example:
select * from openquery(commerceora, 'select * from users') WORKS
but
select * from commerceora...users DOES NOT WORK
error:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'commerceora' does not contain table 'users'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='commerceora', TableName='users'].
select * from commerceora...cleardb.users DOES NOT WORK
error
OLE DB provider 'commerceora' does not contain table '"cleardb"."users"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='clearcommerceora', TableName='"cleardb"."users"'].
Can anyone help?
March 30, 2009 at 5:23 pm
I sort of figured it out. Seems that the oracle instance is case sensitive and requiring the table names and schema name to be in UPPER CASE (but only when using the provider directly, not when using openquery.
Weird. I'm not an oracle fan lately .;-)
March 30, 2009 at 11:44 pm
Hi ,
This is actually a bug in MS SQL Server 2000. When you run a four-part name query on a table in an Oracle data source, if the table name contains lowercase characters, you will receive an error message.Works fine for table with all uppercase letters.
You may have to go with the latest Service Pack installation. For more details check the below link:
http://support.microsoft.com/kb/294459
Thanks,
Have a nice day !!!
----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **
March 31, 2009 at 9:26 am
Hmmm, the server is sp4. The article says sp1 fixes it. So it should be fixed. I've also got to install on a cluster and I have no cluster test environment (our test environment -- where it is now) is not clustered. I think this may need a call to Microsoft.
Unless anyone else knows? I've been looking through the forums and many of these send to end with questions still unanswered.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply