DTS-table name or alias name used in the query 1

[Jozopiso]

Hi, this is my trouble...For me, this code is perfect, and actually it was yesterday, but now it shows me message:"the column prefix 'ID_Rank'does not match with a table name or alias name used in the query " Why? how can i solve it?

CREATE PROCEDURE dbo.PersonSkillView1
as

if exists (select * from sysobjects where id = object_id(N'PersonSkillView')
and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [PersonSkillView]


DECLARE @SQLString NVARCHAR(500)

SET @SQLString ='CREATE VIEW PersonSkillView
AS
SELECT TOP 100 PERCENT ps.ID_PersonSkill, ps.ID_Person, ps.ID_Skill, ps.ID_ProficiencyAcc,
ps.ID_UseType, ps.ID_Experience, ps.ID_YearLastUsed, ps.ID_Activity,
skill.ID_SkillCategory, skill.SkillCode, skill.SkillName, skill.DefaultUseType,
ID_Rank.ID_Proficiency, ID_Rank.InternalRank, ID_Rank.ID_Rank, ID_Rank.[Level],
ID_Rank.RankCode, ID_Rank.Rank
FROM PersonSkill ps INNER JOIN
ID_Skill skill ON skill.ID_Skill = ps.ID_Skill INNER JOIN
ID_SkillCategory sc ON sc.ID_SkillCategory = skill.ID_SkillCategory INNER JOIN
ID_SkillArea sa ON sa.ID_SkillArea = sc.ID_SkillArea INNER JOIN
ID_SkillProficiency ON ps.ID_Proficiency = ID_SkillProficiency.ID_Proficiency INNER JOIN
ID_Rank ON ID_SkillProficiency.ID_Proficiency = ID_Rank.ID_Proficiency
ORDER BY ps.ID_Person'

EXECUTE sp_executesql @SQLString


COMMIT
GO

 

[Jozopiso]

I'm sorry, i solved my problem :0). Troble was with declaring @SQLString NVARCHAR(500)...i just increased number to 1500 and everything is ok. DECLARE @SQLString NVARCHAR(1500)....I'm not good in this, can anyone explain me why it works now?

Thanx and really appreciate

 

[dbomrrsm]

when you had @SQLString declared as 500 it mena that only the first 500 charachters of the string could be held so it was truncating your statement to the point where it was incomplete your statement would have therefore looked something like:

CREATE VIEW PersonSkillView
    AS
    SELECT TOP 100 PERCENT ps.ID_PersonSkill, ps.ID_Person, ps.ID_Skill, ps.ID_ProficiencyAcc,
                 ps.ID_UseType, ps.ID_Experience, ps.ID_YearLastUsed, ps.ID_Activity, 
                skill.ID_SkillCategory, skill.SkillCode, skill.SkillName, skill.DefaultUseType, 
                ID_Rank.ID_Proficiency, ID_Rank.InternalRank, ID_Rank.ID_Rank, ID_Rank.[Level], 
                ID_Rank.RankCode, ID_Rank.Rank
    FROM

so when this was parsed you got the error message !!

[blue]DBomrrsm[/blue]

 

[Jozopiso]

Dear DBomrrsm, I knew that was reason of my troubles. Thanx very much. But the error messages was absolutelly different that i was expecting.

 

[dbomrrsm]

The message was cut short by the limit of (500) before the ID_Rank was stated in the FROM clause - so you had things in the select that were prefixed by ID_Rank such as ID_Rank.Rank

so it was saying that

'ID_Rank'does not match with a table name or alias name used in the query'

because ID_Rank table was not in the from clause.



[blue]DBomrrsm[/blue]