pittfurg
  •  pittfurg
  • 56% (Neutral)
  • YAF Camper Topic Starter
2012-02-07T03:08:52Z
Hi everyone,

First, thanks to all who helped me with getting our legacy user administration system ported over to YAF. That part worked great in our integration testing.

I am now installing the application on our production server, but I've run into a strange error here. I am able to copy the files and folders to our server, and I can even get the install pages to load and process, but the script is failing mid-way through the installation, with the following error page:

Quote:

FILE:
mssql/procedures.sql

ERROR:
ORDER BY items must appear in the select list if the statement contains a UNION operator.

STATEMENT:
create procedure [dbo].[yaf_forum_moderators] (@StyledNicks bit) as
BEGIN
select
ForumID = a.ForumID,
ModeratorID = a.GroupID,
ModeratorName = b.Name,
Style = '',
IsGroup=1
from
[dbo].[yaf_ForumAccess] a WITH(NOLOCK)
INNER JOIN [dbo].[yaf_Group] b WITH(NOLOCK) ON b.GroupID = a.GroupID
INNER JOIN [dbo].[yaf_AccessMask] c WITH(NOLOCK) ON c.AccessMaskID = a.AccessMaskID
where
(b.Flags & 1)=0 and
(c.Flags & 64)<>0
union all
select
ForumID = access.ForumID,
ModeratorID = usr.UserID,
ModeratorName = usr.Name,
Style = case(@StyledNicks)
when 1 then ISNULL((SELECT TOP 1 f.Style FROM [dbo].[yaf_UserGroup] e
join [dbo].[yaf_Group] f on f.GroupID=e.GroupID WHERE e.UserID=usr.UserID AND LEN(f.Style) > 2 ORDER BY f.SortOrder),
r.Style)
else '' end,
IsGroup=0
from
[dbo].[yaf_User] usr WITH(NOLOCK)
INNER JOIN (
select
UserID = a.UserID,
ForumID = x.ForumID,
ModeratorAccess = MAX(ModeratorAccess)
from
[dbo].[yaf_vaccessfull] as x WITH(NOLOCK)
INNER JOIN [dbo].[yaf_UserGroup] a WITH(NOLOCK) on a.UserID=x.UserID
INNER JOIN [dbo].[yaf_Group] b WITH(NOLOCK) on b.GroupID=a.GroupID
WHERE
ModeratorAccess <> 0 AND x.AdminGroup = 0
GROUP BY a.UserId, x.ForumID
) access ON usr.UserID = access.UserID
JOIN [dbo].[yaf_Rank] r
ON r.RankID = usr.UserID
where
access.ModeratorAccess<>0
order by
IsGroup desc,
ModeratorName asc
END
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: FILE:
mssql/procedures.sql

ERROR:
ORDER BY items must appear in the select list if the statement contains a UNION operator.

STATEMENT:
create procedure [dbo].[yaf_forum_moderators] (@StyledNicks bit) as
BEGIN
select
ForumID = a.ForumID,
ModeratorID = a.GroupID,
ModeratorName = b.Name,
Style = '',
IsGroup=1
from
[dbo].[yaf_ForumAccess] a WITH(NOLOCK)
INNER JOIN [dbo].[yaf_Group] b WITH(NOLOCK) ON b.GroupID = a.GroupID
INNER JOIN [dbo].[yaf_AccessMask] c WITH(NOLOCK) ON c.AccessMaskID = a.AccessMaskID
where
(b.Flags & 1)=0 and
(c.Flags & 64)<>0
union all
select
ForumID = access.ForumID,
ModeratorID = usr.UserID,
ModeratorName = usr.Name,
Style = case(@StyledNicks)
when 1 then ISNULL((SELECT TOP 1 f.Style FROM [dbo].[yaf_UserGroup] e
join [dbo].[yaf_Group] f on f.GroupID=e.GroupID WHERE e.UserID=usr.UserID AND LEN(f.Style) > 2 ORDER BY f.SortOrder),
r.Style)
else '' end,
IsGroup=0
from
[dbo].[yaf_User] usr WITH(NOLOCK)
INNER JOIN (
select
UserID = a.UserID,
ForumID = x.ForumID,
ModeratorAccess = MAX(ModeratorAccess)
from
[dbo].[yaf_vaccessfull] as x WITH(NOLOCK)
INNER JOIN [dbo].[yaf_UserGroup] a WITH(NOLOCK) on a.UserID=x.UserID
INNER JOIN [dbo].[yaf_Group] b WITH(NOLOCK) on b.GroupID=a.GroupID
WHERE
ModeratorAccess <> 0 AND x.AdminGroup = 0
GROUP BY a.UserId, x.ForumID
) access ON usr.UserID = access.UserID
JOIN [dbo].[yaf_Rank] r
ON r.RankID = usr.UserID
where
access.ModeratorAccess<>0
order by
IsGroup desc,
ModeratorName asc
END

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[Exception: FILE:
mssql/procedures.sql

ERROR:
ORDER BY items must appear in the select list if the statement contains a UNION operator.

STATEMENT:
create procedure [dbo].[yaf_forum_moderators] (@StyledNicks bit) as
BEGIN
select
ForumID = a.ForumID,
ModeratorID = a.GroupID,
ModeratorName = b.Name,
Style = '',
IsGroup=1
from
[dbo].[yaf_ForumAccess] a WITH(NOLOCK)
INNER JOIN [dbo].[yaf_Group] b WITH(NOLOCK) ON b.GroupID = a.GroupID
INNER JOIN [dbo].[yaf_AccessMask] c WITH(NOLOCK) ON c.AccessMaskID = a.AccessMaskID
where
(b.Flags & 1)=0 and
(c.Flags & 64)<>0
union all
select
ForumID = access.ForumID,
ModeratorID = usr.UserID,
ModeratorName = usr.Name,
Style = case(@StyledNicks)
when 1 then ISNULL((SELECT TOP 1 f.Style FROM [dbo].[yaf_UserGroup] e
join [dbo].[yaf_Group] f on f.GroupID=e.GroupID WHERE e.UserID=usr.UserID AND LEN(f.Style) > 2 ORDER BY f.SortOrder),
r.Style)
else '' end,
IsGroup=0
from
[dbo].[yaf_User] usr WITH(NOLOCK)
INNER JOIN (
select
UserID = a.UserID,
ForumID = x.ForumID,
ModeratorAccess = MAX(ModeratorAccess)
from
[dbo].[yaf_vaccessfull] as x WITH(NOLOCK)
INNER JOIN [dbo].[yaf_UserGroup] a WITH(NOLOCK) on a.UserID=x.UserID
INNER JOIN [dbo].[yaf_Group] b WITH(NOLOCK) on b.GroupID=a.GroupID
WHERE
ModeratorAccess <> 0 AND x.AdminGroup = 0
GROUP BY a.UserId, x.ForumID
) access ON usr.UserID = access.UserID
JOIN [dbo].[yaf_Rank] r
ON r.RankID = usr.UserID
where
access.ModeratorAccess<>0
order by
IsGroup desc,
ModeratorName asc
END]
YAF.Classes.Data.LegacyDb.system_initialize_executescripts(String script, String scriptFile, Boolean useTransactions) +634
YAF.Install._default.ExecuteScript(String scriptFile, Boolean useTransactions) +243
YAF.Install._default.UpgradeDatabase(Boolean fullText) +79
YAF.Install._default.Wizard_NextButtonClick(Object sender, WizardNavigationEventArgs e) +989
System.Web.UI.WebControls.Wizard.OnNextButtonClick(WizardNavigationEventArgs e) +108
System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +418
System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +19
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +118
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +166
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565


Version Information: Microsoft .NET Framework Version:2.0.50727.4963; ASP.NET Version:2.0.50727.4971



The server is running SQL 2005 and IIS 7. I didn't run into this on my testing server. Any ideas what would cause this?

Thanks!
Sponsor
bbobb
  •  bbobb
  • 100% (Exalted)
  • YAF Developer
2012-02-07T11:50:34Z
Can't be reproduced, so no help is possible. If you can compile ypur version just split union and make it in C# code.


P.S. You have an error , should be
JOIN [{databaseOwner}].[{objectQualifier}Rank] r
		ON r.RankID = usr.RankID
where
access.ModeratorAccess<>0
order by
IsGroup desc,
ModeratorName asc

pittfurg
  •  pittfurg
  • 56% (Neutral)
  • YAF Camper Topic Starter
2012-02-08T22:05:05Z
But I didn't change anything here: this is using the set-up/installation pages for the first time. That file referenced is one it's trying to run. I never made any changes to it... how can I fix this?

About Us

The YAF.NET is an open source .NET forum project. YAF.NET is supported by an team of international developers who are build community by building community software.

Powered by Resharper Donate with PayPal button

Project Twitter Updates

Copyright © YetAnotherForum.NET & Ingo Herbote. All rights reserved