I have checked all my stored procedures and they all seem correct to the upgrade scripts. The reason for the ALTER was I used the Modify function in SQL Studio to paste the procedure into this forum. This always creates the script with ALTER.
I am still having the problem though.
I think I might have tracked it down to the Addressed column in the table Forum_Posts not accepting null. If you look at the stored procedue Forum_Post_Add it does not insert any value into the column Addressed so wil create a null value which is not allowed. Would someone be able to tell me whether the procedure is wrong or the Addressed column should accept null.
CREATE PROCEDURE {databaseOwner}[{objectQualifier}Forum_Post_Add]
@ParentPostID INT,
@ForumID INT,
@UserID INT,
@RemoteAddr NVARCHAR(100),
@Subject NVARCHAR(255),
@Body NVARCHAR(MAX),
@IsPinned BIT,
@PinnedDate DATETIME,
@IsClosed BIT,
@PortalID INT,
@PollID INT,
@IsModerated BIT,
@ParseInfo INT
AS
DECLARE @PostID INT
DECLARE @ThreadID INT
DECLARE @CreatedDate DATETIME
DECLARE @ModeratedForum BIT
DECLARE @GroupID INT
DECLARE @DateApproved DATETIME
DECLARE @IsApproved BIT
SELECT @CreatedDate = GETDATE()
IF @IsModerated = 0
BEGIN
SET @IsApproved = 1
SET @DateApproved = GETDATE()
END
ELSE
BEGIN
SET @IsApproved = 0
END
IF @ParentPostID = 0 -- New Thread
BEGIN
INSERT {databaseOwner}{objectQualifier}Forum_Posts
(
ParentPostID,
UserID,
RemoteAddr,
[Subject],
Body,
CreatedDate,
UpdatedDate,
IsApproved,
IsClosed,
DateApproved,
ParseInfo,
PostReported
)
VALUES (
@ParentPostID,
@UserID,
@RemoteAddr,
@Subject,
@Body,
@CreatedDate,
@CreatedDate,
@IsApproved,
@IsClosed,
@DateApproved,
@ParseInfo,
0
)
SET @PostID = SCOPE_IDENTITY()
SET @ThreadID = @PostID
EXEC {databaseOwner}{objectQualifier}Forum_Thread_Add @ThreadID, @ForumID, @IsPinned,
@PinnedDate, @PollID
UPDATE {databaseOwner}{objectQualifier}Forum_Posts
SET ThreadID = @ThreadID
WHERE PostID = @PostID
EXEC {databaseOwner}{objectQualifier}Forum_UserThreadsAdd @UserID, @ThreadID, @CreatedDate
END
ELSE -- Reply
BEGIN
SET @ThreadID = ( SELECT ThreadID
FROM {databaseOwner}{objectQualifier}Forum_Posts
WHERE PostID = @ParentPostID
)
INSERT {databaseOwner}{objectQualifier}Forum_Posts
(
ParentPostID,
UserID,
RemoteAddr,
[Subject],
Body,
CreatedDate,
ThreadID,
UpdatedDate,
IsApproved,
IsClosed,
DateApproved,
ParseInfo,
PostReported
)
VALUES (
@ParentPostID,
@UserID,
@RemoteAddr,
@Subject,
@Body,
@CreatedDate,
@ThreadID,
@CreatedDate,
@IsApproved,
@IsClosed,
@DateApproved,
@ParseInfo,
0
)
SELECT @PostID = @@IDENTITY
IF @IsModerated = 0
BEGIN
EXEC {databaseOwner}{objectQualifier}Forum_AA_ThreadUpdate @ThreadID, @PostID,
@IsPinned, @PinnedDate, 'postadd', @PollID
END
END
IF @IsModerated = 0
BEGIN
EXEC {databaseOwner}{objectQualifier}Forum_AA_UserPostCountUpdate @UserID, @PortalID
EXEC {databaseOwner}{objectQualifier}Forum_Forum_PostAdded @ForumID, @ThreadID, @PostID,
@UserID, 'add'
END
ELSE
BEGIN
EXEC {databaseOwner}{objectQualifier}Forum_Forum_PostAdded @ForumID, @ThreadID, @PostID,
@UserID, 'submit'
END
SELECT @PostID
GO
|