 Rank: YAF ForumlingJoined: 2/7/2018(UTC) Posts: 5  Location: London
|
Hi, I'm having trouble upgrading the database as part of the latest version upgrade. Here's the error I get -- Code:Server Error in '/' Application.
FILE:
mssql/upgrade/tables.sql
ERROR:
The procedure 'sys.sp_fulltext_column' cannot be executed within a transaction.
Cannot alter or drop column 'Message' because it is enabled for Full-Text Search.
STATEMENT:
if exists (select top 1 1 from sys.columns where object_id = object_id('[dbo].[yaf_Message]') and name = 'Message' and system_type_id = 99
and exists(select * from sys.sysfulltextcatalogs where name = N'YafSearch'))
begin
exec sp_fulltext_column N'[dbo].[yaf_Message]', N'Message', N'drop'
alter table [dbo].[yaf_Message] alter column [Message] nvarchar(max)
exec sp_fulltext_column N'[dbo].[yaf_Message]', N'Message', N'add'
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/upgrade/tables.sql
ERROR:
The procedure 'sys.sp_fulltext_column' cannot be executed within a transaction.
Cannot alter or drop column 'Message' because it is enabled for Full-Text Search.
STATEMENT:
if exists (select top 1 1 from sys.columns where object_id = object_id('[dbo].[yaf_Message]') and name = 'Message' and system_type_id = 99
and exists(select * from sys.sysfulltextcatalogs where name = N'YafSearch'))
begin
exec sp_fulltext_column N'[dbo].[yaf_Message]', N'Message', N'drop'
alter table [dbo].[yaf_Message] alter column [Message] nvarchar(max)
exec sp_fulltext_column N'[dbo].[yaf_Message]', N'Message', N'add'
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/upgrade/tables.sql
ERROR:
The procedure 'sys.sp_fulltext_column' cannot be executed within a transaction.
Cannot alter or drop column 'Message' because it is enabled for Full-Text Search.
STATEMENT:
if exists (select top 1 1 from sys.columns where object_id = object_id('[dbo].[yaf_Message]') and name = 'Message' and system_type_id = 99
and exists(select * from sys.sysfulltextcatalogs where name = N'YafSearch'))
begin
exec sp_fulltext_column N'[dbo].[yaf_Message]', N'Message', N'drop'
alter table [dbo].[yaf_Message] alter column [Message] nvarchar(max)
exec sp_fulltext_column N'[dbo].[yaf_Message]', N'Message', N'add'
end]
YAF.Classes.Data.LegacyDb.system_initialize_executescripts(String script, String scriptFile, Boolean useTransactions) +1475
YAF.Core.Services.InstallUpgradeService.ExecuteScript(String scriptFile, Boolean useTransactions) +172
YAF.Types.Extensions.EnumerableExtensions.ForEach(IEnumerable`1 list, Action`1 action) +232
YAF.Core.Services.InstallUpgradeService.UpgradeDatabase(Boolean fullText, Boolean upgradeExtensions) +145
YAF.Install._default.Wizard_NextButtonClick(Object sender, WizardNavigationEventArgs e) +1554
System.Web.UI.WebControls.Wizard.OnNextButtonClick(WizardNavigationEventArgs e) +134
System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +491
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +50
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5062
-- Is there anything I can do at my end to fix this - for example something I could change in my SQL server settings that would help make this work? Thanks, Will
|
|
|
|
|
|
 Rank: YAF.NET Project LeadJoined: 3/6/2010(UTC) Posts: 3,816  Thanks: 70 times
Was thanked: 1173 time(s) in 1003 post(s)
|
What happens when you go to Admin > Database > Run SQL Query Code:if exists (select top 1 1 from sys.columns where object_id = object_id('[{databaseOwner}].[{objectQualifier}Message]') and name = 'Message' and system_type_id = 99
and exists(select * from sys.sysfulltextcatalogs where name = N'YafSearch'))
begin
exec sp_fulltext_column N'[{databaseOwner}].[{objectQualifier}Message]', N'Message', N'drop'
end
if exists (select top 1 1 from sys.columns where object_id = object_id('[{databaseOwner}].[{objectQualifier}Message]') and name = 'Message' and system_type_id = 99
and exists(select * from sys.sysfulltextcatalogs where name = N'YafSearch'))
begin
alter table [{databaseOwner}].[{objectQualifier}Message] alter column [Message] nvarchar(max)
end
go
And click run Query |
|
|
|
|
|
|
Rank: YAF ForumlingJoined: 2/7/2018(UTC) Posts: 5 Location: London
|
Thanks. It says: Code:Incorrect syntax near 'go'.
No Results Returned.
Cheers, Will
|
|
|
|
|
|
Rank: YAF.NET Project LeadJoined: 3/6/2010(UTC) Posts: 3,816 Thanks: 70 times
Was thanked: 1173 time(s) in 1003 post(s)
|
Yes sorry remove the last go Code:if exists (select top 1 1 from sys.columns where object_id = object_id('[{databaseOwner}].[{objectQualifier}Message]') and name = 'Message' and system_type_id = 99
and exists(select * from sys.sysfulltextcatalogs where name = N'YafSearch'))
begin
exec sp_fulltext_column N'[{databaseOwner}].[{objectQualifier}Message]', N'Message', N'drop'
end
if exists (select top 1 1 from sys.columns where object_id = object_id('[{databaseOwner}].[{objectQualifier}Message]') and name = 'Message' and system_type_id = 99
and exists(select * from sys.sysfulltextcatalogs where name = N'YafSearch'))
begin
alter table [{databaseOwner}].[{objectQualifier}Message] alter column [Message] nvarchar(max)
end
|
|
|
|
|
|
|
Rank: YAF ForumlingJoined: 2/7/2018(UTC) Posts: 5 Location: London
|
Thanks. That executed and I got: Code:
Cannot alter or drop column 'Message' because it is enabled for Full-Text Search.
No Results Returned.
|
|
|
|
|
|
Rank: YAF.NET Project LeadJoined: 3/6/2010(UTC) Posts: 3,816 Thanks: 70 times
Was thanked: 1173 time(s) in 1003 post(s)
|
Not sure why its not working but lets try the direct approach Code:if exists (select top 1 1 from sys.columns where object_id = object_id('[{databaseOwner}].[{objectQualifier}Message]') and name = 'Message' and system_type_id = 99
and exists(select * from sys.sysfulltextcatalogs where name = N'YafSearch'))
begin
alter fulltext index on [dbo].[yaf_Message] drop ([Message])
alter table [{databaseOwner}].[{objectQualifier}Message] alter column [Message] nvarchar(max)
end
|
|
|
|
|
|
|
Rank: YAF ForumlingJoined: 2/7/2018(UTC) Posts: 5 Location: London
|
Thanks very much - that worked OK. I'll have another go at running the upgrade on Monday and see how it goes.
Will
|
|
|
|
|
|
Rank: YAF ForumlingJoined: 2/7/2018(UTC) Posts: 5 Location: London
|
I've run a test upgrade on a staging server and that went OK - thanks for your help.
|
|
|
|
|
|
Rank: YAF.NET Project LeadJoined: 3/6/2010(UTC) Posts: 3,816 Thanks: 70 times
Was thanked: 1173 time(s) in 1003 post(s)
|
Uff Okay wait with the upgrade there will be a new version on the weekend. |
|
|
|
|
|
|
| Users browsing this topic |
|
|
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.