SQL Server Performance Forum – Threads Archive

Error in Linked Server After migration

Hi All,
We have SQL Server which has a link to mysql database. end of the day, we are trasnfering data from mySQL to MSSQLServer. This was workign fine with SQL Server 2000 but after migrating to SQL Server 2005 we are getting following error.
[Microsoft SQL-DMO (ODBC SQLState: 40001)] Error 1205: [Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction (process ID #13) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction. —————————————-

---

Are you getting this everytime the query runs?
Have you performed the relevant index optimization on those tables Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

yes I am getting this every time. index performance is already done on sql server 2000 so we just did a re-indexing —————————————-

---

Not sure whether the KBA:http://support.microsoft.com/kb/821537/ is any help, check it out.
Its not ideal but have you tried using transaction isolation level on SQL from serializable to read committed.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Thanks again for the advise.
That link asks to update SP3 for SQL Server 2000, but I have SQL Server 2005 with latest SP1. I have set Isolationlevel to REPEATABLE READ to ignore the dirty reads. Even I have doubt whether this is the cause —————————————-

---

Well, Identified the issue. When I changed the isolationlevel it works.
—————————————-

---

]]>