Create View from Linked Server with more than 4 part naming

[Rx_]

This is a solution (or work-around) for creating a view from a SQL Server Linked Table. Is this the only solution?
If someone has a solution to using the SSMS Create View so it can handle a FROM with more than 3 dots, please let me know.

My preference in SQL Server Management Studio (SSMS) would be to use the query grid to create the view. Many of the Oracle datatypes need to be Cast.
Example: the text 34 needs to be Cast into a integer 34

As an Access user, this is much easier to accomplish in the Create View grid rather than a TSQL Statement.

1. A SQLServer Linked Server to Oracle using ODBC was created and available.
- Under the Object Explorer Linked Server Name - expand Views. Find the desired view.
- Right Click - Script View as - Select To this generates the Select statement
2. In the production database, go to Object Explorer Views - Right Click - New View
- in the SQL Payne past in the SQL statement generated from step 1
- Click exclamation toolbar button to Run.
- Error: Invalid Object Name
[DAT_DIV_3.CoName.COM].INFO.TheINFO_VW
This has more than 3 dots
Changing the SQL to [DAT_DIV_3.CoName.COM].[INFO].[TheINFO_VW]
will fail too, and the [ ] will be removed

The field names do appear in the design grid, with table name <Unknown>
Use the design grid to Cast data types AS a field name
e.g. [Cast(START_OPR_DATE as date) as SPUD_DT]
Note: the SQL view below will update as the grid design is updated.
However, with no Table Name - the SQL will need to be modified so the square brackets on each end must be removed. Basically, a CAST or AS adds things to the field's SQL that must be removed - Example:
e.g. SQL generated needs to be modified to this
[WELL_ID as CAST(WELL_ID AS INT) AS WELL_ID] <generated in grid>
CAST(WELL_ID AS INT) AS WELL_ID <Fixed to run in Script>
Click on New Query
Paste in the following to CREATE VIEW

USE [RegulatoryDB]
GO
/****** Object:  create View from Linked Server  [DAT_DIV_3.CoName.COM].INFO.TheINFO_VW   Script Date: 05/33/2015 4:15AM 
Rename CREATE VIEW string
Copy SQL Statement from altered view of Linked Server into SELECT through FROM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[EnterViewName]
AS
<PASTE SQL OVER THIS including SELECT - FROM - WHERE>
-- Here is the CATCH!  Note the two " .. "  change in the FROM address
--  [DAT_DIV_3.CoName.COM]..INFO.TheINFO_VW
-- The line above will not work in the Create View query view
-- But, it will work in the script
GO

Be sure to add the extra dot between ].INFO.
Use the Check SQL (check button) to see that it will work.
Run the SQL statement to Create a new View.

My new view is now consumable from Access as a Linked Table.
The source is Oracle in a remote location.

This concludes the steps.

Basically, this was the steps because the Linked Server's View - create SQL could not be put directly into the SQL Server 2008R2 create View tool. It just won't handle the FROM statement with more than 4 dots, regardless of how the square brackets are arranged.

I have a dozen more of these to do. Please share if there is a better way to accomplish this.

 

[SQL_Hell]

Hi there,

In your post above you mention the 3 dots, but for a linked table it should be a four part reference.

linkedservername.databasename.schemaname.tablename

I am just wondering if that is your issue?

Have you tried:

[DAT_DIV_3.CoName.COM].[INFO].[dbo].[TheINFO_VW]

 

[Rx_]

Thanks for that suggestion.
Yes. The SSMS Create View just removes the brackets.
Just tried it again and got the old error:
The sQL Execution Error from Microsoft Server Management Studio in the Create View (grid, sql view) is:
The (path used) contains more than the maximum number of prefixes. The maximum is 3

Last year, I posted the question on the Newbie section of SQL Server Central. They basically told me to go write a script (like the one above).

Searching many sites, a few creditable sources suggest creating an alias for the Shared Server. https://social.msdn.microsoft.com/F...with-a-fqdn-linked-server?forum=sqlgetstarted

The bottom line is that many of the Oracle numbers need to be Cast anyway.
I think your right when running my script to Create View, the second dot probably implies DBO. But, in the SQL Server Management Studio Create View, the double dot or adding .DBO. just returns the error and removes the brackets.

Guess that is Microsoft trying to help me?