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

[Rx_]

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:

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

[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?

 

[kevlray]

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")?

 

[Rx_]

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

 

[kevlray]

Not really sure. Have you tried posting your question on SQLServerCentral.com?

 

[Rx_]

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.

 

[kevlray]

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.