YAF.NET Support Forum

Welcome Guest! To enable all features please Login or Register.
Issue SQL upgrade error
Options
View
Go to last post Go to first unread
Previous Topic Next Topic
Offline willhowells  
#1 Posted : 07 February 2018 12:25:43(UTC)
willhowells


Rank: YAF Forumling

Reputation:

Joined: 07/02/2018(UTC)
Posts: 6
United Kingdom
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
Back to top
Sponsor
Back to top  

Wanna join the discussion?! Login to your YAF.NET Support Forum forum account, or Register a new forum account.
Back to top  
Offline tha_watcha  
#2 Posted : 08 February 2018 15:29:27(UTC)
tha_watcha


Rank: YAF.NET Project Lead

Reputation:

Medals: Medal of Honor: Portal Puzzle Master: Portal Puzzle Master for DNNMedal of Honor Key: Given to pillars of the community who are key players in the YAF community and project.DNN Master: DNN Module ManBug Killer Medal of Honor: Medal for being a kick-ass bug killer!Medal of Honor for the Support King: Support King! Amazing asset to the YAF.NET Community.

Joined: 06/03/2010(UTC)
Posts: 3,941
Germany

Thanks: 73 times
Was thanked: 1191 time(s) in 1021 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
Back to top
Offline willhowells  
#3 Posted : 08 February 2018 17:35:03(UTC)
willhowells


Rank: YAF Forumling

Reputation:

Joined: 07/02/2018(UTC)
Posts: 6
United Kingdom
Location: London
Thanks. It says:

Code:
Incorrect syntax near 'go'.

No Results Returned.


Cheers,
Will
Back to top
Offline tha_watcha  
#4 Posted : 08 February 2018 19:51:45(UTC)
tha_watcha


Rank: YAF.NET Project Lead

Reputation:

Medals: Medal of Honor: Portal Puzzle Master: Portal Puzzle Master for DNNMedal of Honor Key: Given to pillars of the community who are key players in the YAF community and project.DNN Master: DNN Module ManBug Killer Medal of Honor: Medal for being a kick-ass bug killer!Medal of Honor for the Support King: Support King! Amazing asset to the YAF.NET Community.

Joined: 06/03/2010(UTC)
Posts: 3,941
Germany

Thanks: 73 times
Was thanked: 1191 time(s) in 1021 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
Back to top
Offline willhowells  
#5 Posted : 09 February 2018 11:11:33(UTC)
willhowells


Rank: YAF Forumling

Reputation:

Joined: 07/02/2018(UTC)
Posts: 6
United Kingdom
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.
Back to top
Offline tha_watcha  
#6 Posted : 09 February 2018 15:27:40(UTC)
tha_watcha


Rank: YAF.NET Project Lead

Reputation:

Medals: Medal of Honor: Portal Puzzle Master: Portal Puzzle Master for DNNMedal of Honor Key: Given to pillars of the community who are key players in the YAF community and project.DNN Master: DNN Module ManBug Killer Medal of Honor: Medal for being a kick-ass bug killer!Medal of Honor for the Support King: Support King! Amazing asset to the YAF.NET Community.

Joined: 06/03/2010(UTC)
Posts: 3,941
Germany

Thanks: 73 times
Was thanked: 1191 time(s) in 1021 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
Back to top
Offline willhowells  
#7 Posted : 09 February 2018 16:55:25(UTC)
willhowells


Rank: YAF Forumling

Reputation:

Joined: 07/02/2018(UTC)
Posts: 6
United Kingdom
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
Back to top
Offline willhowells  
#8 Posted : 09 February 2018 17:29:07(UTC)
willhowells


Rank: YAF Forumling

Reputation:

Joined: 07/02/2018(UTC)
Posts: 6
United Kingdom
Location: London
I've run a test upgrade on a staging server and that went OK - thanks for your help.
Back to top
Offline tha_watcha  
#9 Posted : 09 February 2018 19:33:58(UTC)
tha_watcha


Rank: YAF.NET Project Lead

Reputation:

Medals: Medal of Honor: Portal Puzzle Master: Portal Puzzle Master for DNNMedal of Honor Key: Given to pillars of the community who are key players in the YAF community and project.DNN Master: DNN Module ManBug Killer Medal of Honor: Medal for being a kick-ass bug killer!Medal of Honor for the Support King: Support King! Amazing asset to the YAF.NET Community.

Joined: 06/03/2010(UTC)
Posts: 3,941
Germany

Thanks: 73 times
Was thanked: 1191 time(s) in 1021 post(s)
Uff Okay wait with the upgrade there will be a new version on the weekend.
Back to top
Rss Feed  Atom Feed
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.

Privacy Policy | Powered by YAF.NET 2.2.4.15 | YAF.NET © 2003-2019, Yet Another Forum.NET
This page was generated in 0.094 seconds.

Notification

Icon
Error

About

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.

Project Twitter Updates

Contact Us

Email: Email Contact

Stay Connected

Powered by Resharper

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