Solved

Trying to create a view querying against a linked server

Posted on 2013-11-06
10
542 Views
Last Modified: 2013-11-07
I receive an error that the maximum prefix is 3 when I try to execute the query below

SELECT     nssql.SalesOfficeID, nssql.Name
FROM        NSSQL.MyDatatabse.com.SPLive.NSSQL.dbo.SalesOffices AS nssql


I am trying to use the alias method to work around the prefix limitation. I didn't add the AS statement, Can someone tell me what I am doing wrong?

Thanks!
0
Comment
Question by:J C
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39628865
try without repeating "nssql"

SELECT     sof.SalesOfficeID, sof.Name
FROM         [NSSQL].[MyDatabase.COM].[SPLive].[dbo].SalesOffices AS sof

think you need to use [ ] as well
0
 
LVL 6

Expert Comment

by:RaithZ
ID: 39628882
You might also be able to shorten it if the default schema for the user being used is dbo, then you might be able to get away with not having .dbo. in the query.  

I am guessing that NSSQL.MyDatabase.COM is the name of the linked server, in which case your brackets would look like  [NSSQL.MyDatabase.COM].[SPLive].[dbo].SalesOffices instead, which would reduce your prefixes to the 3 limit.
0
 

Author Comment

by:J C
ID: 39628891
The alias method is not working for me. I've tried with and without brackets and RaithZ's suggestion but to no avail
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 6

Expert Comment

by:RaithZ
ID: 39628897
Is the linked server name as I specified?  if so you shouldn't need an alias.
0
 

Author Comment

by:J C
ID: 39628916
The schema {dbo} is needed it appears which makes 4 prefixes. I've seen where the alias method works for others, I wonder why it isn't working for me. It's as if the alias is being ignored entirely.
0
 

Author Comment

by:J C
ID: 39628931
Maybe it's how I am using it that is the problem. I am trying to create a view inside of a database that resides on my localized SQL server. I am trying to use the syntax above to pull in some info from the linked server. Maybe that isn't supported?

I can use the alias method if I create a new query but when I try to execute this inside of a view I receive the errors.
0
 
LVL 6

Expert Comment

by:RaithZ
ID: 39628942
Try this:

SELECT     sof.SalesOfficeID, sof.Name
FROM         [NSSQL.MyDatabase.COM].[SPLive].[dbo].SalesOffices  sof
0
 

Author Comment

by:J C
ID: 39628978
It removes the brackets and returns the error that there are too many prefixes.
0
 
LVL 6

Accepted Solution

by:
RaithZ earned 500 total points
ID: 39628983
Then I would suggest going into your linked server, setting the fully qualified address in the datasource field.. and shortening the name of the linked server to something without period.  

Instructions here:
http://alexpinsker.blogspot.com/2007/08/how-to-give-alias-to-sql-linked-server.html
0
 

Author Comment

by:J C
ID: 39629000
That did it. Thanks Raith
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question