logo
Welcome to our new AbleCommerce forums. As a guest, you may view the information here. To post to this forum, you must have a registered account with us, either as a new user evaluating AbleCommerce or an existing user of the application. For all questions related to the older version of Gold and earlier, please go to forums.ablecommerce.com. Please use your AbleCommerce username and password to Login. Forums Registration.

Notification

Icon
Error

Options
Go to last post Go to first unread
Joe Payne @ AbleMods LLC  
#1 Posted : Friday, September 6, 2019 10:08:44 AM(UTC)
Joe Payne @ AbleMods LLC

Rank: Newbie

Groups: Developers
Joined: 11/9/2018(UTC)
Posts: 5

Trying to upgrade a 7.0.3 install to v9. First I upgraded to Gold R12. That worked flawlessly the db runs fine as Gold R12 so far as I can tell. I was able to authenticate admin and view the admin dashboard.

When I try to upgrade the now-Gold database to v9 B5588, it throws an exception about a missing column. Any suggestions?

Quote:

[SqlException (0x80131904): Invalid column name 'CatalogUrlId'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +3306108
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +736
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4061
System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +90
System.Data.SqlClient.SqlDataReader.get_MetaData() +99
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) +604
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +3303
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +667
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +83
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +301
NHibernate.AdoNet.AbstractBatcher.ExecuteReader(DbCommand cmd) +503
NHibernate.Loader.Loader.GetResultSet(DbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) +418
NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer) +271
NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer) +213
NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer) +163

[GenericADOException: could not execute query
[ SELECT this_.RedirectId as redirectid1_89_0_, this_.StoreId as storeid2_89_0_, this_.SourceUrl as sourceurl3_89_0_, this_.LoweredSourceUrl as loweredsourceurl4_89_0_, this_.TargetUrl as targeturl5_89_0_, this_.UseRegEx as useregex6_89_0_, this_.CreatedDate as createddate7_89_0_, this_.LastVisitedDate as lastvisiteddate8_89_0_, this_.VisitCount as visitcount9_89_0_, this_.CatalogUrlId as catalogurlid10_89_0_, this_.OrderBy as orderby11_89_0_ FROM ac_Redirects this_ WHERE this_.StoreId = @p0 and this_.UseRegEx = @p1 ORDER BY this_.OrderBy asc ]
Name:cp0 - Value:CommerceBuilder.Stores.Store Name:cp1 - Value:True
[SQL: SELECT this_.RedirectId as redirectid1_89_0_, this_.StoreId as storeid2_89_0_, this_.SourceUrl as sourceurl3_89_0_, this_.LoweredSourceUrl as loweredsourceurl4_89_0_, this_.TargetUrl as targeturl5_89_0_, this_.UseRegEx as useregex6_89_0_, this_.CreatedDate as createddate7_89_0_, this_.LastVisitedDate as lastvisiteddate8_89_0_, this_.VisitCount as visitcount9_89_0_, this_.CatalogUrlId as catalogurlid10_89_0_, this_.OrderBy as orderby11_89_0_ FROM ac_Redirects this_ WHERE this_.StoreId = @p0 and this_.UseRegEx = @p1 ORDER BY this_.OrderBy asc]]
NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer) +488
NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) +26
NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) +789
NHibernate.Impl.CriteriaImpl.List(IList results) +62
NHibernate.Impl.CriteriaImpl.List() +75
CommerceBuilder.Seo.RedirectRepository.LoadDynamicRedirects() +38
CommerceBuilder.Seo.RedirectService.InitializeCache() +117
CommerceBuilder.Seo.RedirectService.LocateRedirect(String sourceUrl) +115
CommerceBuilder.Essentials.RedirectionProcessingModule.a(HttpContext A_0, HttpRequest A_1) +458
CommerceBuilder.Essentials.RedirectionProcessingModule.b(Object A_0, EventArgs A_1) +254
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +139
System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +195
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +88

Wanna join the discussion?! Login to your AbleCommerce Forums forum account. Forums Registration.

shari  
#2 Posted : Friday, September 6, 2019 11:20:11 AM(UTC)
shari

Rank: Member

Groups: Admin, Authorized User, Registered, Developers
Joined: 10/5/2018(UTC)
Posts: 14

Was thanked: 4 time(s) in 4 post(s)
From an upgrade from Gold R12 database, this should be working fine.
I have retested with an Upgrade process from Gold R12 to AC9 5588. It is working fine. No exception at all.

Can you please confirm that you are using the latest release build.

In our upgrade script, we managed to create a newly added column in ac_redirects table.

For now, you can fix your install by running this script on your database.

Code:
--
-- Alter [ac_Redirects] Add column [CatalogUrlId]
--
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ac_Redirects' AND COLUMN_NAME = 'CatalogUrlId')
BEGIN
	ALTER TABLE [ac_Redirects] ADD [CatalogUrlId] INT NULL
END

GO

--
-- Create foreign key [ac_CatalogUrls_ac_Redirects_FK1] on table [ac_Redirects]
--
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'ac_Redirects'
AND CONSTRAINT_NAME = 'ac_CatalogUrls_ac_Redirects_FK1')
BEGIN
	ALTER TABLE ac_Redirects
		ADD CONSTRAINT ac_CatalogUrls_ac_Redirects_FK1 FOREIGN KEY (CatalogUrlId)
	 REFERENCES ac_CatalogUrls (CatalogUrlId) ON UPDATE NO ACTION ON DELETE CASCADE
END

Joe Payne @ AbleMods LLC  
#3 Posted : Friday, September 6, 2019 11:23:59 AM(UTC)
Joe Payne @ AbleMods LLC

Rank: Newbie

Groups: Developers
Joined: 11/9/2018(UTC)
Posts: 5

Yea I don't have that column in ac_Redirects

But, I can't run your script. The db does not have a table 'ac_CatalogUrls'.

Joe Payne @ AbleMods LLC  
#4 Posted : Friday, September 6, 2019 11:25:53 AM(UTC)
Joe Payne @ AbleMods LLC

Rank: Newbie

Groups: Developers
Joined: 11/9/2018(UTC)
Posts: 5

/app_data/VersionInfo.xml shows build 5588
CommerceBuilder.dll shows 9.0.7137.17888
shari  
#5 Posted : Friday, September 6, 2019 11:33:03 AM(UTC)
shari

Rank: Member

Groups: Admin, Authorized User, Registered, Developers
Joined: 10/5/2018(UTC)
Posts: 14

Was thanked: 4 time(s) in 4 post(s)
Can you please answer the following.

Were you able to successfully upgrade from GoldR12 to AC9 5588?
Or at which step of the upgrade process are you getting this error?
Joe Payne @ AbleMods LLC  
#6 Posted : Friday, September 6, 2019 1:15:56 PM(UTC)
Joe Payne @ AbleMods LLC

Rank: Newbie

Groups: Developers
Joined: 11/9/2018(UTC)
Posts: 5

I hit the v9 install screen. Specify all the connection settings. Make sure I've selected 'upgrade' instead of new database. Then tell it to continue.

This is the url that comes up next with the original error: http://<site>/Install/Default/Upgrade?DBV=R12
Joe Payne @ AbleMods LLC  
#7 Posted : Wednesday, September 11, 2019 8:40:48 AM(UTC)
Joe Payne @ AbleMods LLC

Rank: Newbie

Groups: Developers
Joined: 11/9/2018(UTC)
Posts: 5

So are there any other suggestions to resolve this issue? My client has authorized me to offer to send you the Gold R12 database so you can test it yourselves if you'd like. Or I can send you a Jing video showing the exact steps I am taking to upgrade the site.

Otherwise I have no other options to offer my client.
shari  
#8 Posted : Wednesday, September 11, 2019 12:28:25 PM(UTC)
shari

Rank: Member

Groups: Admin, Authorized User, Registered, Developers
Joined: 10/5/2018(UTC)
Posts: 14

Was thanked: 4 time(s) in 4 post(s)
Hi Joe,

Sorry to be late on this.
Yes, if you can share the database it will be easy for us to investigate the issue.

Thanks
MikeR  
#9 Posted : Wednesday, September 11, 2019 1:23:59 PM(UTC)
MikeR

Rank: Advanced Member

Groups: Administrators, Developers, Registered, HelpDesk, System, Admin
Joined: 10/18/2018(UTC)
Posts: 109

Was thanked: 5 time(s) in 5 post(s)
Hi Joe,

I've done quite a few of these 7.0.3 to V9 upgrades. Triggers from 7.0.3 seem to get in the way.

Check the db after upgrade to Gold with the show triggers, and delete them if they are found.

----------------- show TRIGGERS ------------------------

SELECT
[so].[name] AS [trigger_name],
USER_NAME([so].[uid]) AS [trigger_owner],
USER_NAME([so2].[uid]) AS [table_schema],
OBJECT_NAME([so].[parent_obj]) AS [table_name],
OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS [isupdate],
OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS [isdelete],
OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS [isinsert],
OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS [isafter],
OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS [isinsteadof],
OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS [disabled] FROM sysobjects AS [so] INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id WHERE [so].[type] = 'TR'


----------------- REMOVE TRIGGERS ------------------------


DECLARE @SQLCmd nvarchar(1000)
DECLARE @Trig varchar(500)
DECLARE @sch varchar(500)

DECLARE TGCursor CURSOR FOR

SELECT ISNULL(tbl.name, vue.name) AS [schemaName]
, trg.name AS triggerName
FROM sys.triggers trg
LEFT OUTER JOIN (SELECT tparent.object_id, ts.name
FROM sys.tables tparent
INNER JOIN sys.schemas ts ON TS.schema_id = tparent.SCHEMA_ID)
AS tbl ON tbl.OBJECT_ID = trg.parent_id LEFT OUTER JOIN (SELECT vparent.object_id, vs.name
FROM sys.views vparent
INNER JOIN sys.schemas vs ON vs.schema_id = vparent.SCHEMA_ID)
AS vue ON vue.OBJECT_ID = trg.parent_id

OPEN TGCursor
FETCH NEXT FROM TGCursor INTO @sch,@Trig WHILE @@FETCH_STATUS = 0 BEGIN

SET @SQLCmd = N'DROP TRIGGER [' + @sch + '].[' + @Trig + ']'
EXEC sp_executesql @SQLCmd
PRINT @SQLCmd

FETCH next FROM TGCursor INTO @sch,@Trig END

CLOSE TGCursor
DEALLOCATE TGCursor

Thank you for your support!
Mike Randolph
AbleCommerce.com
Joe Payne @ AbleMods LLC  
#10 Posted : Thursday, September 12, 2019 8:08:01 AM(UTC)
Joe Payne @ AbleMods LLC

Rank: Newbie

Groups: Developers
Joined: 11/9/2018(UTC)
Posts: 5

No triggers were found in the database.

Mike/Shari: Do you have Dropbox so I can shoot this db over to you guys to take a look at this?
shari  
#11 Posted : Thursday, September 12, 2019 8:18:43 AM(UTC)
shari

Rank: Member

Groups: Admin, Authorized User, Registered, Developers
Joined: 10/5/2018(UTC)
Posts: 14

Was thanked: 4 time(s) in 4 post(s)
Hi Joe,

Yes, you can share with me.

shaharyartiwana@gmail.com
Joe Payne @ AbleMods LLC  
#12 Posted : Thursday, September 12, 2019 8:38:57 AM(UTC)
Joe Payne @ AbleMods LLC

Rank: Newbie

Groups: Developers
Joined: 11/9/2018(UTC)
Posts: 5

Awesome thank you. File is uploaded and share invite has been sent.
Joe Payne @ AbleMods LLC  
#13 Posted : Thursday, September 12, 2019 8:39:31 AM(UTC)
Joe Payne @ AbleMods LLC

Rank: Newbie

Groups: Developers
Joined: 11/9/2018(UTC)
Posts: 5

What I sent is the Gold R12 version of the db by the way.
shari  
#14 Posted : Thursday, September 12, 2019 10:57:21 AM(UTC)
shari

Rank: Member

Groups: Admin, Authorized User, Registered, Developers
Joined: 10/5/2018(UTC)
Posts: 14

Was thanked: 4 time(s) in 4 post(s)
Thanks Joe.

I have downloaded the DB but I have a compatibility issue in restoring the database. I am downloading the latest version to restore successfully.
Joe Payne @ AbleMods LLC  
#15 Posted : Thursday, September 12, 2019 11:25:46 AM(UTC)
Joe Payne @ AbleMods LLC

Rank: Newbie

Groups: Developers
Joined: 11/9/2018(UTC)
Posts: 5

Sorry should have told you, it's a SQL 2017 db. Kinda helps to know that I'm sure :)
shari  
#16 Posted : Thursday, September 12, 2019 11:27:53 AM(UTC)
shari

Rank: Member

Groups: Admin, Authorized User, Registered, Developers
Joined: 10/5/2018(UTC)
Posts: 14

Was thanked: 4 time(s) in 4 post(s)
Yes I am downloading the same version.

Thanks for mentioning.
shari  
#17 Posted : Friday, September 13, 2019 4:52:19 AM(UTC)
shari

Rank: Member

Groups: Admin, Authorized User, Registered, Developers
Joined: 10/5/2018(UTC)
Posts: 14

Was thanked: 4 time(s) in 4 post(s)
Hi Joe,

I have used your GoldR12 DB to upgrade to AC9. I am able to upgrade successfully with no errors.
I have used our latest build v9 B5588 (AbleCommerce AC9 (Version: 9.0.0.5588))

Are you using B5588 latest version or you upgraded RC3 B5338 to the final version?


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.