"Transaction context in use by another session"

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 2005 Forums

Transact-SQL (2005)

"Transaction context in use by another session"

Author

Topic

user1111
Starting Member

5 Posts

Posted - 2009-09-11 : 16:35:59

Hi!

I'v the following view and that triger ...


CREATE view [dbo].[t] as
select * from t1
union
select * from server2.DB.dbo.t2


create trigger t_insert
    on dbo.t
    instead of insert
as
        insert into t1 ([name],[Type])
        select [name],[Type]
        from inserted where Type<>14
     

        insert into server2.DB.dbo.t2 ([name],[Type])
        select [name],[Type]
        from inserted where Type=14
 
go

when i try this query, give me error "Transaction context in use by another session"


insert into t ([name],[Type]) values ('n',14)

i want to try this query with success
is there any change to triger???
pls help

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 17:09:02

I think it is a bad idea to have a trigger perform a remote server insert. If you have any problems at all the user transaction will be aborted. Perhaps you can insert to a local table and replicate that table over to your other server. Or use some other process to move the data outside the context of that user transaction.

Be One with the Optimizer
TG

user1111
Starting Member

5 Posts

Posted - 2009-09-11 : 17:24:56

I am using sql express that doesnt support replication...
I have used windows task scheduler and some scripts to move data between servers for other tables...
but for above table i want to move data to remote server as soon as local insert occoures...is there a way about this by using partitioned view as above?

user1111
Starting Member

5 Posts

Posted - 2009-09-14 : 10:31:30

the problem resolved

i had a trigger on table t2 involving errors

now query works...

thanks TG !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources