Database issues

[scuba]

For some reason, it seems that I cannot use SQL Server Management Studio (hereafter "SSMS") and access my AbleCommerce store web site (hereafter "AC") at the same time. If connect to the database in SSMS, then AC is unavailable (Server error in application). If I disconnect from SSMS and go to AC everything is fine, but I cannot reconnect to the DB in SSMS. If I close AC, I still cannot connect in SSMS (cannot access DB, it is being used by another process). After about 10 minutes has passed since I closed AC, I can get back into the DB through SSMS. Any help would be greatly appreciated. The support staff has not been able to help and told me to post my problem in the forums... Thanks.

[jmestep]

There is a patch for a server unavailable message if the store cannot access the database
http://help.ablecommerce.com/upgrades/a ... _7.0.3.htm
down at the bottom.

The only reason I can think of that would be causing your issue is if you are only allowing one user at a time to connect to the database, like exclusive access.

[scuba]

Thanks, but that patch is for 7.0.3 and I am using 7.0.4 (which I would have to assume already includes the 7.0.3 patches, yes? I've also installed all of the 7.0.4 patches at the suggestion of the support staff... no dice). Where would I check to make sure I don't have exclusive access enabled? I doubt I do unless it is enabled by default in SQL Server Express 2008... :/

[scuba]

Okay, I checked the restricted access option and it is already set to Multi User, so that isn't the problem either... This is driving me crazy! Can you (or anyone else) offer any other advice? Should I export all my data and rebuild the database from scratch or what????

[Michael Ward]

Hello
For clarification can you please tell us what version of SQL you are using?

Thanks

[scuba]

I am using SQL Server Express 2008 and SQL Server Management Studio version 10.0.1600.22.

[igavemybest]

I would post it on asp.net forums or better yet here: http://www.sqlteam.com/forums/ . Doesnt sound like an ablecommerce issue.

[afm]

I am using SQL Server Express 2008 and SQL Server Management Studio version 10.0.1600.22.

Are you connecting to the *.mdf file directly from either AC or SSMS? For example, does the AC connection string include "AttachDbFilename"?

[scuba]

I am using SQL Server Express 2008 and SQL Server Management Studio version 10.0.1600.22.

Are you connecting to the *.mdf file directly from either AC or SSMS? For example, does the AC connection string include "AttachDbFilename"?

I did attach the database to SSMS--is that the problem?? If I don't attach it, how am I supposed to use SSMS to access the database? (I am a programmer, not a DBA, so a lot of this is new to me). I have no idea if the AC connection string has "AttachDbFilename" in it, as the connection string is encrypted in the database.config file.

[afm]

I did attach the database to SSMS--is that the problem?? If I don't attach it, how am I supposed to use SSMS to access the database? (I am a programmer, not a DBA, so a lot of this is new to me). I have no idea if the AC connection string has "AttachDbFilename" in it, as the connection string is encrypted in the database.config file.

If you use server type of "Database Engine" when you form the connection in SSMS, then SSMS is not causing the problem.

There is an AC Admin setting in Security to remove the connection string encryption.

The bottom line is that if SSMS or AC is connecting to the database in single-user mode, it will lock out the other. Both SSMS and AC can attach directly to the database file, which forces the database into single-user mode. On the other hand, both SSMS and AC can attach to the database as normal SQL Server clients which is definitely multi-user.

If you are not sure, I think you should avoid using SSMS.