locks and sp_execute - SQL Server Forums

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

SQL Server Development (2000)

locks and sp_execute

Author

Topic

Yohann
Starting Member

2 Posts

Posted - 2009-07-16 : 09:31:20

Hi all,

I encouter big problem with locks on my DB.

I have two DB (respectively db0 and db1) and i use triggers for copy data to table0 to table1

Trigger i created is like this :

CREATE TRIGGER TRIGGER_INSERT
ON db0.table0 AFTER INSERT
AS
SET NOCOUNT ON
BEGIN
INSERT INTO db1.table1 (
data0,
data1,
data2
) SELECT
INS.data0,
INS.data1,
INS.data2
'INSERT'
FROM
INSERTED INS
WHERE
INS.data0 = '1'
END
SET NOCOUNT OFF

And i have one web service, and two jobs who use the table1 on db1, and i experiment very often this locks:

[URL=http://img402.imageshack.us/i/spexecute.jpg/]

[/URL]

What can i do?

What's this sp_execute;1 ? It's the trigger who is the cause?

What's the most efficient, replication or triggers?

Thanks for your help and sorry for my anglish (i'm french ^^).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-07-16 : 13:48:48

Why do you need the data in two databases?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Yohann
Starting Member

2 Posts

Posted - 2009-07-17 : 03:45:06

Because we need to limit the number of connection on database (we have already lot of users on) and we can't add another job connect this DB.

The primary DB have more than 200 sql tables and i just need 6 of them, so i have think trigger was the best solution...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources