I have two tables in a one to many relationship, customers and invoices. The
invoices can be a variety of types marked by an invoice flag column (for
instance 1, 2 or 3). I want to retrieve customers, which only have invoices
of 1 AND 2. If there is an invoice of type 3 I don't want the customer
returned. How do I do this SQL
SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>I have two tables in a one to many relationship, customers and invoices.
>The invoices can be a variety of types marked by an invoice flag column
>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>customer returned. How do I do this SQL
>
|||I can't get this to work. If the customer has invoices of type 1 and 2 I
want the customer returned. If i.InvoiceType IN (1,2) in the query and
customer has an invoice type of 3 I don't want the customer returned.
Does this make sense. Thanks.
SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
> news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>
|||SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
AND NOT EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType = 3)
?
That gives you customers that have invoice with type either 1 or 2 but don't
have invoices of type 3.
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:u8eMEMjKFHA.2852@.TK2MSFTNGP14.phx.gbl...
>I can't get this to work. If the customer has invoices of type 1 and 2 I
>want the customer returned. If i.InvoiceType IN (1,2) in the query and
>customer has an invoice type of 3 I don't want the customer returned.
> Does this make sense. Thanks.
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> wrote in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
|||select c.*
from customers c
where c.customerid not in (select customerid from invoices where
invoicetype = 3)
if you need other data from the invoice table then just do a join to
it.
i am presuming in this example that customers can have all three types,
but will have the same customerid on each row in the invoice table,
thus if you exclude the customers on an id level, then it will exclude
the ones that have any combination of 3 (1-2-3, 1-3, 2-3, 3)
hth,
hans
|||Hi
As I have understood you need the list of customers who have invoices of
type 1 AND 2 so we can't use IN keyword because it is actually a type of OR.
Here is the query (I assumed invoice Type field is char):
select distinct A.idcustomer from
(select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
(select idinvoice,idcustomer from tblinvoice where type='2') B on
A.idcustomer=B.idcustomer
and not exists (select 'true' from tblinvoice C where
C.idcustomer=A.idcustomer and C.type not in ('1','2'))
|||Hi this is going in the right direction. To complicate things would there be
any way to make the query more dynamic. Say there were 5 invoices types and
I need to show customers with invoices of invoice type 1 AND 3 but then
wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
"Reza" <Reza@.discussions.microsoft.com> wrote in message
news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
> Hi
> As I have understood you need the list of customers who have invoices of
> type 1 AND 2 so we can't use IN keyword because it is actually a type of
> OR.
> Here is the query (I assumed invoice Type field is char):
> select distinct A.idcustomer from
> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
> (select idinvoice,idcustomer from tblinvoice where type='2') B on
> A.idcustomer=B.idcustomer
> and not exists (select 'true' from tblinvoice C where
> C.idcustomer=A.idcustomer and C.type not in ('1','2'))
|||On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>Hi this is going in the right direction. To complicate things would there be
>any way to make the query more dynamic. Say there were 5 invoices types and
>I need to show customers with invoices of invoice type 1 AND 3 but then
>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
Hi Chris,
For full flexibility, create an extra table to hold the types you need.
Then, to find all customers with all requested types of invoice plus
possibly others, use
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
INNER JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
And to find all customers with all requested types of invoice, but no
others, you change this to
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
LEFT JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
AND COUNT(*) = COUNT(t.Type)
Both above queries are untested. Post CREATE TABLE and INSERT statements
with test data if you want a tested solution.
>"Reza" <Reza@.discussions.microsoft.com> wrote in message
>news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
>
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Where does the Types wanted table come from. Is this like a many to many
relationship with customers having many invoices and invoices types having
many invoices?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:2j4j31lmk3p78au3eookj29outfd7uaei2@.4ax.com...
> On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>
> Hi Chris,
> For full flexibility, create an extra table to hold the types you need.
> Then, to find all customers with all requested types of invoice plus
> possibly others, use
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> INNER JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> And to find all customers with all requested types of invoice, but no
> others, you change this to
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> LEFT JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> AND COUNT(*) = COUNT(t.Type)
> Both above queries are untested. Post CREATE TABLE and INSERT statements
> with test data if you want a tested solution.
>
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Thu, 17 Mar 2005 16:16:24 -0000, Chris Kennedy wrote:
>Where does the Types wanted table come from. Is this like a many to many
>relationship with customers having many invoices and invoices types having
>many invoices?
Hi Chris,
The TypesWanted table is where you (temporarily) store the types you are
looking for. Your question was:[vbcol=seagreen]
To find customer with types 1 and 3 and 5, just run
DELETE FROM TypesWanted; -- No where - everything gets dispatched of.
INSERT TypesWanted (Type) VALUE(1);
INSERT TypesWanted (Type) VALUE(3);
INSERT TypesWanted (Type) VALUE(5);
And then run the query.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label flag. Show all posts
Showing posts with label flag. Show all posts
Friday, February 24, 2012
AND and One to many relationships
I have two tables in a one to many relationship, customers and invoices. The
invoices can be a variety of types marked by an invoice flag column (for
instance 1, 2 or 3). I want to retrieve customers, which only have invoices
of 1 AND 2. If there is an invoice of type 3 I don't want the customer
returned. How do I do this SQLSELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>I have two tables in a one to many relationship, customers and invoices.
>The invoices can be a variety of types marked by an invoice flag column
>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>customer returned. How do I do this SQL
>|||I can't get this to work. If the customer has invoices of type 1 and 2 I
want the customer returned. If i.InvoiceType IN (1,2) in the query and
customer has an invoice type of 3 I don't want the customer returned.
Does this make sense. Thanks.
SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
> news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>|||SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
AND NOT EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType = 3)
?
That gives you customers that have invoice with type either 1 or 2 but don't
have invoices of type 3.
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:u8eMEMjKFHA.2852@.TK2MSFTNGP14.phx.gbl...
>I can't get this to work. If the customer has invoices of type 1 and 2 I
>want the customer returned. If i.InvoiceType IN (1,2) in the query and
>customer has an invoice type of 3 I don't want the customer returned.
> Does this make sense. Thanks.
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>|||select c.*
from customers c
where c.customerid not in (select customerid from invoices where
invoicetype = 3)
if you need other data from the invoice table then just do a join to
it.
i am presuming in this example that customers can have all three types,
but will have the same customerid on each row in the invoice table,
thus if you exclude the customers on an id level, then it will exclude
the ones that have any combination of 3 (1-2-3, 1-3, 2-3, 3)
hth,
hans|||Hi
As I have understood you need the list of customers who have invoices of
type 1 AND 2 so we can't use IN keyword because it is actually a type of OR.
Here is the query (I assumed invoice Type field is char):
select distinct A.idcustomer from
(select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
(select idinvoice,idcustomer from tblinvoice where type='2') B on
A.idcustomer=B.idcustomer
and not exists (select 'true' from tblinvoice C where
C.idcustomer=A.idcustomer and C.type not in ('1','2'))|||Hi this is going in the right direction. To complicate things would there be
any way to make the query more dynamic. Say there were 5 invoices types and
I need to show customers with invoices of invoice type 1 AND 3 but then
wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
"Reza" <Reza@.discussions.microsoft.com> wrote in message
news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
> Hi
> As I have understood you need the list of customers who have invoices of
> type 1 AND 2 so we can't use IN keyword because it is actually a type of
> OR.
> Here is the query (I assumed invoice Type field is char):
> select distinct A.idcustomer from
> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
> (select idinvoice,idcustomer from tblinvoice where type='2') B on
> A.idcustomer=B.idcustomer
> and not exists (select 'true' from tblinvoice C where
> C.idcustomer=A.idcustomer and C.type not in ('1','2'))|||On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>Hi this is going in the right direction. To complicate things would there b
e
>any way to make the query more dynamic. Say there were 5 invoices types and
>I need to show customers with invoices of invoice type 1 AND 3 but then
>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
Hi Chris,
For full flexibility, create an extra table to hold the types you need.
Then, to find all customers with all requested types of invoice plus
possibly others, use
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
INNER JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
And to find all customers with all requested types of invoice, but no
others, you change this to
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
LEFT JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
AND COUNT(*) = COUNT(t.Type)
Both above queries are untested. Post CREATE TABLE and INSERT statements
with test data if you want a tested solution.
>"Reza" <Reza@.discussions.microsoft.com> wrote in message
>news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Where does the Types wanted table come from. Is this like a many to many
relationship with customers having many invoices and invoices types having
many invoices?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:2j4j31lmk3p78au3eookj29outfd7uaei2@.
4ax.com...
> On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>
> Hi Chris,
> For full flexibility, create an extra table to hold the types you need.
> Then, to find all customers with all requested types of invoice plus
> possibly others, use
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> INNER JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> And to find all customers with all requested types of invoice, but no
> others, you change this to
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> LEFT JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> AND COUNT(*) = COUNT(t.Type)
> Both above queries are untested. Post CREATE TABLE and INSERT statements
> with test data if you want a tested solution.
>
>
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 17 Mar 2005 16:16:24 -0000, Chris Kennedy wrote:
>Where does the Types wanted table come from. Is this like a many to many
>relationship with customers having many invoices and invoices types having
>many invoices?
Hi Chris,
The TypesWanted table is where you (temporarily) store the types you are
looking for. Your question was:[vbcol=seagreen]
To find customer with types 1 and 3 and 5, just run
DELETE FROM TypesWanted; -- No where - everything gets dispatched of.
INSERT TypesWanted (Type) VALUE(1);
INSERT TypesWanted (Type) VALUE(3);
INSERT TypesWanted (Type) VALUE(5);
And then run the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
invoices can be a variety of types marked by an invoice flag column (for
instance 1, 2 or 3). I want to retrieve customers, which only have invoices
of 1 AND 2. If there is an invoice of type 3 I don't want the customer
returned. How do I do this SQLSELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>I have two tables in a one to many relationship, customers and invoices.
>The invoices can be a variety of types marked by an invoice flag column
>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>customer returned. How do I do this SQL
>|||I can't get this to work. If the customer has invoices of type 1 and 2 I
want the customer returned. If i.InvoiceType IN (1,2) in the query and
customer has an invoice type of 3 I don't want the customer returned.
Does this make sense. Thanks.
SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
> news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>|||SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
AND NOT EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType = 3)
?
That gives you customers that have invoice with type either 1 or 2 but don't
have invoices of type 3.
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:u8eMEMjKFHA.2852@.TK2MSFTNGP14.phx.gbl...
>I can't get this to work. If the customer has invoices of type 1 and 2 I
>want the customer returned. If i.InvoiceType IN (1,2) in the query and
>customer has an invoice type of 3 I don't want the customer returned.
> Does this make sense. Thanks.
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>|||select c.*
from customers c
where c.customerid not in (select customerid from invoices where
invoicetype = 3)
if you need other data from the invoice table then just do a join to
it.
i am presuming in this example that customers can have all three types,
but will have the same customerid on each row in the invoice table,
thus if you exclude the customers on an id level, then it will exclude
the ones that have any combination of 3 (1-2-3, 1-3, 2-3, 3)
hth,
hans|||Hi
As I have understood you need the list of customers who have invoices of
type 1 AND 2 so we can't use IN keyword because it is actually a type of OR.
Here is the query (I assumed invoice Type field is char):
select distinct A.idcustomer from
(select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
(select idinvoice,idcustomer from tblinvoice where type='2') B on
A.idcustomer=B.idcustomer
and not exists (select 'true' from tblinvoice C where
C.idcustomer=A.idcustomer and C.type not in ('1','2'))|||Hi this is going in the right direction. To complicate things would there be
any way to make the query more dynamic. Say there were 5 invoices types and
I need to show customers with invoices of invoice type 1 AND 3 but then
wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
"Reza" <Reza@.discussions.microsoft.com> wrote in message
news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
> Hi
> As I have understood you need the list of customers who have invoices of
> type 1 AND 2 so we can't use IN keyword because it is actually a type of
> OR.
> Here is the query (I assumed invoice Type field is char):
> select distinct A.idcustomer from
> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
> (select idinvoice,idcustomer from tblinvoice where type='2') B on
> A.idcustomer=B.idcustomer
> and not exists (select 'true' from tblinvoice C where
> C.idcustomer=A.idcustomer and C.type not in ('1','2'))|||On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>Hi this is going in the right direction. To complicate things would there b
e
>any way to make the query more dynamic. Say there were 5 invoices types and
>I need to show customers with invoices of invoice type 1 AND 3 but then
>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
Hi Chris,
For full flexibility, create an extra table to hold the types you need.
Then, to find all customers with all requested types of invoice plus
possibly others, use
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
INNER JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
And to find all customers with all requested types of invoice, but no
others, you change this to
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
LEFT JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
AND COUNT(*) = COUNT(t.Type)
Both above queries are untested. Post CREATE TABLE and INSERT statements
with test data if you want a tested solution.
>"Reza" <Reza@.discussions.microsoft.com> wrote in message
>news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Where does the Types wanted table come from. Is this like a many to many
relationship with customers having many invoices and invoices types having
many invoices?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:2j4j31lmk3p78au3eookj29outfd7uaei2@.
4ax.com...
> On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>
> Hi Chris,
> For full flexibility, create an extra table to hold the types you need.
> Then, to find all customers with all requested types of invoice plus
> possibly others, use
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> INNER JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> And to find all customers with all requested types of invoice, but no
> others, you change this to
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> LEFT JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> AND COUNT(*) = COUNT(t.Type)
> Both above queries are untested. Post CREATE TABLE and INSERT statements
> with test data if you want a tested solution.
>
>
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 17 Mar 2005 16:16:24 -0000, Chris Kennedy wrote:
>Where does the Types wanted table come from. Is this like a many to many
>relationship with customers having many invoices and invoices types having
>many invoices?
Hi Chris,
The TypesWanted table is where you (temporarily) store the types you are
looking for. Your question was:[vbcol=seagreen]
To find customer with types 1 and 3 and 5, just run
DELETE FROM TypesWanted; -- No where - everything gets dispatched of.
INSERT TypesWanted (Type) VALUE(1);
INSERT TypesWanted (Type) VALUE(3);
INSERT TypesWanted (Type) VALUE(5);
And then run the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
AND and One to many relationships
I have two tables in a one to many relationship, customers and invoices. The
invoices can be a variety of types marked by an invoice flag column (for
instance 1, 2 or 3). I want to retrieve customers, which only have invoices
of 1 AND 2. If there is an invoice of type 3 I don't want the customer
returned. How do I do this SQLSELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
--
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>I have two tables in a one to many relationship, customers and invoices.
>The invoices can be a variety of types marked by an invoice flag column
>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>customer returned. How do I do this SQL
>|||I can't get this to work. If the customer has invoices of type 1 and 2 I
want the customer returned. If i.InvoiceType IN (1,2) in the query and
customer has an invoice type of 3 I don't want the customer returned.
Does this make sense. Thanks.
SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
> news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>>I have two tables in a one to many relationship, customers and invoices.
>>The invoices can be a variety of types marked by an invoice flag column
>>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>>customer returned. How do I do this SQL
>|||SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
AND NOT EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType = 3)
?
That gives you customers that have invoice with type either 1 or 2 but don't
have invoices of type 3.
--
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:u8eMEMjKFHA.2852@.TK2MSFTNGP14.phx.gbl...
>I can't get this to work. If the customer has invoices of type 1 and 2 I
>want the customer returned. If i.InvoiceType IN (1,2) in the query and
>customer has an invoice type of 3 I don't want the customer returned.
> Does this make sense. Thanks.
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>> SELECT <column list>
>> FROM Customers c
>> WHERE EXISTS
>> (SELECT NULL FROM Invoices i
>> WHERE c.CustomerID = i.CustomerID
>> AND i.InvoiceType IN (1,2))
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
>> news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>>I have two tables in a one to many relationship, customers and invoices.
>>The invoices can be a variety of types marked by an invoice flag column
>>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>>customer returned. How do I do this SQL
>>
>|||select c.*
from customers c
where c.customerid not in (select customerid from invoices where
invoicetype = 3)
if you need other data from the invoice table then just do a join to
it.
i am presuming in this example that customers can have all three types,
but will have the same customerid on each row in the invoice table,
thus if you exclude the customers on an id level, then it will exclude
the ones that have any combination of 3 (1-2-3, 1-3, 2-3, 3)
hth,
hans|||Hi
As I have understood you need the list of customers who have invoices of
type 1 AND 2 so we can't use IN keyword because it is actually a type of OR.
Here is the query (I assumed invoice Type field is char):
select distinct A.idcustomer from
(select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
(select idinvoice,idcustomer from tblinvoice where type='2') B on
A.idcustomer=B.idcustomer
and not exists (select 'true' from tblinvoice C where
C.idcustomer=A.idcustomer and C.type not in ('1','2'))|||Hi this is going in the right direction. To complicate things would there be
any way to make the query more dynamic. Say there were 5 invoices types and
I need to show customers with invoices of invoice type 1 AND 3 but then
wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
"Reza" <Reza@.discussions.microsoft.com> wrote in message
news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
> Hi
> As I have understood you need the list of customers who have invoices of
> type 1 AND 2 so we can't use IN keyword because it is actually a type of
> OR.
> Here is the query (I assumed invoice Type field is char):
> select distinct A.idcustomer from
> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
> (select idinvoice,idcustomer from tblinvoice where type='2') B on
> A.idcustomer=B.idcustomer
> and not exists (select 'true' from tblinvoice C where
> C.idcustomer=A.idcustomer and C.type not in ('1','2'))|||On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>Hi this is going in the right direction. To complicate things would there be
>any way to make the query more dynamic. Say there were 5 invoices types and
>I need to show customers with invoices of invoice type 1 AND 3 but then
>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
Hi Chris,
For full flexibility, create an extra table to hold the types you need.
Then, to find all customers with all requested types of invoice plus
possibly others, use
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
INNER JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
And to find all customers with all requested types of invoice, but no
others, you change this to
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
LEFT JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
AND COUNT(*) = COUNT(t.Type)
Both above queries are untested. Post CREATE TABLE and INSERT statements
with test data if you want a tested solution.
>"Reza" <Reza@.discussions.microsoft.com> wrote in message
>news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
>> Hi
>> As I have understood you need the list of customers who have invoices of
>> type 1 AND 2 so we can't use IN keyword because it is actually a type of
>> OR.
>> Here is the query (I assumed invoice Type field is char):
>> select distinct A.idcustomer from
>> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
>> (select idinvoice,idcustomer from tblinvoice where type='2') B on
>> A.idcustomer=B.idcustomer
>> and not exists (select 'true' from tblinvoice C where
>> C.idcustomer=A.idcustomer and C.type not in ('1','2'))
>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Where does the Types wanted table come from. Is this like a many to many
relationship with customers having many invoices and invoices types having
many invoices?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:2j4j31lmk3p78au3eookj29outfd7uaei2@.4ax.com...
> On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>>Hi this is going in the right direction. To complicate things would there
>>be
>>any way to make the query more dynamic. Say there were 5 invoices types
>>and
>>I need to show customers with invoices of invoice type 1 AND 3 but then
>>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
> Hi Chris,
> For full flexibility, create an extra table to hold the types you need.
> Then, to find all customers with all requested types of invoice plus
> possibly others, use
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> INNER JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> And to find all customers with all requested types of invoice, but no
> others, you change this to
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> LEFT JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> AND COUNT(*) = COUNT(t.Type)
> Both above queries are untested. Post CREATE TABLE and INSERT statements
> with test data if you want a tested solution.
>
>>"Reza" <Reza@.discussions.microsoft.com> wrote in message
>>news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
>> Hi
>> As I have understood you need the list of customers who have invoices of
>> type 1 AND 2 so we can't use IN keyword because it is actually a type of
>> OR.
>> Here is the query (I assumed invoice Type field is char):
>> select distinct A.idcustomer from
>> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner
>> join
>> (select idinvoice,idcustomer from tblinvoice where type='2') B on
>> A.idcustomer=B.idcustomer
>> and not exists (select 'true' from tblinvoice C where
>> C.idcustomer=A.idcustomer and C.type not in ('1','2'))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 17 Mar 2005 16:16:24 -0000, Chris Kennedy wrote:
>Where does the Types wanted table come from. Is this like a many to many
>relationship with customers having many invoices and invoices types having
>many invoices?
Hi Chris,
The TypesWanted table is where you (temporarily) store the types you are
looking for. Your question was:
>>I need to show customers with invoices of invoice type 1 AND 3 but then
>>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
To find customer with types 1 and 3 and 5, just run
DELETE FROM TypesWanted; -- No where - everything gets dispatched of.
INSERT TypesWanted (Type) VALUE(1);
INSERT TypesWanted (Type) VALUE(3);
INSERT TypesWanted (Type) VALUE(5);
And then run the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Works great. Cheers.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e23k311ik0teriodjc3ik2u6jie07kh8j4@.4ax.com...
> On Thu, 17 Mar 2005 16:16:24 -0000, Chris Kennedy wrote:
>>Where does the Types wanted table come from. Is this like a many to many
>>relationship with customers having many invoices and invoices types having
>>many invoices?
> Hi Chris,
> The TypesWanted table is where you (temporarily) store the types you are
> looking for. Your question was:
>>I need to show customers with invoices of invoice type 1 AND 3 but then
>>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
> To find customer with types 1 and 3 and 5, just run
> DELETE FROM TypesWanted; -- No where - everything gets dispatched of.
> INSERT TypesWanted (Type) VALUE(1);
> INSERT TypesWanted (Type) VALUE(3);
> INSERT TypesWanted (Type) VALUE(5);
> And then run the query.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
invoices can be a variety of types marked by an invoice flag column (for
instance 1, 2 or 3). I want to retrieve customers, which only have invoices
of 1 AND 2. If there is an invoice of type 3 I don't want the customer
returned. How do I do this SQLSELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
--
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>I have two tables in a one to many relationship, customers and invoices.
>The invoices can be a variety of types marked by an invoice flag column
>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>customer returned. How do I do this SQL
>|||I can't get this to work. If the customer has invoices of type 1 and 2 I
want the customer returned. If i.InvoiceType IN (1,2) in the query and
customer has an invoice type of 3 I don't want the customer returned.
Does this make sense. Thanks.
SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
> news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>>I have two tables in a one to many relationship, customers and invoices.
>>The invoices can be a variety of types marked by an invoice flag column
>>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>>customer returned. How do I do this SQL
>|||SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
AND NOT EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType = 3)
?
That gives you customers that have invoice with type either 1 or 2 but don't
have invoices of type 3.
--
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:u8eMEMjKFHA.2852@.TK2MSFTNGP14.phx.gbl...
>I can't get this to work. If the customer has invoices of type 1 and 2 I
>want the customer returned. If i.InvoiceType IN (1,2) in the query and
>customer has an invoice type of 3 I don't want the customer returned.
> Does this make sense. Thanks.
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>> SELECT <column list>
>> FROM Customers c
>> WHERE EXISTS
>> (SELECT NULL FROM Invoices i
>> WHERE c.CustomerID = i.CustomerID
>> AND i.InvoiceType IN (1,2))
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
>> news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>>I have two tables in a one to many relationship, customers and invoices.
>>The invoices can be a variety of types marked by an invoice flag column
>>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>>customer returned. How do I do this SQL
>>
>|||select c.*
from customers c
where c.customerid not in (select customerid from invoices where
invoicetype = 3)
if you need other data from the invoice table then just do a join to
it.
i am presuming in this example that customers can have all three types,
but will have the same customerid on each row in the invoice table,
thus if you exclude the customers on an id level, then it will exclude
the ones that have any combination of 3 (1-2-3, 1-3, 2-3, 3)
hth,
hans|||Hi
As I have understood you need the list of customers who have invoices of
type 1 AND 2 so we can't use IN keyword because it is actually a type of OR.
Here is the query (I assumed invoice Type field is char):
select distinct A.idcustomer from
(select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
(select idinvoice,idcustomer from tblinvoice where type='2') B on
A.idcustomer=B.idcustomer
and not exists (select 'true' from tblinvoice C where
C.idcustomer=A.idcustomer and C.type not in ('1','2'))|||Hi this is going in the right direction. To complicate things would there be
any way to make the query more dynamic. Say there were 5 invoices types and
I need to show customers with invoices of invoice type 1 AND 3 but then
wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
"Reza" <Reza@.discussions.microsoft.com> wrote in message
news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
> Hi
> As I have understood you need the list of customers who have invoices of
> type 1 AND 2 so we can't use IN keyword because it is actually a type of
> OR.
> Here is the query (I assumed invoice Type field is char):
> select distinct A.idcustomer from
> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
> (select idinvoice,idcustomer from tblinvoice where type='2') B on
> A.idcustomer=B.idcustomer
> and not exists (select 'true' from tblinvoice C where
> C.idcustomer=A.idcustomer and C.type not in ('1','2'))|||On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>Hi this is going in the right direction. To complicate things would there be
>any way to make the query more dynamic. Say there were 5 invoices types and
>I need to show customers with invoices of invoice type 1 AND 3 but then
>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
Hi Chris,
For full flexibility, create an extra table to hold the types you need.
Then, to find all customers with all requested types of invoice plus
possibly others, use
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
INNER JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
And to find all customers with all requested types of invoice, but no
others, you change this to
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
LEFT JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
AND COUNT(*) = COUNT(t.Type)
Both above queries are untested. Post CREATE TABLE and INSERT statements
with test data if you want a tested solution.
>"Reza" <Reza@.discussions.microsoft.com> wrote in message
>news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
>> Hi
>> As I have understood you need the list of customers who have invoices of
>> type 1 AND 2 so we can't use IN keyword because it is actually a type of
>> OR.
>> Here is the query (I assumed invoice Type field is char):
>> select distinct A.idcustomer from
>> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
>> (select idinvoice,idcustomer from tblinvoice where type='2') B on
>> A.idcustomer=B.idcustomer
>> and not exists (select 'true' from tblinvoice C where
>> C.idcustomer=A.idcustomer and C.type not in ('1','2'))
>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Where does the Types wanted table come from. Is this like a many to many
relationship with customers having many invoices and invoices types having
many invoices?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:2j4j31lmk3p78au3eookj29outfd7uaei2@.4ax.com...
> On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>>Hi this is going in the right direction. To complicate things would there
>>be
>>any way to make the query more dynamic. Say there were 5 invoices types
>>and
>>I need to show customers with invoices of invoice type 1 AND 3 but then
>>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
> Hi Chris,
> For full flexibility, create an extra table to hold the types you need.
> Then, to find all customers with all requested types of invoice plus
> possibly others, use
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> INNER JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> And to find all customers with all requested types of invoice, but no
> others, you change this to
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> LEFT JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> AND COUNT(*) = COUNT(t.Type)
> Both above queries are untested. Post CREATE TABLE and INSERT statements
> with test data if you want a tested solution.
>
>>"Reza" <Reza@.discussions.microsoft.com> wrote in message
>>news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
>> Hi
>> As I have understood you need the list of customers who have invoices of
>> type 1 AND 2 so we can't use IN keyword because it is actually a type of
>> OR.
>> Here is the query (I assumed invoice Type field is char):
>> select distinct A.idcustomer from
>> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner
>> join
>> (select idinvoice,idcustomer from tblinvoice where type='2') B on
>> A.idcustomer=B.idcustomer
>> and not exists (select 'true' from tblinvoice C where
>> C.idcustomer=A.idcustomer and C.type not in ('1','2'))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 17 Mar 2005 16:16:24 -0000, Chris Kennedy wrote:
>Where does the Types wanted table come from. Is this like a many to many
>relationship with customers having many invoices and invoices types having
>many invoices?
Hi Chris,
The TypesWanted table is where you (temporarily) store the types you are
looking for. Your question was:
>>I need to show customers with invoices of invoice type 1 AND 3 but then
>>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
To find customer with types 1 and 3 and 5, just run
DELETE FROM TypesWanted; -- No where - everything gets dispatched of.
INSERT TypesWanted (Type) VALUE(1);
INSERT TypesWanted (Type) VALUE(3);
INSERT TypesWanted (Type) VALUE(5);
And then run the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Works great. Cheers.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e23k311ik0teriodjc3ik2u6jie07kh8j4@.4ax.com...
> On Thu, 17 Mar 2005 16:16:24 -0000, Chris Kennedy wrote:
>>Where does the Types wanted table come from. Is this like a many to many
>>relationship with customers having many invoices and invoices types having
>>many invoices?
> Hi Chris,
> The TypesWanted table is where you (temporarily) store the types you are
> looking for. Your question was:
>>I need to show customers with invoices of invoice type 1 AND 3 but then
>>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
> To find customer with types 1 and 3 and 5, just run
> DELETE FROM TypesWanted; -- No where - everything gets dispatched of.
> INSERT TypesWanted (Type) VALUE(1);
> INSERT TypesWanted (Type) VALUE(3);
> INSERT TypesWanted (Type) VALUE(5);
> And then run the query.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Sunday, February 19, 2012
Analyzing Error log with Trace Flag 1204 turned on
Hello, I have what I believe should be a fairly simple question. I have a
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.
The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.
|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16...
10/30/04 22:16
10/30/04 22:16Wait-for graph
10/30/04 22:16
10/30/04 22:16Node:1
10/30/04 22:16KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16Wait List:
10/30/04 22:16Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec
0x73377558) Value:0x5b0
10/30/04 22:16
10/30/04 22:16Node:2
10/30/04 22:16KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16Grant List 0::
10/30/04 22:16Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) Value:0x23a
10/30/04 22:16
10/30/04 22:16Node:3
10/30/04 22:16KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16Grant List 1::
10/30/04 22:16Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec0x51DF7558) Value:0x3b4
10/30/04 22:16Victim Resource Owner:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) Value:0x23a
I do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the line
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:
> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>
>
|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...[vbcol=seagreen]
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec0x73377558) Value:0x5b0
> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec0x5182B558) Value:0x23a
> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec0x51DF7558) Value:0x3b4
> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec0x5182B558) Value:0x23a
> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the real
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that led
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
>
>
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.
The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.
|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16...
10/30/04 22:16
10/30/04 22:16Wait-for graph
10/30/04 22:16
10/30/04 22:16Node:1
10/30/04 22:16KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16Wait List:
10/30/04 22:16Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec
0x73377558) Value:0x5b010/30/04 22:16
10/30/04 22:16Node:2
10/30/04 22:16KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16Grant List 0::
10/30/04 22:16Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec
0x5182B558) Value:0x23a10/30/04 22:16
10/30/04 22:16Node:3
10/30/04 22:16KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16Grant List 1::
10/30/04 22:16Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec
0x51DF7558) Value:0x3b410/30/04 22:16Victim Resource Owner:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec
0x5182B558) Value:0x23aI do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the line
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:
> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>
>
|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...[vbcol=seagreen]
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec
0x73377558) Value:0x5b0> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec
0x5182B558) Value:0x23a> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec
0x51DF7558) Value:0x3b4> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec
0x5182B558) Value:0x23a> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the real
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that led
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
>
>
Analyzing Error log with Trace Flag 1204 turned on
Hello, I have what I believe should be a fairly simple question. I have a
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16 ...
10/30/04 22:16
10/30/04 22:16 Wait-for graph
10/30/04 22:16
10/30/04 22:16 Node:1
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Wait List:
10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec:(0x73377558) Value:0x5b0
10/30/04 22:16
10/30/04 22:16 Node:2
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 0::
10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec:(0x5182B558) Value:0x23a
10/30/04 22:16
10/30/04 22:16 Node:3
10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 1::
10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec:(0x51DF7558) Value:0x3b4
10/30/04 22:16 Victim Resource Owner:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec:(0x5182B558) Value:0x23a
I do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the line
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:
> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> >
> > Hello, I have what I believe should be a fairly simple question. I have a
> > server with trace flag 1204 turned on. I have entries in this log that
> > show
> > deadlock information. i'm looking for information about how to analyze
> > the
> > data in this log. It contains a log of information about things such as
> > Grant lists, keys, owners etc. I need information to explain what I'm
> > looking at.
>
>|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec:(0x73377558) Value:0x5b0
> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec:(0x5182B558) Value:0x23a
> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec:(0x51DF7558) Value:0x3b4
> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec:(0x5182B558) Value:0x23a
> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
>> The article "Troubleshooting Deadlocks" in Books Online explains what all
>> these terms mean.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in
>> message
>> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>> >
>> > Hello, I have what I believe should be a fairly simple question. I
>> > have a
>> > server with trace flag 1204 turned on. I have entries in this log that
>> > show
>> > deadlock information. i'm looking for information about how to analyze
>> > the
>> > data in this log. It contains a log of information about things such
>> > as
>> > Grant lists, keys, owners etc. I need information to explain what I'm
>> > looking at.
>>|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the real
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that led
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
> >
> > Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> > Here is an snippet from my error log:
> >
> > 10/30/04 22:16 ...
> > 10/30/04 22:16
> > 10/30/04 22:16 Wait-for graph
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:1
> > 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Wait List:
> > 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> > SPID:79 ECID:0
> > 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> > 10/30/04 22:16 Input Buf: RPC Event:
> > app_ProductUnit_RetrieveProductUnitData;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> > Ec:(0x73377558) Value:0x5b0
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:2
> > 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Grant List 0::
> > 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:77 ECID:0
> > 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> > 10/30/04 22:16 Input Buf: RPC Event:
> > app_ProductUnitAssoc_RetrieveTargetData;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> > Ec:(0x5182B558) Value:0x23a
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:3
> > 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Grant List 1::
> > 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:71 ECID:0
> > 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> > 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> > Ec:(0x51DF7558) Value:0x3b4
> > 10/30/04 22:16 Victim Resource Owner:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> > Ec:(0x5182B558) Value:0x23a
> >
> > I do not see enough information in BOL around what the information on the
> > lines that start with KEY represent. Are they locks currently held or
> > locks
> > requested? Also I do not understand what information is included on the
> > line
> > starting with ResType (i.e. what are ResType and Stype) I'm finding it
> > very
> > challenging to look at this log and deduce the sequence of the calls and
> > locks that led to my deadlock.
> >
> > If you have any further advice or links I'd appreciate it.
> >
> > Mike
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> The article "Troubleshooting Deadlocks" in Books Online explains what all
> >> these terms mean.
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in
> >> message
> >> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> >> >
> >> > Hello, I have what I believe should be a fairly simple question. I
> >> > have a
> >> > server with trace flag 1204 turned on. I have entries in this log that
> >> > show
> >> > deadlock information. i'm looking for information about how to analyze
> >> > the
> >> > data in this log. It contains a log of information about things such
> >> > as
> >> > Grant lists, keys, owners etc. I need information to explain what I'm
> >> > looking at.
> >>
> >>
> >>
>
>
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16 ...
10/30/04 22:16
10/30/04 22:16 Wait-for graph
10/30/04 22:16
10/30/04 22:16 Node:1
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Wait List:
10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec:(0x73377558) Value:0x5b0
10/30/04 22:16
10/30/04 22:16 Node:2
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 0::
10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec:(0x5182B558) Value:0x23a
10/30/04 22:16
10/30/04 22:16 Node:3
10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 1::
10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec:(0x51DF7558) Value:0x3b4
10/30/04 22:16 Victim Resource Owner:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec:(0x5182B558) Value:0x23a
I do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the line
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:
> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> >
> > Hello, I have what I believe should be a fairly simple question. I have a
> > server with trace flag 1204 turned on. I have entries in this log that
> > show
> > deadlock information. i'm looking for information about how to analyze
> > the
> > data in this log. It contains a log of information about things such as
> > Grant lists, keys, owners etc. I need information to explain what I'm
> > looking at.
>
>|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec:(0x73377558) Value:0x5b0
> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec:(0x5182B558) Value:0x23a
> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec:(0x51DF7558) Value:0x3b4
> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec:(0x5182B558) Value:0x23a
> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
>> The article "Troubleshooting Deadlocks" in Books Online explains what all
>> these terms mean.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in
>> message
>> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>> >
>> > Hello, I have what I believe should be a fairly simple question. I
>> > have a
>> > server with trace flag 1204 turned on. I have entries in this log that
>> > show
>> > deadlock information. i'm looking for information about how to analyze
>> > the
>> > data in this log. It contains a log of information about things such
>> > as
>> > Grant lists, keys, owners etc. I need information to explain what I'm
>> > looking at.
>>|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the real
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that led
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
> >
> > Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> > Here is an snippet from my error log:
> >
> > 10/30/04 22:16 ...
> > 10/30/04 22:16
> > 10/30/04 22:16 Wait-for graph
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:1
> > 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Wait List:
> > 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> > SPID:79 ECID:0
> > 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> > 10/30/04 22:16 Input Buf: RPC Event:
> > app_ProductUnit_RetrieveProductUnitData;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> > Ec:(0x73377558) Value:0x5b0
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:2
> > 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Grant List 0::
> > 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:77 ECID:0
> > 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> > 10/30/04 22:16 Input Buf: RPC Event:
> > app_ProductUnitAssoc_RetrieveTargetData;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> > Ec:(0x5182B558) Value:0x23a
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:3
> > 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Grant List 1::
> > 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:71 ECID:0
> > 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> > 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> > Ec:(0x51DF7558) Value:0x3b4
> > 10/30/04 22:16 Victim Resource Owner:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> > Ec:(0x5182B558) Value:0x23a
> >
> > I do not see enough information in BOL around what the information on the
> > lines that start with KEY represent. Are they locks currently held or
> > locks
> > requested? Also I do not understand what information is included on the
> > line
> > starting with ResType (i.e. what are ResType and Stype) I'm finding it
> > very
> > challenging to look at this log and deduce the sequence of the calls and
> > locks that led to my deadlock.
> >
> > If you have any further advice or links I'd appreciate it.
> >
> > Mike
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> The article "Troubleshooting Deadlocks" in Books Online explains what all
> >> these terms mean.
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in
> >> message
> >> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> >> >
> >> > Hello, I have what I believe should be a fairly simple question. I
> >> > have a
> >> > server with trace flag 1204 turned on. I have entries in this log that
> >> > show
> >> > deadlock information. i'm looking for information about how to analyze
> >> > the
> >> > data in this log. It contains a log of information about things such
> >> > as
> >> > Grant lists, keys, owners etc. I need information to explain what I'm
> >> > looking at.
> >>
> >>
> >>
>
>
Analyzing Error log with Trace Flag 1204 turned on
Hello, I have what I believe should be a fairly simple question. I have a
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16 ...
10/30/04 22:16
10/30/04 22:16 Wait-for graph
10/30/04 22:16
10/30/04 22:16 Node:1
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Wait List:
10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData
;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec0x73377558) Value:0x5b0
10/30/04 22:16
10/30/04 22:16 Node:2
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 0::
10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData
;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) Value:0x23a
10/30/04 22:16
10/30/04 22:16 Node:3
10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 1::
10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec0x51DF7558) Value:0x3b4
10/30/04 22:16 Victim Resource Owner:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) Value:0x23a
I do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the lin
e
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:
> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in messa
ge
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>
>|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...[vbcol=seagreen]
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;
1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec0x73377558) Value:0x5b0
> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;
1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec0x5182B558) Value:0x23a
> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec0x51DF7558) Value:0x3b4
> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec0x5182B558) Value:0x23a
> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
>|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have
a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the re
al
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that l
ed
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in messa
ge
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
>
>
server with trace flag 1204 turned on. I have entries in this log that show
deadlock information. i'm looking for information about how to analyze the
data in this log. It contains a log of information about things such as
Grant lists, keys, owners etc. I need information to explain what I'm
looking at.The article "Troubleshooting Deadlocks" in Books Online explains what all
these terms mean.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
> Hello, I have what I believe should be a fairly simple question. I have a
> server with trace flag 1204 turned on. I have entries in this log that
> show
> deadlock information. i'm looking for information about how to analyze
> the
> data in this log. It contains a log of information about things such as
> Grant lists, keys, owners etc. I need information to explain what I'm
> looking at.|||Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
Here is an snippet from my error log:
10/30/04 22:16 ...
10/30/04 22:16
10/30/04 22:16 Wait-for graph
10/30/04 22:16
10/30/04 22:16 Node:1
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Wait List:
10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData
;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec
0x73377558) Value:0x5b010/30/04 22:16
10/30/04 22:16 Node:2
10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 0::
10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16 Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData
;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec
0x5182B558) Value:0x23a10/30/04 22:16
10/30/04 22:16 Node:3
10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16 Grant List 1::
10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16 Requested By:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec
0x51DF7558) Value:0x3b410/30/04 22:16 Victim Resource Owner:
10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec
0x5182B558) Value:0x23aI do not see enough information in BOL around what the information on the
lines that start with KEY represent. Are they locks currently held or locks
requested? Also I do not understand what information is included on the lin
e
starting with ResType (i.e. what are ResType and Stype) I'm finding it very
challenging to look at this log and deduce the sequence of the calls and
locks that led to my deadlock.
If you have any further advice or links I'd appreciate it.
Mike
"Kalen Delaney" wrote:
> The article "Troubleshooting Deadlocks" in Books Online explains what all
> these terms mean.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in messa
ge
> news:45642E4E-84C6-4DBE-99C9-3008291F3160@.microsoft.com...
>
>|||Hi Mike, and thanks, :-)
If the truth be known, I usually don't use the traceflag output for the real
troubleshooting. If I am trying to track down deadlocks, I hae this flag
enabled, and I also have a trace running to capture deadlock events. I use
this traceflag output only to get the spids and the time, and then I can
find what I need in the trace output, which shows me the statements that led
up to the deadlock. Usually that's enough to figure it out.
The keys can be either the ones being waited on or the ones requested. It
depends where in the output the line occurs.
I have quite a bit of info on interpreting this output and understand lock
resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
Locking and Blocking.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in message
news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...[vbcol=seagreen]
> Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> Here is an snippet from my error log:
> 10/30/04 22:16 ...
> 10/30/04 22:16
> 10/30/04 22:16 Wait-for graph
> 10/30/04 22:16
> 10/30/04 22:16 Node:1
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Wait List:
> 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> SPID:79 ECID:0
> 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnit_RetrieveProductUnitData;
1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> Ec
0x73377558) Value:0x5b0> 10/30/04 22:16
> 10/30/04 22:16 Node:2
> 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 0::
> 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:77 ECID:0
> 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> 10/30/04 22:16 Input Buf: RPC Event:
> app_ProductUnitAssoc_RetrieveTargetData;
1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec
0x5182B558) Value:0x23a> 10/30/04 22:16
> 10/30/04 22:16 Node:3
> 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> Flags:
> 0x0
> 10/30/04 22:16 Grant List 1::
> 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:71 ECID:0
> 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> 10/30/04 22:16 Requested By:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> Ec
0x51DF7558) Value:0x3b4> 10/30/04 22:16 Victim Resource Owner:
> 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> Ec
0x5182B558) Value:0x23a> I do not see enough information in BOL around what the information on the
> lines that start with KEY represent. Are they locks currently held or
> locks
> requested? Also I do not understand what information is included on the
> line
> starting with ResType (i.e. what are ResType and Stype) I'm finding it
> very
> challenging to look at this log and deduce the sequence of the calls and
> locks that led to my deadlock.
> If you have any further advice or links I'd appreciate it.
> Mike
>
> "Kalen Delaney" wrote:
>|||Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have
a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this...is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
> If the truth be known, I usually don't use the traceflag output for the re
al
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that l
ed
> up to the deadlock. Usually that's enough to figure it out.
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "MikeInOakville" <MikeInOakville@.discussions.microsoft.com> wrote in messa
ge
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)