Create view linked server - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Create view linked server

Author

Topic

giszzmo
Starting Member

29 Posts

Posted - 2015-01-06 : 12:08:54

I'm trying to create a view from two linked server tables. I get an error and I can't figure out what I am missing.

Create View EmployeeData as
select * 
from openquery
(LNKSERV1, 
'select FirstName
from Records 
where FirstName (select FirstName 
from History )');

The error that I get with the openquery part says: "ORA-00936: missing expression" but I just don't know what I could be missing. The error with the create view part says: Incorrect syntax, Create View must be the only statement in the batch.
If any one could point me in the right direction. Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-06 : 12:23:43

You've got something above the create view. Add a GO to separate it into it's one batch.

GO
Create View EmployeeData as
select *
from openquery
(LNKSERV1,
'select FirstName
from Records
where FirstName = (select FirstName
from History )');
GO

You are missing an equal sign. I added it above. I would use a JOIN instead though.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

giszzmo
Starting Member

29 Posts

Posted - 2015-01-06 : 14:35:58

I would use JOIN too but I had to make it with a subquery. I added the GO before and after and the equal sign to the main query without the Create view and I get this error twice: single-row subquery returns more than one row".

giszzmo
Starting Member

29 Posts

Posted - 2015-01-06 : 14:47:06

I replaced the "=" with "IN". That seems to work.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-01-07 : 01:39:09

It's linking to an Oracle database. Have you run the code directly on the Oracle db ?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources