SQL Server DB error when upgrading to latest YAF version

Welcome Guest! To enable all features please Login or Register

johnk
74.8% (Friendly)
YAF All-the-Time Topic Starter

11 years ago

I downloaded the latest version 2.0 RC 1 from codeplex.

When I ran /forum/install/default.aspx, after clicking on Next on "Upgrade Database" page, I got the error below.

I am using Microsoft Azure (which has a slightly different version of SQL server called Azure SQL which is built on top of SQL Server 2012).


Server Error in '/' Application.

FILE:
mssql/tables.sql

ERROR:
Deprecated feature 'DROP INDEX with two-part name' is not supported in this version of SQL Server.

STATEMENT:
-- convert uniqueidentifier to nvarchar(64)
if exists (select top 1 1 from syscolumns where id=object_id('[dbo].[yaf_User]') and name='ProviderUserKey' and xtype='36')
begin
-- drop the provider user key index if it exists...
if exists(select 1 from dbo.sysindexes where name=N'IX_yaf_User_ProviderUserKey' and id=object_id(N'[dbo].[yaf_User]'))
begin
DROP INDEX [dbo].[yaf_User].[IX_yaf_User_ProviderUserKey]
end
-- alter the column
ALTER TABLE [dbo].[yaf_User] ALTER COLUMN ProviderUserKey nvarchar(64)
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/tables.sql

ERROR:
Deprecated feature 'DROP INDEX with two-part name' is not supported in this version of SQL Server.

STATEMENT:
-- convert uniqueidentifier to nvarchar(64)
if exists (select top 1 1 from syscolumns where id=object_id('[dbo].[yaf_User]') and name='ProviderUserKey' and xtype='36')
begin
-- drop the provider user key index if it exists...
if exists(select 1 from dbo.sysindexes where name=N'IX_yaf_User_ProviderUserKey' and id=object_id(N'[dbo].[yaf_User]'))
begin
DROP INDEX [dbo].[yaf_User].[IX_yaf_User_ProviderUserKey]
end
-- alter the column
ALTER TABLE [dbo].[yaf_User] ALTER COLUMN ProviderUserKey nvarchar(64)
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/tables.sql

ERROR:
Deprecated feature 'DROP INDEX with two-part name' is not supported in this version of SQL Server.

STATEMENT:
-- convert uniqueidentifier to nvarchar(64)
if exists (select top 1 1 from syscolumns where id=object_id('[dbo].[yaf_User]') and name='ProviderUserKey' and xtype='36')
begin
	-- drop the provider user key index if it exists...
	if exists(select 1 from dbo.sysindexes where name=N'IX_yaf_User_ProviderUserKey' and id=object_id(N'[dbo].[yaf_User]'))
	begin
		DROP INDEX [dbo].[yaf_User].[IX_yaf_User_ProviderUserKey]
	end
	-- alter the column
	ALTER TABLE [dbo].[yaf_User] ALTER COLUMN ProviderUserKey nvarchar(64)
end]
   YAF.Classes.Data.LegacyDb.system_initialize_executescripts(String script, String scriptFile, Boolean useTransactions) in d:\yafnet_ba903e9138b2\yafsrc\YAF.Classes\YAF.Classes.Data\mssql\LegacyDb.cs:6295
   YAF.Core.Services.InstallUpgradeService.ExecuteScript(String scriptFile, Boolean useTransactions) in d:\yafnet_ba903e9138b2\yafsrc\YAF.Core\Services\InstallUpgradeService.cs:347
   YAF.Core.Services.InstallUpgradeService.UpgradeDatabase(Boolean fullText, Boolean upgradeExtensions) in d:\yafnet_ba903e9138b2\yafsrc\YAF.Core\Services\InstallUpgradeService.cs:256
   YAF.Install._default.Wizard_NextButtonClick(Object sender, WizardNavigationEventArgs e) +1353
   System.Web.UI.WebControls.Wizard.OnNextButtonClick(WizardNavigationEventArgs e) +111
   System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +401
   System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +16
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +114
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +159
   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) +35
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.18033

Edited by user 11 years ago | Reason: Not specified

Wanna join the discussion?! Login to your YAF.NET Support Forum forum account or Register a new forum account

Sponsor

bbobb
100% (Exalted)
YAF Developer

11 years ago

Azure implementation was discussed here and was a manual, use search.

johnk
74.8% (Friendly)
YAF All-the-Time Topic Starter

11 years ago

I just did a search for "azure" and the last post was 10 months ago. Any idea what search term I should use?

tha_watcha
100% (Exalted)
YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2

11 years ago

I just did a search for "azure" and the last post was 10 months ago. Any idea what search term I should use?

Originally Posted by: johnk

Try the documentation

https://yafnet.codeplex.com/wikipage?title=Install%20YAF%20with%20Windows%20SQL%20Azure&referringTitle=Documentation

johnk
74.8% (Friendly)
YAF All-the-Time Topic Starter

11 years ago

Thanks tha_watcha. I already did that few months back and it was working fine till Beta 2. Only when I upgraded to RC1, I suddenly got that error message.

http://www.dnnsoftware.com/forums/forumid/56/postid/459277/scope/posts

I also found this documentation on technet which says they are going to depreciate the "Drop Index" function

http://technet.microsoft.com/en-us/library/ms143729.aspx

bbobb
100% (Exalted)
YAF Developer

11 years ago

You could explain it before.

We use 2005 version anyway so all the indexes drops in YAF should be rewritten to something like

DROP INDEX [IX_yaf_User_ProviderUserKey] ON [dbo].[yaf_User].

johnk
74.8% (Friendly)
YAF All-the-Time Topic Starter

11 years ago

You could explain it before.

We use 2005 version anyway so all the indexes drops in YAF should be rewritten to something like

DROP INDEX [IX_yaf_User_ProviderUserKey] ON [dbo].[yaf_User].

Originally Posted by: bbobb

Sorry, I should have also mentioned that I was upgrading the code.

Currently in production, the site has YAF 2.0 RC1 code. DB has 2.0 Beta 2 code. In addition when I ran the /install/default.aspx file, it failed DURING the upgrade process (error message above).

I am guessing this mean the site/DB is in unstable state. Is it possible for you to modify the "drop index" fix and post it to codeplex? Thank you 🙂

Edited by user 11 years ago | Reason: Not specified

johnk
74.8% (Friendly)
YAF All-the-Time Topic Starter

11 years ago

Thank you bob for fixing the "Drop index" bug. I added the fix and ran /install/default.aspx again.

It gave me around 70+ error messages. Error message is in the spoiler section below.

Show Spoiler

Server Error in '/' Application.

FILE:

mssql/tables.sql

ERROR:

Invalid object name 'dbo.sysindexes'.

STATEMENT:

-- drop the old contrained just in case

if exists (select top 1 1 from dbo.sysindexes where id=object_id('[dbo].[yaf_ActiveAccess]') and name='IX_yaf_ActiveAccess')

alter table [dbo].[yaf_ActiveAccess] drop constraint IX_yaf_ActiveAccess

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/tables.sql

ERROR:

Invalid object name 'dbo.sysindexes'.

STATEMENT:

-- drop the old contrained just in case

if exists (select top 1 1 from dbo.sysindexes where id=object_id('[dbo].[yaf_ActiveAccess]') and name='IX_yaf_ActiveAccess')

alter table [dbo].[yaf_ActiveAccess] drop constraint IX_yaf_ActiveAccess

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/tables.sql

ERROR:

Invalid object name 'dbo.sysindexes'.

STATEMENT:

-- drop the old contrained just in case

if exists (select top 1 1 from dbo.sysindexes where id=object_id('[dbo].[yaf_ActiveAccess]') and name='IX_yaf_ActiveAccess')

alter table [dbo].[yaf_ActiveAccess] drop constraint IX_yaf_ActiveAccess]

YAF.Classes.Data.LegacyDb.system_initialize_executescripts(String script, String scriptFile, Boolean useTransactions) in d:\yafnet_ba903e9138b2\yafsrc\YAF.Classes\YAF.Classes.Data\mssql\LegacyDb.cs:6295

YAF.Core.Services.InstallUpgradeService.ExecuteScript(String scriptFile, Boolean useTransactions) in d:\yafnet_ba903e9138b2\yafsrc\YAF.Core\Services\InstallUpgradeService.cs:347

YAF.Core.Services.InstallUpgradeService.UpgradeDatabase(Boolean fullText, Boolean upgradeExtensions) in d:\yafnet_ba903e9138b2\yafsrc\YAF.Core\Services\InstallUpgradeService.cs:256

YAF.Install._default.Wizard_NextButtonClick(Object sender, WizardNavigationEventArgs e) +1353

System.Web.UI.WebControls.Wizard.OnNextButtonClick(WizardNavigationEventArgs e) +111

System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +401

System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +16

System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37

System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +114

System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +159

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) +35

System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.18033

Since azure SQL does not have dbo.sysindexes (or any other system db), I got a lot of these errors.

What I did is deleted all code which had "dbo.sysindexes" and ran the /install/ again. After lots of deletes, the upgrade finally succeeded.

The forum is running and I am able to make new posts. However when I goto delete any post, the system gives an error message:


Server Error in '/' Application.

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

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.Data.SqlClient.SqlException: Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

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: 


[SqlException (0x80131904): Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1753986
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5296058
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +558
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1682
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +269
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +1379
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +175
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +205
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +160
   YAF.Classes.Data.MsSqlDbAccess.ExecuteNonQuery(IDbCommand cmd, Boolean transaction) in d:\yafnet_ba903e9138b2\yafsrc\YAF.Classes\YAF.Classes.Data\mssql\MsSqlDbAccess.cs:457
   YAF.Types.Interfaces.Data.IDbAccessExtensions.ExecuteNonQuery(IDbAccess dbAccess, IDbCommand cmd) in d:\yafnet_ba903e9138b2\yafsrc\YAF.Types\Interfaces\Data\IDbAccessExtensions.cs:48
   YAF.Classes.Data.LegacyDb.message_deleteRecursively(Object messageID, Boolean isModeratorChanged, String deleteReason, Int32 isDeleteAction, Boolean deleteLinked, Boolean isLinked, Boolean eraseMessages) in d:\yafnet_ba903e9138b2\yafsrc\YAF.Classes\YAF.Classes.Data\mssql\LegacyDb.cs:9902
   YAF.Pages.deletemessage.ToogleDeleteStatus_Click(Object sender, EventArgs e) +255
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9553178
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +103
   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) +35
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.18033

Since this is a generic error message, I am guessing I would get this for other actions (apart from deleting post). Any idea how to fix this?

Also any way to make it seamless to upgrade for Azure SQL DB without modifying 70+ lines of SQL code?

Edited by user 11 years ago | Reason: Not specified

bbobb
100% (Exalted)
YAF Developer

11 years ago

dbo.sysindexes should be changed for sys.indexes

ON [PRIMARY] should be removed

Tecnically this is not a YAF problem, but the conversion soft problem.

We use T-SQL 2000 syntaxis because YAF supported it until recently, now it moved to 2005 and higher and some things should be rewritten. I currently don't have time to transform all this, but maybe someone has.

johnk
74.8% (Friendly)
YAF All-the-Time Topic Starter

11 years ago

#10

Do you mean the text "dbo.sysindexes" should be changed to "sys.indexes"?

Also I could not find any text containing "on [PRIMARY]" or "[PRIMARY]". I searched the entire solution.

Anything else I can do?

Edited by user 11 years ago | Reason: Not specified

bbobb
100% (Exalted)
YAF Developer

11 years ago

#11

When sys.indexes replace id = for object_id =

if there's a (status & 2048) <> 0 in sys.indexes select check replace it for is_primary_key = 1

THat's all with indexes.

I not guarantee that after you finish with them you will not bump into something else.

So ask if somethin' bad happens, i will check the forum more often :wink:

squirrel
100% (Exalted)
YAF Leader

11 years ago

#12

I'm not as advanced with SQL as you are, but if I can help somewhere, just let me know, bbobb --

If you can't find it using the forum search, try my signature link -- searches this site using Google: Google is my Friend

johnk
74.8% (Friendly)
YAF All-the-Time Topic Starter

11 years ago

#13

I actually did not understand the post above by bbobb. Since you are the SQL DB expert, I would have to wait for you (or squirrel) to fix the bug. :)

Tha_watcha: Could you add a note in azure documentation that currently users CANNOT upgrade (or downgrade) the forum. Trying to manually upgrade the forum could make the DB unstable (unless they know what they are doing).

Thanks squirrel for offering to help in fixing this bug 🙂

Edited by user 11 years ago | Reason: just saw squirrels reply above. I had older page open :)

bbobb
100% (Exalted)
YAF Developer

11 years ago

#14

I've changed the scripts, please try it.

I'm not as advanced with SQL as you are, but if I can help somewhere, just let me know, bbobb --

Originally Posted by: squirrel

Thanx.

Edited by user 11 years ago | Reason: Not specified

bbobb
100% (Exalted)
YAF Developer

11 years ago

#15

It can't garantee a smooth upgrade, but at least we would know what to do next.

Important Information: