Transaction Context Error No:3910

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Transaction Context Error No:3910

Author

Topic

nayanancha
Starting Member

27 Posts

Posted - 2012-03-12 : 12:19:11

I am getting the below error when I am running my dynamic SQL in my begin transaction.

Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Below is my sample Code


Begin Transaction
Declare @sql nvarchar(max),
		@ParmDefinition nvarchar(500),
		@table varchar(100)
set @table ='CompServer.Comp_Sandbox.dbo.test_Table'
set @sql = 'delete from ' + @table + '
			Where Id=1'
exec sp_executesql @sql
Commit Transaction

I am able to get rid of the error when I am commenting my begin and commit statements or If I have my table name only with DB name but not server name i.e.

set @table ='Comp_Sandbox.dbo.test_Table'

How could I fix this error by having the server name with full path and inside the begin transaction

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 12:45:03

see this

http://sqlblog.com/blogs/linchi_shea/archive/2009/12/18/the-transact-sql-prime-directive-a-bad-example.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nayanancha
Starting Member

27 Posts

Posted - 2012-03-12 : 13:46:14

So does that mean there's no solution for this??

Thanks,

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-12 : 15:57:26

You don't need a transaction because a single delete is already atomic. However, if you are going to execute mutiple statements from your Dynamic SQL and need to wrap them in all in a transaction, then you need to include the transaction in your D-SQL.

nayanancha
Starting Member

27 Posts

Posted - 2012-03-13 : 09:06:40

I have many delete and update statements in my Stored Procedure, I just gave example with a block. I need to have it in transaction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources