AbleCommerce Shopping Cart Software

Hi, I'm having some problems with some random errors in AbleCommerce. I cannot pinpoint what is causing the problem - there is no problem connecting to the SQL server. The error happens randomly. Any help or ideas would be greatly appreciated:

System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.TypeInitializationException: The type initializer for 'CommerceBuilder.Users.a' threw an exception. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CommerceBuilder.Data.Database.OpenConnection()
at CommerceBuilder.Data.Database.ExecuteReader(DbCommand command)
at CommerceBuilder.Stores.Store.Load(Int32 storeId)
at CommerceBuilder.Stores.StoreDataSource.Load(Int32 storeId, Boolean useCache)
at CommerceBuilder.Common.Token.get_Store()
at CommerceBuilder.Utility.LocaleHelper.ToLocalTime(DateTime utc)
at CommerceBuilder.Users.User.LoadDataReader(User user, IDataReader dr)
at CommerceBuilder.Users.User.Load(Int32 userId)
at CommerceBuilder.Users.UserDataSource.Load(Int32 userId, Boolean useCache)
at CommerceBuilder.Common.Token.InitUserContext(HttpContext context)
at CommerceBuilder.Services.b.b(Object A_0, EventArgs A_1)
at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
--- End of inner exception stack trace ---
at CommerceBuilder.Users.a.a()
at CommerceBuilder.Users.RoleDataSource.GetIdByName(String name)
at CommerceBuilder.Users.RoleDataSource.IsUserInRole(Int32 userId, String roleName)
at CommerceBuilder.Users.User.IsInRole(ICollection`1 roleCollection)
at Layouts_AdminPanel.Page_Init(Object sender, EventArgs e)
at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
--- End of inner exception stack trace ---
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.guitar_bodies_bodybuilder_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

AbleCommerce is having some trouble connecting to your SQL database. Is it on the same server? Are you using the Express version? It's a network error, so it may be difficult to troubleshoot. It could simply be a performance issue if the server is having trouble.

In these older versions of AbleCommerce, it is not unusual to run into maintenance issues. In the error, I noticed this may have something to do with the user table, which makes me think you might have an abnormally large number of user records. We have patches and help instructions available to clean up anonymous users, but it might be best if you let me know which version you are running so I can provide better information to help.

SQL Server 2008 R2 is run on a separate server (Windows Server 2012 R2). The error happens at random times, so it is hard to track down. We are running AC 7.0.7 and it looks like there are 74,482 users in AC. I'll run some maintenance to delete users older than 15 or 30 days.

Actually, I just checked the database and it shows 969885 anonymous users (with the oldest being 15 days old). It is already set to delete older than 15 days. That seems a bit crazy. Any ideas?

I know this is a painfully long document, but it might help -

http://help.ablecommerce.com/upgrades/a ... enance.htm

There were some maintenance issues in older version of 7.0.x that may have carried over to the 7.0.7 version.

Katie wrote:I know this is a painfully long document, but it might help -

http://help.ablecommerce.com/upgrades/a ... enance.htm

There were some maintenance issues in older version of 7.0.x that may have carried over to the 7.0.7 version.

I've narrowed the anonymous user window down to 3 days, but we still have around 180,000. Do you have any idea why so many users are being created? Is an anonymous user created for every site visitor?

Also, I have tried everything that I can think of to try and resolve the random SQL connection error, but I still get them every so often. Do you have any idea what else I could try to do to fix it?

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CommerceBuilder.Data.Database.OpenConnection()
at CommerceBuilder.Data.Database.ExecuteReader(DbCommand command)
at CommerceBuilder.Stores.Store.Load(Int32 storeId)
at CommerceBuilder.Stores.StoreDataSource.Load(Int32 storeId, Boolean useCache)
at CommerceBuilder.Common.Token.get_Store()
at CommerceBuilder.Utility.LocaleHelper.ToLocalTime(DateTime utc)
at CommerceBuilder.Users.User.LoadDataReader(User user, IDataReader dr)
at CommerceBuilder.Users.User.Load(Int32 userId)
at CommerceBuilder.Users.UserDataSource.Load(Int32 userId, Boolean useCache)
at CommerceBuilder.Common.Token.InitUserContext(HttpContext context)
at CommerceBuilder.Services.b.b(Object A_0, EventArgs A_1)
at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Maybe it could be related to this bug- just guessing here.
AC7-288: Store does not restart when the database connection is dropped and working after initialization takes place

Here is a link to a page that has the link to the hot patch.
http://help.ablecommerce.com/upgrades/a ... atches.htm

I've narrowed the anonymous user window down to 3 days, but we still have around 180,000

I doubt you are getting this many anonymous users in 3 days...but I could be wrong. The most likely reason that the number isn't being reduced is that the maintenance isn't working properly. The document I linked should help with that. You can also confirm this is the issue by checking your error logs and look for any maintenance errors.

Judy supplied a link so you can hot patch for AC7-288, which should take care of the network error. However, the maintenance is probably the underlying cause of the network error, so they both need to be fixed.

We are definitely getting tons of anonymous users in a 3 day window. When does Ablecommerce create them? Is a user created on every visit? We are not getting any of the maintenance errors in our logs, so I don't think it is a user constraint issue. I have the maintenance window set to 3 days, deleted old anonymous users, set the maintenance interval to 90, I ran the Fix User Constraints file, and I've had the hot patch (AC7-288) installed for awhile. What else could be the problem?

Yes, AbleCommerce creates an anonymous user record for each new user that visits the site. This site must be getting a lot of traffic to generate that many records, but if your servers have the performance and resources to backup this kind of traffic, then it should work without any trouble.

If you are curious, you can use your website logs and a 3rd-party website analytics program to see the number of visits to the site.

We probably get about 150,000+ pageviews per day on average. Any idea what else I could look into for the random SQL errors?

I'm sorry, but I don't have much more to offer on AbleCommerce. You will need to try some Google searching on this error. I found some info posted by Microsoft indicating this might be fixed by the latest service release.

http://support.microsoft.com/kb/968382

Make sure you have the latest service pack installed for SQL.