SQL Server OpenQuery to Oracle schema with $

0
votes

I am trying to connect from SQL Server 2008 R2 into an Oracle Linked Server, but the Oracle Schema has a $ symbol in it. Unfortunately, I can't change the schema name, but it appears to be causing me an issue when trying to extract information using OpenQuery. Is it possible to escape this character?

select * 
FROM OPENQUERY(linksrv, 'SELECT * FROM [ABC$SCHEMA].[TABLE] where [ID] = ''ABCD0001'' ') oq

The above gives me an error message as follows:

OLE DB provider "OraOLEDB.Oracle" for linked server "linksrv" returned message "ORA-00903: invalid table name".

The below query works, but it is painfully slow on something that takes half a second to run on the Oracle database so I am hoping OPENQUERY might give me results slightly faster?

SELECT *
  FROM [linksrv]..[ABC$SCHEMA].[TABLE]
  where [ID] = 'ABCD0001'
GO

Version information:
Microsoft SQL Server Management Studio      10.50.2500.0
Microsoft Analysis Services Client Tools    10.50.2500.0
Microsoft Data Access Components (MDAC)     6.1.7601.17514
Microsoft MSXML             3.0 6.0 
Microsoft Internet Explorer         9.0.8112.16421
Microsoft .NET Framework            2.0.50727.5456
Operating System                6.1.7601

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

Any assistance would be greatly appreciated!!

Thanks in advance!!

1
sql-serveroracleschemalinked-serveropenquery
What happens if you run SELECT * FROM [ABC$SCHEMA].[TABLE] in Oracle? Is it possible some other delimiter / escape mechanism is required there? The error seems to come from Oracle, not SQL Server, or at least from the OLEDB provider. You may consider trying a different Oracle driver/provider for your linked server if any compatible ones exist. – Aaron Bertrand
Thanks Aaron - you pointed me in the right direction. I've come from an Oracle side and am not used to all these [ and ] around everything, but the error was replicated when I tried to run that query directly in Oracle, as you would expect. I thought the statement would be translated by SQL Server before it was sent, but it appears not. The following statement does work: select * FROM OPENQUERY(linksrv, 'SELECT * FROM ABC$SCHEMA.TABLE where ID = ''ABCD0001'' ') oq Even better - it is lightning fast instead of querying direct off the linked server for some reason!! Thanks for your help! – G-Man

1 Answers

0
votes

Based on the comments it appears that the syntax should be different for Oracle, where square brackets actually cause problems rather than prevent them:

SELECT * 
FROM OPENQUERY(linksrv, 'SELECT * FROM ABC$SCHEMA.TABLE ...') AS oq;