• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 610
  • Last Modified:

Trying to create a view querying against a linked server

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
J C
Asked:
J C
  • 5
  • 4
1 Solution
 
PortletPaulfreelancerCommented:
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
 
RaithZCommented:
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
 
J CAuthor Commented:
The alias method is not working for me. I've tried with and without brackets and RaithZ's suggestion but to no avail
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
RaithZCommented:
Is the linked server name as I specified?  if so you shouldn't need an alias.
0
 
J CAuthor Commented:
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
 
J CAuthor Commented:
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
 
RaithZCommented:
Try this:

SELECT     sof.SalesOfficeID, sof.Name
FROM         [NSSQL.MyDatabase.COM].[SPLive].[dbo].SalesOffices  sof
0
 
J CAuthor Commented:
It removes the brackets and returns the error that there are too many prefixes.
0
 
RaithZCommented:
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
 
J CAuthor Commented:
That did it. Thanks Raith
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now