For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as CurrentSchoo
lName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as IsSameScho
ol
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias n
ame used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias n
ame used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony RobinsonHi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as CurrentSchool
Name
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as IsSameScho
ol
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes matter
s worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:u%23cQP0
f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||You reference Users table trice in the joins
FROM zProfile
INNER JOIN USERS as U ON zProfile.UserId = U.UserID
INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
And what is this supposed to evaluate against as it does not do a compare
against anything?
AND WHERE U.UserID = CONVERT(varchar(15), 1)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:It3gf.2741$js5.646@.tornado.rdc-kc.rr.com...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes
matters worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u%23cQP0f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId =
ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||...it's comparing it against a USERID with a value of 1:
AND WHERE U.UserID = CONVERT(varchar(15), 1)
think of it as
AND WHERE U.UserID = CONVERT(varchar(15), @.USERID)
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eEBDbNg7
FHA.3804@.TK2MSFTNGP14.phx.gbl...
You reference Users table trice in the joins
FROM zProfile
INNER JOIN USERS as U ON zProfile.UserId = U.UserID
INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
And what is this supposed to evaluate against as it does not do a compare
against anything?
AND WHERE U.UserID = CONVERT(varchar(15), 1)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:It3gf.2741$js5.646@.tornado.rdc-kc.rr.com...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes
matters worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u%23cQP0f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId =
ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinsonsql
Showing posts with label distinct. Show all posts
Showing posts with label distinct. Show all posts
Tuesday, March 27, 2012
Thursday, February 9, 2012
Affect of deleting error messages
I'm using a unique index on a table with ignore_dup_key to get around using distinct in a stored procedure. In Query analyser, when I issue the stored procedure, you get a message and also the data in the Grid. In a stored procedure call, initiated from MS Access, the message means I have to put additional client side programming to work around it. I know that duplicate keys are ignored; that is why I've created the table with this feature (unique index in combination with ignore_dup_key). So what's with the message. If I delete the Error message 3604 will I still get an error message?I'm still trying to figure out when I would ever want to do this.
What are you trying to do?
From BOL
Controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If IGNORE_DUP_KEY was specified for the index and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning and ignores the duplicate row.
If IGNORE_DUP_KEY was not specified for the index, SQL Server issues an error message and rolls back the entire INSERT statement.
The table shows when IGNORE_DUP_KEY can be used.|||I'm trying to use a server side solution to improve performance of a stored procedure. I've got a one-to many table with Street names (being the many) where the recno links back to the main table. It is possible for say King St to have a cross street of Kingsway. Hence Recno 13 for both. When I then match back to the main table I'd get multiple rows where I desire 1. To get around this I originally used the distinct clause. It works but is much slower than if I create a unique index with the ignore_dup_key option on a temp table. Problem being is the message. I've explicitly requested this action 'ignore_dup_key' so why tell me 'Duplicate key was ignored.' as if it is an error? We were force to a slower server and now my endusers are complaining about speed, that is why I wish to solve this server side instead of client side. I could write a routine to deal with the error client side but why go this route if I don't have too.|||Select Distinct
But you need to resolve this from a sql perspective...
did you read the quote...
You really should address handling MUCH more closely...you'r going to need to as well as have a general expectance of what the stored procedures are doing and what the expect...|||And that's not an error it's a warning...|||Similar to using a "set nocount" to eliminate an unwanted response to an end user. I'd expected and hoped an equivalent for eliminating an unwanted message from the use of a unique index. Yes I did read "SQL Server issues a warning and ignores the duplicate row" of your message. I wish to eliminate the message as it is unwanted. This is the contents of the message window in Query Analyzer "Server: Msg 3604, Level 16, State 1, Procedure ZZZStreet, Line 15
Duplicate key was ignored." The only way I can think of eliminating this is to delete the error message 3604 from the SQL Box. I've never done it and I am looking for guidance as to whether this will or will not fix the unwanted message from being given. I don't see any downside to this error being gone as the performance improvements should outweigh the loss of a strictly informational message. Maybe there is a downside that I'm unware of, an that's what I'm trying to figure out here.|||funny, I'm using EM here but when I create a table say Table1 with a varchar column (indexed, unique, ignore duplicate key) I am not able to insert duplicate values. From BOL it says: "Ignore duplicate key If you create a unique index, you can set this option to ensure each value in an indexed column is unique.".
Why am I confused?
What are you trying to do?
From BOL
Controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If IGNORE_DUP_KEY was specified for the index and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning and ignores the duplicate row.
If IGNORE_DUP_KEY was not specified for the index, SQL Server issues an error message and rolls back the entire INSERT statement.
The table shows when IGNORE_DUP_KEY can be used.|||I'm trying to use a server side solution to improve performance of a stored procedure. I've got a one-to many table with Street names (being the many) where the recno links back to the main table. It is possible for say King St to have a cross street of Kingsway. Hence Recno 13 for both. When I then match back to the main table I'd get multiple rows where I desire 1. To get around this I originally used the distinct clause. It works but is much slower than if I create a unique index with the ignore_dup_key option on a temp table. Problem being is the message. I've explicitly requested this action 'ignore_dup_key' so why tell me 'Duplicate key was ignored.' as if it is an error? We were force to a slower server and now my endusers are complaining about speed, that is why I wish to solve this server side instead of client side. I could write a routine to deal with the error client side but why go this route if I don't have too.|||Select Distinct
But you need to resolve this from a sql perspective...
did you read the quote...
You really should address handling MUCH more closely...you'r going to need to as well as have a general expectance of what the stored procedures are doing and what the expect...|||And that's not an error it's a warning...|||Similar to using a "set nocount" to eliminate an unwanted response to an end user. I'd expected and hoped an equivalent for eliminating an unwanted message from the use of a unique index. Yes I did read "SQL Server issues a warning and ignores the duplicate row" of your message. I wish to eliminate the message as it is unwanted. This is the contents of the message window in Query Analyzer "Server: Msg 3604, Level 16, State 1, Procedure ZZZStreet, Line 15
Duplicate key was ignored." The only way I can think of eliminating this is to delete the error message 3604 from the SQL Box. I've never done it and I am looking for guidance as to whether this will or will not fix the unwanted message from being given. I don't see any downside to this error being gone as the performance improvements should outweigh the loss of a strictly informational message. Maybe there is a downside that I'm unware of, an that's what I'm trying to figure out here.|||funny, I'm using EM here but when I create a table say Table1 with a varchar column (indexed, unique, ignore duplicate key) I am not able to insert duplicate values. From BOL it says: "Ignore duplicate key If you create a unique index, you can set this option to ensure each value in an indexed column is unique.".
Why am I confused?
Subscribe to:
Posts (Atom)