Welcome Guest! To enable all features please Login or Register.
Options
View
Go to last post Go to first unread
Offline willhowells  
#1 Posted : Wednesday, February 7, 2018 11:25:43 AM(UTC)
willhowells


Rank: YAF Forumling

Reputation:

Joined: 2/7/2018(UTC)
Posts: 5
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
Sponsor
Offline tha_watcha  
#2 Posted : Thursday, February 8, 2018 2:29:27 PM(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: 3/6/2010(UTC)
Posts: 3,783
Germany

Thanks: 70 times
Was thanked: 1159 time(s) in 989 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
Offline willhowells  
#3 Posted : Thursday, February 8, 2018 4:35:03 PM(UTC)
willhowells


Rank: YAF Forumling

Reputation:

Joined: 2/7/2018(UTC)
Posts: 5
United Kingdom
Location: London

Thanks. It says:

Code:
Incorrect syntax near 'go'.

No Results Returned.


Cheers,
Will
Offline tha_watcha  
#4 Posted : Thursday, February 8, 2018 6:51:45 PM(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: 3/6/2010(UTC)
Posts: 3,783
Germany

Thanks: 70 times
Was thanked: 1159 time(s) in 989 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
Offline willhowells  
#5 Posted : Friday, February 9, 2018 10:11:33 AM(UTC)
willhowells


Rank: YAF Forumling

Reputation:

Joined: 2/7/2018(UTC)
Posts: 5
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.
Offline tha_watcha  
#6 Posted : Friday, February 9, 2018 2:27:40 PM(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: 3/6/2010(UTC)
Posts: 3,783
Germany

Thanks: 70 times
Was thanked: 1159 time(s) in 989 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
Offline willhowells  
#7 Posted : Friday, February 9, 2018 3:55:25 PM(UTC)
willhowells


Rank: YAF Forumling

Reputation:

Joined: 2/7/2018(UTC)
Posts: 5
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
Offline willhowells  
#8 Posted : Friday, February 9, 2018 4:29:07 PM(UTC)
willhowells


Rank: YAF Forumling

Reputation:

Joined: 2/7/2018(UTC)
Posts: 5
United Kingdom
Location: London

I've run a test upgrade on a staging server and that went OK - thanks for your help.
Offline tha_watcha  
#9 Posted : Friday, February 9, 2018 6:33:58 PM(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: 3/6/2010(UTC)
Posts: 3,783
Germany

Thanks: 70 times
Was thanked: 1159 time(s) in 989 post(s)
Uff Okay wait with the upgrade there will be a new version on the weekend.
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.

Notification

Icon
Error