openquery error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

openquery error

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-10-14 : 10:59:12

I am trying to query the f0006 table in symprod
for mcrp25 records which lie in another table d_businessunits...
The code right at the bottom generates the error message:

OLE DB provider "OraOLEDB.Oracle" for linked server "symprod_etldev" returned message "ORA-00907: missing right parenthesis".
Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "select * from proddta.f0006 where
mcrp25 in (select businessunitid from biprod.dbo.d_businessunits) " for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "symprod_etldev".

select * from openquery(symprod_etldev,'select * from proddta.f0006 where
mcrp25 in (select businessunitid from biprod.dbo.d_businessunits where sblid = 'OPE') ')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 11:02:34

try


select * from openquery(symprod_etldev,'select * from proddta.f0006 where 
mcrp25 in (select businessunitid from biprod.dbo.d_businessunits where sblid = ''OPE'') ')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tariq2
Posting Yak Master

125 Posts

Posted - 2011-10-14 : 11:15:22

Hi Visakh, I run that and receive the below:

OLE DB provider "OraOLEDB.Oracle" for linked server "symprod_etldev" returned message "ORA-00907: missing right parenthesis".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select * from proddta.f0006 where
mcrp25 in (select businessunitid from biprod.dbo.d_businessunits where sblid = 'OPE') " for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "symprod_etldev".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 11:25:44

where's the table biprod.dbo.d_businessunits? is it in sql server or in linked oracle server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tariq2
Posting Yak Master

125 Posts

Posted - 2011-10-14 : 11:43:05

Hi Visakh,

Its in sql server

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 11:47:25

then it should be outside


select * from openquery(symprod_etldev,'select * from proddta.f0006') t
where mcrp25 in (select businessunitid from biprod.dbo.d_businessunits where sblid = 'OPE')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tariq2
Posting Yak Master

125 Posts

Posted - 2011-10-14 : 11:59:57

Thank you Visakh

works a treat :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 12:02:05

welcome