Create a View - Linked Server - 2 methods - one won't work

Rx_

Nothing In Moderation
Local time
Yesterday, 22:33
Joined
Oct 22, 2009
Messages
2,803
Wondering if someone has an idea why one method of creating a view in SQL Server works, and the other one doesn't.

Background: A Linked Server to an Oracle DB in SQL Server provides tables to MS SQL Server Management Studio view.
Go to the Linked Server, choose a table, right-click, in menu- Script As - choose Select To New Query Window.
This SQL Select query can be executed:
Code:
SELECT top 100 [AFE_ID]
       FROM [V2.EGG.COM]..[NAV_DBA].[NV_WELL_AFE]
GO
Method One: New View
1. In Views - Create View - Past in the SQL Statement above minus the GO statement
2. Error near From clause. Note: the [V2.EGG.COM] parens go away - Add them back - error: ... contains more than the maximum number of prefixes. The Maximum is 3.
3. The attempt to execute - removes the [ ] around the [V2.EGG.COM]
So: the editor doesn't like the V2.EGG.COM
Method Two: Create Script
Code:
[SIZE=3][FONT=Times New Roman]USE [ProductionDB][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]GO[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]SET ANSI_NULLS ON[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]GO[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]use ProductionDB[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]SET QUOTED_IDENTIFIER ON[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]GO[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]CREATE VIEW [dbo].[VNav][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]AS[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]SELECT top 100 [AFE_ID][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      FROM [V2.EGG.COM]..[NAV_DBA].[NV_WELL_AFE][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]GO[/FONT][/SIZE]
This one works great!

Just wondering if there is any way to run the first method in Create View?
 
I would not think that SQL would not like the double period between the [V2.EGG.COM] and the [NAV_DBA]. The other question, is V2.EGG.COM a field name? SQL normally uses periods to qualify a name (i.e., dbo.sometable.somefield). Have you tried double quotes around the field names (i.e., "NAV_DBA")?
 
It runs perfectlly (2nd example) as shown.
This is a domain name, and if the square brackets are not around it, it will error because of too many periods (dots) in the naming convention.

Also, on the Linked Server - create SQL in new Query View - SSIS will create the SQL with this same naming convention
SELECT top 100 [AFE_ID] -- and the other field names
FROM [V2.EGG.COM]..[NAV_DBA].[NV_WELL_AFE]

It is just that trying to paste the same SQL statement created from the Linked Server table into a New View - (1st example) won't compile.
The SQL compiler removes the square brackets around the V2.EGG.COM
 
Not really sure. Have you tried posting your question on SQLServerCentral.com?
 
LOL, yes in the Newbie section.
They just said, "try this". I did, it worked, but don't know why the other one won't work.
 
SQL is funny about that. I have my own copy of SQL Server 2008 and a common way of adding values into a table using TSQL gives me a syntax error and it should not.
 

Users who are viewing this thread

Back
Top Bottom