Moved to a linked DB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Moved to a linked DB

Author

Topic

jamjam200
Starting Member

1 Post

Posted - 2010-09-15 : 15:39:51

hi,

I currently have some queries set up as views on local tables. Very soon these will move to a linked server. I have set up the linked server in enterprise manager and can access all the tables no problem. So my issue is I have some quieres that I need to convet so they look at the linked table not local tables and I have been trying for weeks to get it working with no sucess so I beg for some help.

here is one query that works with local sql tables
____
Select Resource.resourceName, Skill.SkillName,
ResourceSkillMapping.CompetenceLevel, TeamName
from Resource inner join ResourceSkillMapping on Resource.resourceSkillMapID=resourceSkillMapping.resourceSkillMapID inner join skill on ResourceSkillMapping.SkillID=Skill.skillID
inner join Team on Resource.AssignedTeamId = Team.teamID
Where Resource.Active='1'
AND Team.active='1'
AND resourceSkillMapping.active='1'
AND Skill.active=1
____

this issue seems to be when I try and access a column from the linked server for example
[linkedserver].[catalogue].[schema].[table].[column]

an error occurs saying
"contains more than the maximum number of prefixes. The maximum is 3"

do I need to re-write the whole query or is there a simple way round it- please help

my linked server is

TestSVR.db_cd.informix.[table]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-15 : 15:47:23

Show us the query that gives that error. The fix is to use an alias instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-15 : 16:13:55

I think I would create a view with a parameter

CREATE VIEW myView99
AS
SELECT *, 1 AS Source FROM localTable
UNION ALL
SELECT *, 2 AS Source FROM linkedTable
GO

SELECT * FROM myView99 WHERE Source = 1

Or

SELECT * FROM myView99 WHERE Source = 2

Or

SELECT * FROM myView99 WHERE Source IN (1,2)

MOO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources