I've tried LEFT JOIN and UNION but nothing worked as it should be.
It would be nice if anybody has an idea what I could do.
I have 2 Tables
TABLE1
ID NAME
1 Anton
2 Bea
3 Cesar
4 Dora
5 Emil
TABLE2
NR ID
22 1
22 3
33 4
33 3
44 5
my output should be a full list of TABLE1 and marked with the number fro= m =
TABLE2 but only if it is 22
OUTPUT
ID NAME NR
1 Anton 22
2 Bea NULL
3 Cesar 22
4 Dora NULL
5 Emil NULL
-- =
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/Try:
select
t1.*
, t2.NR
from
Table1 t1
left join
Table2 t2 on t2.ID = TD.ID and T2.NR = 22
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Matthias Kreusch" <mk@.loft-net.de> wrote in message
news:op.s979wodzifpv2o@.gollom.loft-net.local...
I've tried LEFT JOIN and UNION but nothing worked as it should be.
It would be nice if anybody has an idea what I could do.
I have 2 Tables
TABLE1
ID NAME
1 Anton
2 Bea
3 Cesar
4 Dora
5 Emil
TABLE2
NR ID
22 1
22 3
33 4
33 3
44 5
my output should be a full list of TABLE1 and marked with the number from
TABLE2 but only if it is 22
OUTPUT
ID NAME NR
1 Anton 22
2 Bea NULL
3 Cesar 22
4 Dora NULL
5 Emil NULL
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||Thx for the fast reply but it didn't work.
Access says: "Error 3296 Join expression not supported."
it's because T2.NR =3D 22 is not supported and if I put this line in a W=HERE =
statement then I get only 2 rows.
On Sat, 27 May 2006 23:13:04 +0200, Tom Moreau <tom@.dont.spam.me.cips.ca=> =
wrote:
> Try:
> select
> t1.*
> , t2.NR
> from
> Table1 t1
> left join
> Table2 t2 on t2.ID =3D TD.ID and T2.NR =3D 22
>
-- =
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||not supported?
What is your RDBMS server?
if you want it in the where clause, try this:
where (t2.nr is null or t2.nr = 22)
"Matthias Kreusch" <mk@.loft-net.de> wrote in message
news:op.s99hq0xjifpv2o@.gollom.loft-net.local...
Thx for the fast reply but it didn't work.
Access says: "Error 3296 Join expression not supported."
it's because T2.NR = 22 is not supported and if I put this line in a WHERE
statement then I get only 2 rows.
On Sat, 27 May 2006 23:13:04 +0200, Tom Moreau <tom@.dont.spam.me.cips.ca>
wrote:
> Try:
> select
> t1.*
> , t2.NR
> from
> Table1 t1
> left join
> Table2 t2 on t2.ID = TD.ID and T2.NR = 22
>
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||It IS supported - in SQL Server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Matthias Kreusch" <mk@.loft-net.de> wrote in message
news:op.s99hq0xjifpv2o@.gollom.loft-net.local...
Thx for the fast reply but it didn't work.
Access says: "Error 3296 Join expression not supported."
it's because T2.NR = 22 is not supported and if I put this line in a WHERE
statement then I get only 2 rows.
On Sat, 27 May 2006 23:13:04 +0200, Tom Moreau <tom@.dont.spam.me.cips.ca>
wrote:
> Try:
> select
> t1.*
> , t2.NR
> from
> Table1 t1
> left join
> Table2 t2 on t2.ID = TD.ID and T2.NR = 22
>
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
Showing posts with label union. Show all posts
Showing posts with label union. Show all posts
Wednesday, March 28, 2012
Problem with LEFT JOIN
I've tried LEFT JOIN and UNION but nothing worked as it should be.
It would be nice if anybody has an idea what I could do.
I have 2 Tables
TABLE1
ID NAME
1 Anton
2 Bea
3 Cesar
4 Dora
5 Emil
TABLE2
NR ID
22 1
22 3
33 4
33 3
44 5
my output should be a full list of TABLE1 and marked with the number fro=
m =
TABLE2 but only if it is 22
OUTPUT
ID NAME NR
1 Anton 22
2 Bea NULL
3 Cesar 22
4 Dora NULL
5 Emil NULL
-- =
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/Try:
select
t1.*
, t2.NR
from
Table1 t1
left join
Table2 t2 on t2.ID = TD.ID and T2.NR = 22
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Matthias Kreusch" <mk@.loft-net.de> wrote in message
news:op.s979wodzifpv2o@.gollom.loft-net.local...
I've tried LEFT JOIN and UNION but nothing worked as it should be.
It would be nice if anybody has an idea what I could do.
I have 2 Tables
TABLE1
ID NAME
1 Anton
2 Bea
3 Cesar
4 Dora
5 Emil
TABLE2
NR ID
22 1
22 3
33 4
33 3
44 5
my output should be a full list of TABLE1 and marked with the number from
TABLE2 but only if it is 22
OUTPUT
ID NAME NR
1 Anton 22
2 Bea NULL
3 Cesar 22
4 Dora NULL
5 Emil NULL
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||Thx for the fast reply but it didn't work.
Access says: "Error 3296 Join expression not supported."
it's because T2.NR =3D 22 is not supported and if I put this line in a W=
HERE =
statement then I get only 2 rows.
On Sat, 27 May 2006 23:13:04 +0200, Tom Moreau <tom@.dont.spam.me.cips.ca=
> =
wrote:
> Try:
> select
> t1.*
> , t2.NR
> from
> Table1 t1
> left join
> Table2 t2 on t2.ID =3D TD.ID and T2.NR =3D 22
>
-- =
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||not supported?
What is your RDBMS server?
if you want it in the where clause, try this:
where (t2.nr is null or t2.nr = 22)
"Matthias Kreusch" <mk@.loft-net.de> wrote in message
news:op.s99hq0xjifpv2o@.gollom.loft-net.local...
Thx for the fast reply but it didn't work.
Access says: "Error 3296 Join expression not supported."
it's because T2.NR = 22 is not supported and if I put this line in a WHERE
statement then I get only 2 rows.
On Sat, 27 May 2006 23:13:04 +0200, Tom Moreau <tom@.dont.spam.me.cips.ca>
wrote:
> Try:
> select
> t1.*
> , t2.NR
> from
> Table1 t1
> left join
> Table2 t2 on t2.ID = TD.ID and T2.NR = 22
>
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||It IS supported - in SQL Server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Matthias Kreusch" <mk@.loft-net.de> wrote in message
news:op.s99hq0xjifpv2o@.gollom.loft-net.local...
Thx for the fast reply but it didn't work.
Access says: "Error 3296 Join expression not supported."
it's because T2.NR = 22 is not supported and if I put this line in a WHERE
statement then I get only 2 rows.
On Sat, 27 May 2006 23:13:04 +0200, Tom Moreau <tom@.dont.spam.me.cips.ca>
wrote:
> Try:
> select
> t1.*
> , t2.NR
> from
> Table1 t1
> left join
> Table2 t2 on t2.ID = TD.ID and T2.NR = 22
>
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
It would be nice if anybody has an idea what I could do.
I have 2 Tables
TABLE1
ID NAME
1 Anton
2 Bea
3 Cesar
4 Dora
5 Emil
TABLE2
NR ID
22 1
22 3
33 4
33 3
44 5
my output should be a full list of TABLE1 and marked with the number fro=
m =
TABLE2 but only if it is 22
OUTPUT
ID NAME NR
1 Anton 22
2 Bea NULL
3 Cesar 22
4 Dora NULL
5 Emil NULL
-- =
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/Try:
select
t1.*
, t2.NR
from
Table1 t1
left join
Table2 t2 on t2.ID = TD.ID and T2.NR = 22
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Matthias Kreusch" <mk@.loft-net.de> wrote in message
news:op.s979wodzifpv2o@.gollom.loft-net.local...
I've tried LEFT JOIN and UNION but nothing worked as it should be.
It would be nice if anybody has an idea what I could do.
I have 2 Tables
TABLE1
ID NAME
1 Anton
2 Bea
3 Cesar
4 Dora
5 Emil
TABLE2
NR ID
22 1
22 3
33 4
33 3
44 5
my output should be a full list of TABLE1 and marked with the number from
TABLE2 but only if it is 22
OUTPUT
ID NAME NR
1 Anton 22
2 Bea NULL
3 Cesar 22
4 Dora NULL
5 Emil NULL
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||Thx for the fast reply but it didn't work.
Access says: "Error 3296 Join expression not supported."
it's because T2.NR =3D 22 is not supported and if I put this line in a W=
HERE =
statement then I get only 2 rows.
On Sat, 27 May 2006 23:13:04 +0200, Tom Moreau <tom@.dont.spam.me.cips.ca=
> =
wrote:
> Try:
> select
> t1.*
> , t2.NR
> from
> Table1 t1
> left join
> Table2 t2 on t2.ID =3D TD.ID and T2.NR =3D 22
>
-- =
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||not supported?
What is your RDBMS server?
if you want it in the where clause, try this:
where (t2.nr is null or t2.nr = 22)
"Matthias Kreusch" <mk@.loft-net.de> wrote in message
news:op.s99hq0xjifpv2o@.gollom.loft-net.local...
Thx for the fast reply but it didn't work.
Access says: "Error 3296 Join expression not supported."
it's because T2.NR = 22 is not supported and if I put this line in a WHERE
statement then I get only 2 rows.
On Sat, 27 May 2006 23:13:04 +0200, Tom Moreau <tom@.dont.spam.me.cips.ca>
wrote:
> Try:
> select
> t1.*
> , t2.NR
> from
> Table1 t1
> left join
> Table2 t2 on t2.ID = TD.ID and T2.NR = 22
>
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||It IS supported - in SQL Server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Matthias Kreusch" <mk@.loft-net.de> wrote in message
news:op.s99hq0xjifpv2o@.gollom.loft-net.local...
Thx for the fast reply but it didn't work.
Access says: "Error 3296 Join expression not supported."
it's because T2.NR = 22 is not supported and if I put this line in a WHERE
statement then I get only 2 rows.
On Sat, 27 May 2006 23:13:04 +0200, Tom Moreau <tom@.dont.spam.me.cips.ca>
wrote:
> Try:
> select
> t1.*
> , t2.NR
> from
> Table1 t1
> left join
> Table2 t2 on t2.ID = TD.ID and T2.NR = 22
>
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
Saturday, February 25, 2012
Problem with deadocks
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...Aswers in microsoft.public.sqlserver.server
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||http://www.microsoft.com/communitie...80-e67d8812bd2d
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||In your from statement use WITH (NOLOCKS) when you are selecting data.
Rick
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:B5CBFB12-BF8F-4BA8-B71A-8D9A893C66F6@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...Aswers in microsoft.public.sqlserver.server
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||http://www.microsoft.com/communitie...80-e67d8812bd2d
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||In your from statement use WITH (NOLOCKS) when you are selecting data.
Rick
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:B5CBFB12-BF8F-4BA8-B71A-8D9A893C66F6@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
Problem with Deadlocks
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...
See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/colu...gdeadlocks.asp
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
|||Hi
Have a look as this thread:
http://groups.google.ch/group/micros...1e5ad772e285b7
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...
|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...
See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/colu...gdeadlocks.asp
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
|||Hi
Have a look as this thread:
http://groups.google.ch/group/micros...1e5ad772e285b7
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...
|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
Problem with Deadlocks
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||Hi
Have a look as this thread:
http://groups.google.ch/group/microsoft.public.sqlserver.programming/browse_thread/thread/6f618594592ab8d7/381e5ad772e285b7?lnk=st&q=tibor+deadlock&rnum=1&hl=en#381e5ad772e285b7
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||Hi
Have a look as this thread:
http://groups.google.ch/group/microsoft.public.sqlserver.programming/browse_thread/thread/6f618594592ab8d7/381e5ad772e285b7?lnk=st&q=tibor+deadlock&rnum=1&hl=en#381e5ad772e285b7
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
Problem with Deadlocks
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||Hi
Have a look as this thread:
e5ad772e285b7" target="_blank">http://groups.google.ch/group/micro...
e5ad772e285b7
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||Hi
Have a look as this thread:
e5ad772e285b7" target="_blank">http://groups.google.ch/group/micro...
e5ad772e285b7
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
Subscribe to:
Posts (Atom)