J C
asked on
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.SPLi ve.NSSQL.d bo.SalesOf fices 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!
SELECT nssql.SalesOfficeID, nssql.Name
FROM NSSQL.MyDatatabse.com.SPLi
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!
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].[SP Live].[dbo ].SalesOff ices instead, which would reduce your prefixes to the 3 limit.
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].[SP
ASKER
The alias method is not working for me. I've tried with and without brackets and RaithZ's suggestion but to no avail
Is the linked server name as I specified? if so you shouldn't need an alias.
ASKER
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.
ASKER
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.
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.
Try this:
SELECT sof.SalesOfficeID, sof.Name
FROM [NSSQL.MyDatabase.COM].[SP Live].[dbo ].SalesOff ices sof
SELECT sof.SalesOfficeID, sof.Name
FROM [NSSQL.MyDatabase.COM].[SP
ASKER
It removes the brackets and returns the error that there are too many prefixes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did it. Thanks Raith
SELECT sof.SalesOfficeID, sof.Name
FROM [NSSQL].[MyDatabase.COM].[
think you need to use [ ] as well