YAFLogo

pittfurg
  • pittfurg
  • 56% (Neutral)
  • YAF Camper Topic Starter
13 years ago
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:

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!

View All Posts by User
Sponsor
bbobb
  • bbobb
  • 100% (Exalted)
  • YAF Developer
13 years ago
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
View All Posts by User
pittfurg
  • pittfurg
  • 56% (Neutral)
  • YAF Camper Topic Starter
13 years ago
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?
View All Posts by User
Similar Topics
Users browsing this topic