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 AbleCommerce Gold forum. Please use your AbleCommerce username and password to Login. New Registrations are disabled.

Notification

Icon
Error

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

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 post(s)
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. New Registrations are disabled.

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

Rank: Advanced Member

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

Thanks: 5 times
Was thanked: 113 time(s) in 112 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 Payne2  
#3 Posted : Friday, September 6, 2019 11:23:59 AM(UTC)
Joe Payne2

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 post(s)
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 Payne2  
#4 Posted : Friday, September 6, 2019 11:25:53 AM(UTC)
Joe Payne2

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 post(s)
/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)
shaharyar

Rank: Advanced Member

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

Thanks: 5 times
Was thanked: 113 time(s) in 112 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 Payne2  
#6 Posted : Friday, September 6, 2019 1:15:56 PM(UTC)
Joe Payne2

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 post(s)
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 Payne2  
#7 Posted : Wednesday, September 11, 2019 8:40:48 AM(UTC)
Joe Payne2

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 post(s)
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)
shaharyar

Rank: Advanced Member

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

Thanks: 5 times
Was thanked: 113 time(s) in 112 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: 183

Thanks: 1 times
Was thanked: 6 time(s) in 6 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 Payne2  
#10 Posted : Thursday, September 12, 2019 8:08:01 AM(UTC)
Joe Payne2

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 post(s)
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)
shaharyar

Rank: Advanced Member

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

Thanks: 5 times
Was thanked: 113 time(s) in 112 post(s)
Hi Joe,

Yes, you can share with me.

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

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 post(s)
Awesome thank you. File is uploaded and share invite has been sent.
Joe Payne2  
#13 Posted : Thursday, September 12, 2019 8:39:31 AM(UTC)
Joe Payne2

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 post(s)
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)
shaharyar

Rank: Advanced Member

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

Thanks: 5 times
Was thanked: 113 time(s) in 112 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 Payne2  
#15 Posted : Thursday, September 12, 2019 11:25:46 AM(UTC)
Joe Payne2

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 post(s)
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)
shaharyar

Rank: Advanced Member

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

Thanks: 5 times
Was thanked: 113 time(s) in 112 post(s)
Yes I am downloading the same version.

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

Rank: Advanced Member

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

Thanks: 5 times
Was thanked: 113 time(s) in 112 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?


Joe Payne2  
#18 Posted : Wednesday, September 18, 2019 3:32:31 PM(UTC)
Joe Payne2

Rank: Advanced Member

Groups: HelpDesk, Developers
Joined: 11/9/2018(UTC)
Posts: 564

Thanks: 122 times
Was thanked: 26 time(s) in 25 post(s)
Sorry for the late reply. I didn't get an email notification from the forum software.

Yes it's the b5588 download and version info was confirmed earlier in this thread.

I'm at a complete loss as to why I'm getting the errors. It's not like I'm new to spinning up a site on my local machine. There must be something different with how these MVC sites work causing me a problem. Here are my steps:

Create a hosting folder named 'testsite1'
Copy b5588 website files into the folder

Add a host entry 'testsite1' to local hosts file pointed to my local IP
Create site in IIS named 'testsite1', point folder to the hosting folder and set the host header to 'testsite1'

Fire up google, hit http://testsite1/ and the site spins right up. Install launches and the site is working. I fill out the form, set the db connection info, set the radio choice to upgrade and proceed.

Next page I get is the error.

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.