HELP !! DEADLOCK OCCURED !!! - 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 Administration (2000)

HELP !! DEADLOCK OCCURED !!!

Author

Topic

trusted4u
Posting Yak Master

109 Posts

Posted - 2002-11-18 : 00:24:09

Hi:
The following is the program which I have scheduled on IT_dev server to run daily at 8.30pm. From DTC2 server - Proto DB - EQ_MAIN table, I want to copy the records to IT_DEV server - TrafficFines DB - PEQ_MAIN table [Before copying I deleting the current records from PEQ_MAIN - DAILY].

Dim ProtoCnn As New Connection
Dim TrafficFinesCnn As New Connection
Dim ProtoRS As New Recordset
Dim TFRS As New Recordset
Dim CMD As New ADODB.Command

Sub main()
ProtoCnn.ConnectionString = "Provider=SQLOLEDB;DATA SOURCE=DTC2;DATABASE=proto;UID=username;pwd=mypwd"
TrafficFinesCnn.ConnectionString = "Provider=SQLOLEDB;DATA SOURCE=it_dev;DATABASE=trafficfines;UID=username;pwd=mypwd"

TrafficFinesCnn.Open

CMD.ActiveConnection = TrafficFinesCnn
CMD.CommandType = adCmdText
CMD.CommandText = "Delete From PEQ_Main"
CMD.Execute
Set CMD = Nothing
ProtoCnn.Open

ProtoRS.Open "EQ_MAIN", ProtoCnn, adOpenForwardOnly, adLockReadOnly
TFRS.Open "PEQ_MAIN", TrafficFinesCnn, adOpenForwardOnly, adLockPessimistic

While Not ProtoRS.EOF
TFRS.AddNew
For i = 0 To TFRS.Fields.Count - 1
TFRS.Fields(i).Value = ProtoRS.Fields(i).Value
Next
TFRS.Update
ProtoRS.MoveNext
Wend
End Sub

It worked fine for 3-4 days but today morning when I checked my IT_DEV server it gave me an error stating :

[b]
Your Transaction was deadlocked with another process & has been choosen as a deadlock victim. Return your Transaction.
/b]

Can Anybody please tell me how to solve this problem. And also let me know whether my approach is correct or not.
Its very URGENT!!

Thanks in Anticipation,
Marjo.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-18 : 01:10:18

You can link the two servers using sp_addlinkedserver,
then create a stored procedure (see below) and a dts package to run the stored procedure at 8:30pm.

Alternatively you can also do it all in the dts package by running the delete in an execute sql task and then run a transformation task to copy over the data.

CREATE PROCEDURE dbo.CopyRecordsFromProtoDB

AS

DELETE FROM IT_DEV.TrafficFines.dbo.PEQ_MAIN

INSERT INTO IT_DEV.TrafficFines.dbo.PEQ_Main (Col1,Col2,...,Coln)
SELECT Col1, Col2,....,Coln
FROM DTC2.Proto.dbo.EQ_MAIN

Edited by - ValterBorges on 11/18/2002 01:13:03

trusted4u
Posting Yak Master

109 Posts

Posted - 2002-11-18 : 02:00:58

Thanks a lot Valter.
As I am not a DBA I have never tried such tools [ Linked Servers ]. Just now I read about it, and I tried to create a linked server through EM. It created but under the new linked server node it shows a node called as Tables which is blank. While creating the linked server I specified the Proto DB Servername i.e. DTC2,username and password.
Thanks,
Marjo.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-18 : 10:43:45

Actually you might want to go with the DTS since you can easily modify it in case you're server names change, then you won't need to relink servers and change stored procedures.

trusted4u
Posting Yak Master

109 Posts

Posted - 2002-11-20 : 01:33:31

Valter now the data is getting copied properly. But what was the reason for the deadlock ??

Bye.
Marjo.

sherrer

64 Posts

Posted - 2002-11-20 : 21:39:56

I don't see anything in your code that actually causes the deadlock. I could give you some advise on handling the deadlock, but the best thing for your database is to find out what is causing it. Do you know what a deadlock is? If not see BOL, it has an excellent description of how a deadlock occurs. You can use profiler to track deadlocks, and log information about the processes and queries that cause the deadlock.

trusted4u
Posting Yak Master

109 Posts

Posted - 2002-11-21 : 02:22:58

Hi :
I have gone through BOL to read about deadlock. But still I am not able to get the clear picture of it. The table which I am using DTC2 Server - Proto DB - EQ-Main table is used by many users at the time when I am reading the records from it.
If possible can anybody explain me what a deadlock is and why it occurs. It will really help me.
Thanx a lot for ur replies.
Marjo.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-21 : 07:31:49

Books Online has probably the best and most comprehensive explanation of deadlocking you're liable to find anywhere. If it's still not clear, try reading it again, it will make sense in time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources