前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >sqlserver 2000/2005 Ambiguous column error错误解决办法

sqlserver 2000/2005 Ambiguous column error错误解决办法

作者头像
张善友
发布2018-01-19 15:06:59
6550
发布2018-01-19 15:06:59
举报
文章被收录于专栏:张善友的专栏

今天针对Sql server 2005的脚本在Sql server 2000上执行,发生了两个错误

Msg 209, Level 16, State 1, Procedure tbh_Forums_GetUnapprovedPosts, Line 13 Ambiguous column name 'AddedDate'. Msg 209, Level 16, State 1, Procedure tbh_Forums_GetThreadByID, Line 13 Ambiguous column name 'AddedDate'.

原来就是Forums表中和Post表中具有相同的列AddedDate,通过as 方式改写就可以解决了

代码语言:js
复制
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbh_Forums_GetThreadByID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[tbh_Forums_GetThreadByID]
(
   @ThreadPostID  int
)
AS
SET NOCOUNT ON
 
SELECT tbh_Posts.PostID, tbh_Posts.AddedDate as AddedDate, tbh_Posts.AddedBy, tbh_Posts.AddedByIP, tbh_Posts.ForumID, tbh_Posts.ParentPostID, tbh_Posts.Title, tbh_Posts.Body, tbh_Posts.Approved, tbh_Posts.Closed, tbh_Posts.ViewCount, tbh_Posts.ReplyCount, tbh_Posts.LastPostDate, tbh_Posts.LastPostBy,
   tbh_Forums.Title AS ForumTitle
   FROM tbh_Posts INNER JOIN
      tbh_Forums ON tbh_Posts.ForumID = tbh_Forums.ForumID
   WHERE PostID = @ThreadPostID OR ParentPostID = @ThreadPostID
   ORDER BY AddedDate ASC' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbh_Forums_GetUnapprovedPosts]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[tbh_Forums_GetUnapprovedPosts]
AS
SET NOCOUNT ON
SELECT tbh_Posts.PostID, tbh_Posts.AddedDate  as AddedDate, tbh_Posts.AddedBy, tbh_Posts.AddedByIP, tbh_Posts.ForumID, tbh_Posts.ParentPostID, tbh_Posts.Title, tbh_Posts.Approved, tbh_Posts.Closed, tbh_Posts.ViewCount, tbh_Posts.ReplyCount, tbh_Posts.LastPostDate, tbh_Posts.LastPostBy,
   tbh_Forums.Title AS ForumTitle, CASE
                                    WHEN ParentPostID = 0 THEN 1
                                    ELSE 0
                                 END AS IsThreadPost
   FROM tbh_Posts INNER JOIN
      tbh_Forums ON tbh_Posts.ForumID = tbh_Forums.ForumID
   WHERE Approved = 0
   ORDER BY IsThreadPost DESC, AddedDate ASC' 
END
GO

虽然这样解决了问题,这不应该是问题的真正原因,各位同学谁知道这个问题的真正原因?

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2006-08-22 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档