1

Closed

setup error - This SqlTransaction has completed; it is no longer usable

description

Hi
I encountered an error when I tried to install itracker. - This SqlTransaction has completed; it is no longer usable
 
SYSTEM: Win2008 R2 Datacenter SP1 , SQL 2008 R2, .NET 4.0, ASP .net 4.0 on default app pool
Running unver vmware workstation 7.x with 2 gig of ram
** O/S was installed from scratch. no Prior system was in existance.
** The database seems to be updated but I am unable to proceed from here.
 
Attached is the exception and the scripted database
 
I am very eager to try out this system. I hope there will be a way around this.
 
 
This SqlTransaction has completed; it is no longer usable

Server Error in '/it' Application.

 
This SqlTransaction has completed; it is no longer usable.
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.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
 
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:
 
 
[InvalidOperationException: This SqlTransaction has completed; it is no longer usable.]
System.Data.SqlClient.SqlTransaction.ZombieCheck() +1623536
System.Data.SqlClient.SqlTransaction.Rollback() +172
Setup_Default.Button3a_Click(Object sender, EventArgs e) +2561
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +154
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3691
 
[EDIT---Script trimmed out to make post more readable -- Curt ]
Closed Mar 20, 2012 at 6:38 PM by darkfalz

comments

eagleeye1 wrote Apr 2, 2011 at 8:46 AM

If there is a way to manual install, I don't mind executing .SQL scripts to create the database and manually editing files to get this program working..

darkfalz wrote Apr 2, 2011 at 4:22 PM

Couple quick notes/questions.
  • What step in the process were you at when this happened?
  • What kind of DB setup/user were you running?
Additionally, if you want to manually do the setup:
  • run the MSSQL_V3_NewInstall.sql
  • run the RequiredData.sql
  • From VS, run the ASPNET Configuration and add a user and give it Administrator group access within the app groups.
Let me know on the first items and I'll try to recreate but I hadn't had the issue you mentioned so it's difficult to assertain the cause.
~Curt

wrote Apr 2, 2011 at 4:22 PM

eagleeye1 wrote Apr 3, 2011 at 1:54 AM

Hi
1) I did the above 2 steps (the sampledata.sql had errors in the code # of values is too many and also some int conflict
THe other 2 SQL command text executed successfully

2) So anyway I created a user called i3 which owns the inv3 database
2.1 ) I changed the connection string to
add name="dfs_ITracker_dbConnectionString" connectionString="Data Source=localhost;Initial Catalog=inv3;User ID=i3;Password=q;" providerName="System.Data.SqlClient"

3) I accessed the application - http://localhost and it forced me to the url 4) http://localhost/it/Setup/Default.aspx
5) How do I make changes to which file to force it to goto the login.aspx page?

Allan

eagleeye1 wrote Apr 3, 2011 at 7:16 AM

I tried the manual install. however I cannot get past the setup sequence. I know I need to change the connection string in web.config - what else need I change to bypass the setup?
*There are errors in the sample user data sql text file - too many fields and incompatible datatype



I also tried again on a new VM, with SQL server 2005 this time. But got exactly the same error

I am now testing with XP and SQL server 2008 Express

I need to BYPASS the setup page? what can i change to fix it?

Allan

eagleeye1 wrote Apr 3, 2011 at 8:01 AM

I just tried on XP with ASP.NET 4 and IIS 6 connecting to a remote SQL server 2008 R2 Exact same error.
Is this a security related error? I created a login (i8) with dbowner datareader and datawriter.. and I used this user
again I got

setup error - This SqlTransaction has completed; it is no longer usable

Allan

darkfalz wrote Apr 3, 2011 at 6:51 PM

In the web.config is a setting called RunSetup, just changed this to False and the setup process will be bypassed for manual setups. The app automatically sets this to FALSE at the end of the setup process.

wrote Apr 4, 2011 at 12:09 PM

darkfalz wrote Apr 4, 2011 at 6:50 PM

What's throwing me off here, trying to debug your issue, is the note that the error was "in /it application". By default it's using an Application Name of "/" and not "/it". It looks like you tried to run the SQL before running the application for auto-setup.
The app, if using the web-installer, is designed so that you just extract the app, create a blank DB and then run the site. It will run all the SQL for you, you dont manually run the SQL unless you want to do it all manually.
Please, could you give me the exact steps you did to setup the site and what you did to run it that caused you to get the initial SQL error?

eagleeye1 wrote Apr 5, 2011 at 1:16 AM

Hi
OK about the \it issue here is what I simply did.
1) I got a server ready for IIS ASP.net 4.0
2) I copied the ITTRACKER files to c:\inetpub\wwwroot\it
3) I then Converted the folder to an IIS application from the IIS manager
4) I then use http://localhost/it to acces it Sorry , if it was not apparent to me if the app was hardcoded to be in c:\inetpub\wwwroot\ is this the solution? I'll try it out once I get the green light.

Allan

darkfalz wrote Apr 5, 2011 at 2:33 AM

The app should run fine in a subsite its how it was developed so that isn't it. I don't have access to a cm setup but I doubt that would be the cause either. When you setup the new db it was just blank, correct? You didn't run any scripts on it manually before the setup? Also, you mentioned the user you setup in SQL what specific permissions did you give it? I'm thinking that may be the issue. I did all the initial work for it with a user that was dbo.

eagleeye1 wrote Apr 5, 2011 at 3:26 AM

Hi
Yes the database was EMPTY, maybe you can just post the DBO user that you created as an SQL script, I will then apply it again to a completely blank system.

Rights I gave the user was EVERYTHING (except DENYxxxx). I tried using SA but sql stopped me.

Alternatively, can you post a DEBUG compile version so that all the values can be shown?

Allan

darkfalz wrote Apr 5, 2011 at 12:40 PM

  • As for the user, if possible try as a superuser/sa account with dbowner rights, just to see if that was the case.
  • As far as a debug version, I don't intend to release a debug version of the web installer. If there is a need to run the debug code the intent is that you get the other version (full source code) and build/deploy/run it from there.

darkfalz wrote Apr 5, 2011 at 12:42 PM

Additionally...... I see the DB script you posted has the db name of "TEST" but your connstring says the db name is "inv3".... I'm confused

eagleeye1 wrote Apr 6, 2011 at 5:36 AM

That was from one of my earlier tests that failed with the SQL error, in that case its called test. I tried to many times with so many cre-creations of the databases. So I have many empty databases which I used for retries.

Allan

vuln wrote Jun 23, 2011 at 10:01 AM

Hi, I'm getting same error here when i try to install the application:
-------START-----------------

This SqlTransaction has completed; it is no longer usable.
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.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.

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:


[InvalidOperationException: This SqlTransaction has completed; it is no longer usable.]
System.Data.SqlClient.SqlTransaction.ZombieCheck() +1647392
System.Data.SqlClient.SqlTransaction.Rollback() +172
Setup_Default.Button3a_Click(Object sender, EventArgs e) +2561
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +154
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3707
---------------------END-----------------------------

What i did is the following: (The sql server is 2008 on a windows server 2008, the IIS 7 is another server windows server 2008 with .Net 4)

1- Created blank DB named itracker
2- used the sa as db owner
3- created application pool in the iis and selected .Net 4 as frameword and integrated managed pipeline mode. named it itracker.
4- create website itracker, default pool selected as itracker and folder location was C:\inetpub\wwwroot\itracker , port 8084
5- browsed the website and the setup page loaded. added my db server name as well the db name and sa username with the password. clicked next and i got the error.
when i check the db table i see the following is created though:

dbo.aspnet_Application
dbo.aspnet_Membership
dbo.aspnet_Path
dbo.aspnet_PersonalizationAllUsers
dbo.aspnet_PersonalizationPerUser
dbo.aspnet_Profile
dbo.aspnet_Role
dbo.aspnet_SchemaVersions
dbo.aspnet_Users
dbo.aspnet_UsersInRoles
dbo.aspnet_WebEvent_Events
dbo.cs_Site
dbo.inventory
dbo.vendor

every time i try to brows i keep getting the same problem, did i miss something here?

Thanks,

darkfalz wrote Jun 23, 2011 at 12:35 PM

It looks like the DB created for you, I'm guessing it failed out on the Add User portion.
Try editing the Web.Config and setting the RunSetup, set it to False.
Then try the app once.

Also, look in the web.config, see what it has for a connection string. The user you put into the setup tool should be in the connectionstring.

Lastly, if the first two are done you may have to register a new user and manually add them to the Administrator group in the application if the initial user wasn't created.

wrote Jun 23, 2011 at 12:37 PM

vuln wrote Jun 26, 2011 at 12:46 PM

thanks darkfalz, i will give it a try and update the post later.

vuln wrote Jun 26, 2011 at 1:12 PM

Hi,
i done as per the suggestion:
<?xml version="1.0"?>
<configuration>
<appSettings>
    <add key="runSetup" value="false"/>
</appSettings>
<connectionStrings>
    <clear/>
    <add name="dfs_ITracker_dbConnectionString" connectionString="Data Source=(10.1.1.62)\itracker;Initial Catalog=ITracker;User ID=itrack;Password=123456;" providerName="System.Data.SqlClient"/>
</connectionStrings>
when i run the app i get the following error:

------------START-----------------

Server Error in '/' Application.

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
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: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Source Error:

The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:
  1. Add a "Debug=true" directive at the top of the file that generated the error. Example:
<%@ Page Language="C#" Debug="true" %>

or:

2) Add the following section to the configuration file of your application:

<configuration>
<system.web>
   <compilation debug="true"/>
</system.web>
</configuration>

Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.

Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario.

Stack Trace:


[SqlException (0x80131904): 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
BasePage.Page_Error(Object sender, EventArgs e) +53
System.Web.UI.Page.HandleError(Exception e) +182
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +11352022
System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +11351550
System.Web.UI.Page.ProcessRequest() +269
System.Web.UI.Page.ProcessRequest(HttpContext context) +167
ASP.default_aspx.ProcessRequest(HttpContext context) +51
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +625

System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +270

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1

-----------------------END---------------------

Any idea?

Thanks,
Best regards,

vuln wrote Jun 26, 2011 at 1:26 PM

Also when i try to change the connection string to:

<?xml version="1.0"?>
<configuration>
<appSettings>
    <add key="runSetup" value="false"/>
</appSettings>
<connectionStrings>
    <clear/>
    <add name="dfs_ITracker_dbConnectionString" connectionString="Data Source=10.1.1.62;Initial Catalog=itracker;User ID=itrack;Password=123456;" providerName="System.Data.SqlClient"/>
</connectionStrings>
---------START---------------

Server Error in '/' Application.

There is no row at position 0.
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.IndexOutOfRangeException: There is no row at position 0.

----------------END-------------

Best regards,

darkfalz wrote Jun 27, 2011 at 2:29 PM

Vuln....
From the 2 things you posted on Sunday.... The first looks like you have a bad connection string. You will want to verify that.
That could lead to the other item you posted but to me that looks like your DB isn't fully setup.
  • Delete your DB.
  • Create a new DB (named whatever). Add a user to it from within SQL Manager, give this user Owner/DBO (a SQL user, not a Windows user).
  • Re-enable the RunSetup in the web.config.
  • Re-launch the site.
    This will star the setup process over. Try again once and let me know if you still have an issue. If you do I can see about creating a .BAK file for SQL that can be pre-loaded and setup for the application...but that can often be just as big of a pain.

vuln wrote Jul 7, 2011 at 11:37 AM

Hi darkfalz,

i did as suggested but it same, the connectionstring is fine at my server (i run the setup from the iis server)

here is the log error from the event viewer:

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 7/7/2011 3:12:30 PM
Event time (UTC): 7/7/2011 11:12:30 AM
Event ID: 6705d7c5954c4ed89cf96c5176ed127d
Event sequence: 3
Event occurrence: 1
Event detail code: 0

Application information:
Application domain: /LM/W3SVC/4/ROOT-6-129545107503266676 
Trust level: Full 
Application Virtual Path: / 
Application Path: C:\inetpub\wwwroot\itracker\ 
Machine name: FILESERVER 
Process information:
Process ID: 5628 
Process name: w3wp.exe 
Account name: IIS APPPOOL\itracker 
Exception information:
Exception type: IndexOutOfRangeException 
Exception message: There is no row at position 0.
at BasePage.Page_Error(Object sender, EventArgs e)
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.default_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)



Request information:
Request URL: http://localhost:8084/default.aspx 
Request path: /default.aspx 
User host address: ::1 
User:  
Is authenticated: False 
Authentication Type:  
Thread account name: IIS APPPOOL\itracker 
Thread information:
Thread ID: 39 
Thread account name: IIS APPPOOL\itracker 
Is impersonating: False 
Stack trace:    at BasePage.Page_Error(Object sender, EventArgs e)
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.default_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Best regards,

darkfalz wrote Jul 11, 2011 at 3:51 PM

Vuln..... please confirm the following:
In the Database, you should have 11 tables that start with "aspnet_".
Then you should have the application tables.

In the aspnet_Applications table, there should be 1 entry.
In the aspnet_Membership table, there should be 1 entry.
In the aspnet_Roles table, there should be 1 entry.
In the aspnet_Users table, there should be 1 entry.
In the aspnet_UsersInRoles, there should be 1 entry.
In the aspnet_SchemaVersions, there should be 6 entries.

Let me know what's missing (possibly all).

darkfalz wrote Jul 11, 2011 at 6:44 PM


For those people with this issue. Verify the DB tables are all created and then just manually run this, it will create all the necessary information in the database for you.

-=-=-=-=-=-==-=-=-=
declare @newUserID UniqueIdentifier
declare @newRoleID UniqueIdentifier
set @newUserID = NEWID()
set @newRoleID = NEWID()
Insert into [aspnet_Applications] ([ApplicationName],[LoweredApplicationName],[ApplicationId],[Description]) values ('/','/','F6BD60C5-0A27-4E59-9781-58393C5C0C2B','ITracker Application')
Insert into [aspnet_Users] ([ApplicationId],[UserId],[UserName],[LoweredUserName],[MobileAlias],[IsAnonymous],[LastActivityDate]) values ('F6BD60C5-0A27-4E59-9781-58393C5C0C2B',@newUserID,'Admin','admin',Null,0,GETDATE())
Insert into [aspnet_Membership] ([ApplicationId],[UserId],[Password],[PasswordFormat],[PasswordSalt],[MobilePIN],[Email],[LoweredEmail],[PasswordQuestion],[PasswordAnswer],[IsApproved],[IsLockedOut],[CreateDate],[LastLoginDate],[LastPasswordChangedDate],[LastLockoutDate],[FailedPasswordAttemptCount],[FailedPasswordAttemptWindowStart],[FailedPasswordAnswerAttemptCount],[FailedPasswordAnswerAttemptWindowStart],[Comment]) values ('F6BD60C5-0A27-4E59-9781-58393C5C0C2B',@newUserID,'FZ2efGESh1qBaK0UFVgLwYEmg98=',1,'5m1+0pDJRX7iHk9SpLXBpg==',Null,'me@example.com','me@example.com',Null,Null,1,0,GETDATE(),GETDATE(),GETDATE(),'1754-01-01 00:00:00.000',0,'1754-01-01 00:00:00.000',0,'1754-01-01 00:00:00.000',Null)
Insert into [aspnet_Roles] ([ApplicationId],[RoleId],[RoleName],[LoweredRoleName],[Description]) values ('F6BD60C5-0A27-4E59-9781-58393C5C0C2B',@newRoleID,'Administrator','administrator',Null)
Insert into [aspnet_UsersInRoles] ([UserId],[RoleId]) values (@newUserID,@newRoleID )
Insert into [aspnet_SchemaVersions] ([Feature],[CompatibleSchemaVersion],[IsCurrentVersion]) values ('common',1,1)
Insert into [aspnet_SchemaVersions] ([Feature],[CompatibleSchemaVersion],[IsCurrentVersion]) values ('health monitoring',1,1)
Insert into [aspnet_SchemaVersions] ([Feature],[CompatibleSchemaVersion],[IsCurrentVersion]) values ('membership',1,1)
Insert into [aspnet_SchemaVersions] ([Feature],[CompatibleSchemaVersion],[IsCurrentVersion]) values ('personalization',1,1)
Insert into [aspnet_SchemaVersions] ([Feature],[CompatibleSchemaVersion],[IsCurrentVersion]) values ('profile',1,1)
Insert into [aspnet_SchemaVersions] ([Feature],[CompatibleSchemaVersion],[IsCurrentVersion]) values ('role manager',1,1)
INSERT INTO [cs_Site] ([SiteTitle],[SiteSubTitle],[SiteVersion],[SiteTheme],[SiteImage],[ShowSiteImage],[RequireLogin],[AllowManualRegistration],[SiteMessage],[OnlyAdminsCanEdit],[AutoApproveUsers]) VALUES ('Demo Site','This is a demo site.','V3.0.0','Standard','',0,0,0,'<p>This is where you would put any site messages you want to display. They would only display if the selected Theme has enabled them in it''s layout.</p>',0,0);
-=-=-=-==-=-=-=-=-=-=
hope this helps you all.

~Curt

darkfalz wrote Mar 20, 2012 at 6:37 PM

With 4.0 this issue is defunct, we've removed the SQL option.
Closing the issue.

wrote Mar 20, 2012 at 6:38 PM

wrote Feb 14, 2013 at 7:37 PM

wrote May 16, 2013 at 7:47 AM