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
9.0.6 upgrade error - not AC fault
Options
Go to last post Go to first unread
Previous Topic Next Topic
judy at Web2Market  
#1 Posted : Wednesday, April 6, 2022 11:18:31 AM(UTC)
judy at Web2Market

Rank: Advanced Member

Groups: Developers
Joined: 11/7/2018(UTC)
Posts: 303

Thanks: 21 times
Was thanked: 5 time(s) in 5 post(s)
We have an old database we are trying to upgrade from R12- I get this error after the upgrade ran when I try to access the site. We ran into similar errors once when we tried to move the database.
Any help would be appreciated. There were numerous other errors during the upgrade and I had to remove some DTA indexes that had been added.

Incorrect syntax near '@errorNumber'.
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: Incorrect syntax near '@errorNumber'.

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): Incorrect syntax near '@errorNumber'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +265
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +714
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4496
System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +108
System.Data.SqlClient.SqlDataReader.get_MetaData() +93
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) +551
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) +3307
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) +650
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +117
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +368
NHibernate.AdoNet.AbstractBatcher.ExecuteReader(DbCommand cmd) +473
NHibernate.Id.InsertSelectDelegate.ExecuteAndExtract(DbCommand insert, ISessionImplementor session) +78
NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder) +119

[GenericADOException: could not insert: [CommerceBuilder.Users.User][SQL: INSERT INTO ac_Users (StoreId, UserName, LoweredUserName, Email, LoweredEmail, AffiliateId, AffiliateReferralDate, PrimaryWishlistId, PayPalId, PasswordQuestion, PasswordAnswer, IsApproved, IsAnonymous, IsLockedOut, CreateDate, LastActivityDate, LastLoginDate, LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart, TaxExemptionType, TaxExemptionReference, Comment) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); select SCOPE_IDENTITY()]]
NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder) +371
NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Object obj, ISessionImplementor session) +276
NHibernate.Action.EntityIdentityInsertAction.Execute() +194
NHibernate.Engine.ActionQueue.Execute(IExecutable executable) +44
NHibernate.Event.Default.AbstractSaveEventListener.PerformSaveOrReplicate(Object entity, EntityKey key, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) +846
NHibernate.Event.Default.AbstractSaveEventListener.SaveWithGeneratedId(Object entity, String entityName, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) +253
NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.SaveWithGeneratedOrRequestedId(SaveOrUpdateEvent event) +96
NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.EntityIsTransient(SaveOrUpdateEvent event) +139
CommerceBuilder.DomainModel.SaveOrUpdateEventListener.PerformSaveOrUpdate(SaveOrUpdateEvent event) +216
NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(SaveOrUpdateEvent event) +343
NHibernate.Impl.SessionImpl.FireSaveOrUpdate(SaveOrUpdateEvent event) +91
NHibernate.Impl.SessionImpl.SaveOrUpdate(Object obj) +80
CommerceBuilder.DomainModel.RepositoryWithTypedId`2.Save(TEntity item) +141
CommerceBuilder.DomainModel.Repository`1.Save(TEntity item) +56
CommerceBuilder.Users.UserRepository.Save(User item) +114

Edited by user Wednesday, April 6, 2022 11:19:34 AM(UTC)  | Reason: Not specified
Back to top

Wanna join the discussion?! Login to your AbleCommerce Forums forum account. New Registrations are disabled.
Back to top  
nadeem  
#2 Posted : Thursday, April 7, 2022 1:16:18 AM(UTC)
nadeem

Rank: Advanced Member

Groups: Administrators, Developers, Registered, HelpDesk, Authorized User, Admin, System
Joined: 10/11/2018(UTC)
Posts: 110

Thanks: 19 times
Was thanked: 18 time(s) in 18 post(s)
Quote:
[SqlException (0x80131904): Incorrect syntax near '@errorNumber'.]


This error seems to be related to the schema being migrated having triggers for cascades. The triggers were no longer required on new database they were meant for SQL 2000. If you have triggers for insert, update and delete on ac_Users table that were part of our SQL 2000 scripts for AC7, then you can disable/delete those triggers.

Here is the sql script to delete all triggers.

Code:
DECLARE @TriggerName AS VARCHAR(500)
 -- Drop or Delete All Triggers in a Database in SQL Server 
DECLARE DropTrigger CURSOR FOR
  SELECT TRG.name AS TriggerName
  FROM   sys.triggers TRG
         INNER JOIN sys.tables TBL
                 ON TBL.OBJECT_ID = TRG.parent_id 
OPEN DropTrigger
 FETCH Next FROM DropTrigger INTO @TriggerName 
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL VARCHAR(MAX)=NULL
      SET @SQL='Drop Trigger ' + @TriggerName
      PRINT 'Trigger ::' + @TriggerName
            + ' Droped Successfully'
      EXEC (@SQL)
      PRINT @SQL
      FETCH Next FROM DropTrigger INTO @TriggerName
  END
CLOSE DropTrigger 
DEALLOCATE DropTrigger


Make sure to execute the script only if you don't have any custom triggers because the script will drop all the triggers from the database.
Hope this helps!
Back to top
judy at Web2Market  
#3 Posted : Thursday, April 7, 2022 6:23:34 AM(UTC)
judy at Web2Market

Rank: Advanced Member

Groups: Developers
Joined: 11/7/2018(UTC)
Posts: 303

Thanks: 21 times
Was thanked: 5 time(s) in 5 post(s)
Thank you so much. I had just downloaded some db compare software to try to figure out the problem! Is this something that had been part of AC code in the past for very old sites on SQL 2000? I'm asking because if so, we need to check for it before we run upgrades on really old sites in the future.
Back to top
nadeem  
#4 Posted : Thursday, April 7, 2022 9:23:59 AM(UTC)
nadeem

Rank: Advanced Member

Groups: Administrators, Developers, Registered, HelpDesk, Authorized User, Admin, System
Joined: 10/11/2018(UTC)
Posts: 110

Thanks: 19 times
Was thanked: 18 time(s) in 18 post(s)
Yes, the triggers does existed on old SQL 2000 databases. There will not be any reference in the code. A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. You can find the triggers under ac_Users table in SQL Server database.

To view database level triggers, Open SQL Server management studio and navigate to the database. Expand the database and navigate to Programmability -> Database Triggers

Edited by user Thursday, April 7, 2022 9:29:32 AM(UTC)  | Reason: Not specified
Back to top
Users browsing this topic
Guest (3)
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.14 | YAF.NET © 2003-2024, Yet Another Forum.NET
This page was generated in 1.001 seconds.