Help on query needed

Ok guys.

Lets say i have 2 tables. One called 'Leads' and the other called 'Sales_Reps'. When a new lead comes in would like to assign it to the sales rep who has the least number of active leads. If there is a tie, I'll just assign it alphabetically between the sales reps who are tied with the least.

Can someone point me in the right direction here? I would assume I'd need to do some sort of select count, but I'm not sure how to begin.

Thanks!basic idea is below. I had to make some assumptions about your table DDL obviously, since you didn't provide it.

select top 1
sr.sales_rep_id, count(*)
from sales_reps sr
inner join leads l on l.sales_rep_id=sr.sales_rep_id
group by sr.sales_rep_id
order by count(*) asc|||Sorry. Let me be a bit more descriptive. I havent built the tables yet, but here's what I was thinking.

Sales Rep Table
Sales_Rep_ID
Sales_Rep_Name
Sales_Group

Leads Table
Lead_ID
Customer_Name
Customer_Phone
Lead_Description
Sales_Rep_Name
Sales_Rep_ID

When the lead comes in, the Sales_Rep_Name field will either be blank, or have a default value in it. Then, I'd like to have SQL automatically assign the sales rep like I mentioned in the initial post, but (I guess) using an insert trigger.

Does this help?|||if those are your tables, then the query i posted should work.

btw, you should get rid of the Leads.Sales_Rep_Name column. it's redundant and breaks normalization as it's already in Sales_Rep.

also I wouldn't use a trigger. assuming you are inserting leads using a sproc, you can have that sproc assign the lead as well.|||OK. That helps alot. I'm pretty new to SQL, so here's another quick question if you don't mind..

The application that is managing all my leads is a proprietary app and I don't have the ability to call a sproc directly from the app. Therefore, I thought that the trigger was the way to go. I assumed the sproc must be called by the app, and that it can't invoke itself, am I right?|||if the app is calling a sproc, are you in control of the sproc? if so you could modify it to assign a lead to a sales rep.

If the app is inserting directly into a table with an ad-hoc insert statement, then you'll have to use a trigger I guess.

it sounds like you need to understand your situation better though, and figure out which of the two cases above is happening. or something else entirely perhaps.|||I'm definitely not in control of how the application is inserting the value into the table, so it sounds like the trigger is the way to go.|||not necessarily. if you own the database, and the app is calling a sproc, then you can just modify the sproc and there is no need of a trigger.

stored procedures live in sql server, not in your client app.|||Gotcha. Makes sense. Thanks for your help, and your patience. You've taught me alot.

Help on a SQL query

Hello guys,
maybe you can point me into the right direction on this SQL Query that
is driving me crazy:

I've got the following table:

Contact Company Job_Title Importance
1 Company A CEO 10
2 Company A DIR 9
3 Company B MAN 5
4 Company B DIR 9
5 Company C PRG 4

I need to extract a single value, for each company, with the highest
contact (through the "importance" field).

In the example, I would need to have the following results:

Contact Company Job_Title Importance
1 Company A CEO 10
4 Company B DIR 9
5 Company C PRG 4

...any idea about how can i do it ?

Thanks very much fo your help
MassiHere's one option:

select
t.Contact,
t.Company,
t.Job_Title,
t.Importance
from
dbo.MyTable t
join
(
select Company, max(Importance) as 'Importance'
from dbo.MyTable
) dt
on t.Company = dt.Company
and t.Importance = dt.Importance

Simon|||Unfortunately is not working ...

It returns the following result set:

Contact Company
Job Title Importance
10000000000016374721000000000001587309CCM200
10000000000016374721000000000001587309CCM200
10000000000016374721000000000001587309CCM200
10000000000016374721000000000001587309CCM200

I need the company field to be unique in the resultset. Moreover, I
don't understand where it has taken the value 200 for importance (on DB
values range from 0 to 170)...

Still thanks for your help
Massi|||group by is missing:

Here's one option:

select
t.Contact,
t.Company,
t.Job_Title,
t.Importance
from
dbo.MyTable t
join
(
select Company, max(Importance) as 'Importance'
from dbo.MyTable
-----
GROUP BY COMPANY
----
) dt
on t.Company = dt.Company
and t.Importance = dt.Importance

Simon|||Oops, sorry - I left out the GROUP BY:

select
t.Contact,
t.Company,
t.Job_Title,
t.Importance
from
dbo.MyTable t
join
(
select Company, max(Importance) as 'Importance'
from dbo.MyTable
group by Company
) dt
on t.Company = dt.Company
and t.Importance = dt.Importance

If this still doesn't work as you require, I suggest you post CREATE
TABLE and INSERT statements to show a test case:

http://www.aspfaq.com/etiquette.asp?id=5006

Simonsql

Help needed!

Hello guys,
I'm new to the forum and to MS SQL 2K.
I'm trying to a merge similar rows in a table into a single row and put them in a new table.

Example:-
This is my input table
TableA
ID A B C
--------
1 jk kl bj
2 sd we op
3 io po kl
1 ui gh ew
2 kl re op
1 qw kj nn

My output table should look like this
TableB
ID A1 B1 C1 A2 B2 C2 A3 B3 C3
----------------
1 jk kl bj ui gh ew qw kj nn
2 sd we op kl re op
3 io po kl

Please help me on how to create my output.
Thanks in advance,

Sid.You want help violating the rules of normalization? Should I buy you a carton of cigarettes while I'm at it? Neither activity is healthy.

Seriously, if you must do this it is important to know whether the number of records for an ID is fixed or not. If it can be any number then you are not going to be able to define the columns on your output table ahead of time and you are left with a messy dynamic query task. If there is a limit on the number of records per ID then your problem is merely a moderately difficult cross-tab query.|||normalization or not
this is a good exercise to displace data
kinda like playing scales before you actually play a song on an instrument

i will be working on this tonight|||I'll check this query today ...|||giving up

I don't understand to purpose of this query|||i now have a headache|||There's not much point in pursuing this without further clarification from coolhandsid, so save the Tylenol.|||I've got

Table1
ID | A | B | C
1 | 2 | 3 | 4
2 | 9 | 4 | 5
3 | 22| 53 94

I want this result

Table1
ID X Y Z
1 | 81 | April | NULL
Y | 12 | Dog | Sheep
12.3 | Cherry | Spain | 3|||I've got broccolli and I want lobster. I can't make one out of the other either.|||Remeber the MASH episode (when they used to be good) when they made the spam lamb for the turkish troops?|||Originally posted by blindman
You want help violating the rules of normalization? Should I buy you a carton of cigarettes while I'm at it? Neither activity is healthy.

I would not want you doing the first ... but you can certainly buy that carton of cigs for me ...|||Thanks for all your help :),

I figured it out , it can be down by a DTS package or a cross-tab query.

Sid.

Originally posted by Karolyn
I've got

Table1
ID | A | B | C
1 | 2 | 3 | 4
2 | 9 | 4 | 5
3 | 22| 53 94

I want this result

Table1
ID X Y Z
1 | 81 | April | NULL
Y | 12 | Dog | Sheep
12.3 | Cherry | Spain | 3

Help needed with SSIS and DTS help

Hi Guys,

I'm trying to develop an application and I have been told right here in sql server getting started forum that SSIS or DTS are the best bits.

My old post has these questions:

Code Snippet

I'm making an application. I have 2 different sites running Sql Server 2005 workgroup and sql server express.

I have a 3rd main server and what I would need to do is to get some of the data from both of these sites, merge it and save it on Main server and then generate another file which should be placed in a directory on a network.

I don't have any idea of how I would do that. Its entirely a sql server app.

One person told me its an SSIS and the other one said to investigate replication.

I have checked a bit about both. I want to give it a try.

Where can i find tutorials on both of these. Complete tutorials to develop and design it.

Thanks

Gemma

books on line are a good starting point:

http://msdn2.microsoft.com/en-us/library/bb545450.aspx

|||

Hi Rafael,

I was thinking more in terms apart of books online. I'm not that clever like you guys, you know.

Apart can you tell me if i put Sql Server 2000 as the main server Can i access the Sql Server 2005 and get some data as I tried it and it doesn't work using DTS.

Thanks

Gemma

|||

Gemma wrote:

Apart can you tell me if i put Sql Server 2000 as the main server Can i access the Sql Server 2005 and get some data as I tried it and it doesn't work using DTS.

Can somebody plz reply to this thread? plz. plz. plz.

Thanks

Gemma

|||

This is a quick set of tutorials on SSIS (included as part of Books Online): http://msdn2.microsoft.com/en-us/library/ms169917.aspx

I'd really recommend going through that first, before getting other books.

As a more general reply to your question, SSIS should be able to accomplish what you want to do.

help needed in writing a recursive function

Guys, I have a table with following data
Table Name :TblRecursive
Sample records:
ID, Name, ParentID
1, A, 0
2, B, 1
3, C, 2
4, D, 2
5, E, 1
Using the above data I just want to generate a result as below
A
A\B
A\B\C
A\B\D
A\E
Can you help in writing a query for this?
Regards
PradeepLook at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
In SQL 2005 you can use a common table expression with a recursive member.
ML
http://milambda.blogspot.com/|||http://vyaskn.tripod.com/hierarchie...r_databases.htm
Madhivanan|||Thanks for your response ML.
As I told earlier .. I want to generate the string as A\B and so on. But
your UDF gives me 3 columns.
Also I don't want to provide any input to the function also. I want it to
just start from the beginning and go on till the end. Any idea on how to
achieve this?
Regards
Pradeep
"ML" wrote:

> Look at this example:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
> In SQL 2005 you can use a common table expression with a recursive member.
>
> ML
> --
> http://milambda.blogspot.com/|||Concatenating values into a single string really should be done on the
cilent, as it has no relational value - it's pure presentation.
There are a few ways to do it on the server if absolutely necesary:
declare @.result varchar(8000)
set @.result = N''
select @.result = <column> + '/' + @.result
from <function>
order by <level?>
ML
http://milambda.blogspot.com/|||Check whether this sample helps you!
--Table creation
Create table tblEmployeeInfo
(
EmpId int primary key,
EmpName varchar(30),
MgrId int
)
--Insert test data into it
Insert into tblEmployeeInfo values(1, 'Director', null)
Go
Insert into tblEmployeeInfo values(2, 'Joint Director', 1)
Go
Insert into tblEmployeeInfo values(3, 'Secretary', 2)
Go
Insert into tblEmployeeInfo values(4, 'Joint Secr.,', 3)
Go
Insert into tblEmployeeInfo values(5, 'Legal Advisor', 1)
Go
-- UDF for your requirement
Create function GetEmpPath ( @.pEmpid int ) Returns varchar(8000)
As
Begin
Declare @.intMgrid int
Declare @.strEname varchar(500)
Declare @.strPath varchar(500)
Select @.intMgrid = mgrid, @.strEname = empname From tblEmployeeInfo where
EmpId = @.pEmpid
If (@.intMgrid is null)
Begin
Set @.strPath = @.strEname
End
Else
Set @.strPath = dbo.GetEmpPath(@.intMgrid) + '' + @.strEname
return @.strPath
End
Go
--Test the code
Select dbo.GetEmpPath(empid) as Hierarchy, empname as 'Employee Name' from
tblEmployeeInfo
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"SqlBeginner" wrote:
> Thanks for your response ML.
> As I told earlier .. I want to generate the string as A\B and so on. But
> your UDF gives me 3 columns.
> Also I don't want to provide any input to the function also. I want it to
> just start from the beginning and go on till the end. Any idea on how to
> achieve this?
> Regards
> Pradeep
>
> "ML" wrote:
>|||This is what I was looking for. Thanks a lot Vadivel. It really helped me.
Regards
Pradeep
"Vadivel" wrote:
> Check whether this sample helps you!
> --Table creation
> Create table tblEmployeeInfo
> (
> EmpId int primary key,
> EmpName varchar(30),
> MgrId int
> )
> --Insert test data into it
> Insert into tblEmployeeInfo values(1, 'Director', null)
> Go
> Insert into tblEmployeeInfo values(2, 'Joint Director', 1)
> Go
> Insert into tblEmployeeInfo values(3, 'Secretary', 2)
> Go
> Insert into tblEmployeeInfo values(4, 'Joint Secr.,', 3)
> Go
> Insert into tblEmployeeInfo values(5, 'Legal Advisor', 1)
> Go
> -- UDF for your requirement
> Create function GetEmpPath ( @.pEmpid int ) Returns varchar(8000)
> As
> Begin
> Declare @.intMgrid int
> Declare @.strEname varchar(500)
> Declare @.strPath varchar(500)
> Select @.intMgrid = mgrid, @.strEname = empname From tblEmployeeInfo wher
e
> EmpId = @.pEmpid
> If (@.intMgrid is null)
> Begin
> Set @.strPath = @.strEname
> End
> Else
> Set @.strPath = dbo.GetEmpPath(@.intMgrid) + '' + @.strEname
> return @.strPath
> End
> Go
> --Test the code
> Select dbo.GetEmpPath(empid) as Hierarchy, empname as 'Employee Name' from
> tblEmployeeInfo
> Hope this helps!
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "SqlBeginner" wrote:
>|||Take into acount that in SQL 2000 the maximum recursion level is 32. And
quite frankly, recursion is not necesary here.
In my other post there's a solution that does not use a rexursive function.
Another example can also be found here:
http://milambda.blogspot.com/2005/0...s-as-array.html
ML
http://milambda.blogspot.com/

Help Me read the data collected from Trace Flag 1204

Here is the data i got...from trace flag 1204..Could you guys help me read
this..like understand which is the problem child and which one is chosen as
a
victim any thing that helps me to understand this..
Deadlock encountered ... Printing deadlock information
2006-09-07 03:05:18.92 spid4
2006-09-07 03:05:18.92 spid4 Wait-for graph
2006-09-07 03:05:18.92 spid4
2006-09-07 03:05:18.92 spid4 Node:1
2006-09-07 03:05:18.92 spid4 PAG: 9:1:5151886 CleanCnt:3
Mode: IX Flags: 0x2
2006-09-07 03:05:18.92 spid4 Grant List 1::
2006-09-07 03:05:18.92 spid4 Owner:0x292a7440 Mode: IX Flg:0x0 Ref
:
1 Life:02000000 SPID:181 ECID:0
2006-09-07 03:05:18.92 spid4 SPID: 181 ECID: 0 Statement Type: UPDATE
Line #: 260
2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_Upd;1
2006-09-07 03:05:18.92 spid4 Grant List 2::
2006-09-07 03:05:18.92 spid4 Requested By:
2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:6
2
ECID:0 Ec0x2C2D75A0) Value:0x7877bfa0 Cost0/0)
2006-09-07 03:05:18.92 spid4
2006-09-07 03:05:18.92 spid4 Node:2
2006-09-07 03:05:18.92 spid4 KEY: 9:453576654:1 (6100f6c31ca6) CleanCnt:
3
Mode: U Flags: 0x0
2006-09-07 03:05:18.92 spid4 Convert List:
2006-09-07 03:05:18.92 spid4 Owner:0x28d98fa0 Mode: X Flg:0x2 Ref
:
1 Life:02000000 SPID:210 ECID:0
2006-09-07 03:05:18.92 spid4 SPID: 210 ECID: 0 Statement Type: UPDATE
Line #: 1
2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_upd_Finished;1
2006-09-07 03:05:18.92 spid4 Requested By:
2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: X SPI
D:
181 ECID:0 Ec0x5D6435A0) Value:0x787ad1c0 Cost0/0)
2006-09-07 03:05:18.92 spid4
2006-09-07 03:05:18.92 spid4 Node:3
2006-09-07 03:05:18.92 spid4 KEY: 9:453576654:1 (6100f6c31ca6) CleanCnt:
3
Mode: U Flags: 0x0
2006-09-07 03:05:18.92 spid4 Grant List 1::
2006-09-07 03:05:18.92 spid4 Owner:0x327cc680 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:81 ECID:0
2006-09-07 03:05:18.92 spid4 SPID: 81 ECID: 0 Statement Type: SELECT
Line #: 1
2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_Sea_Sel;1
2006-09-07 03:05:18.92 spid4 Grant List 2::
2006-09-07 03:05:18.92 spid4 Requested By:
2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: X SPI
D:
210 ECID:0 Ec0x1EF9D5A0) Value:0x28d98fa0 Cost0/1BC)
2006-09-07 03:05:18.92 spid4
2006-09-07 03:05:18.92 spid4 Node:4
2006-09-07 03:05:18.92 spid4 PAG: 9:1:5151886 CleanCnt:3
Mode: IX Flags: 0x2
2006-09-07 03:05:18.92 spid4 Wait List:
2006-09-07 03:05:18.92 spid4 Owner:0x7877bfa0 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:62 ECID:0
2006-09-07 03:05:18.92 spid4 SPID: 62 ECID: 0 Statement Type: SELECT
Line #: 6
2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_sel_ToSam;1
2006-09-07 03:05:18.92 spid4 Requested By:
2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: IS
SPID:81 ECID:0 Ec0x403995A0) Value:0x1e480020 Cost0/0)
2006-09-07 03:05:18.92 spid4 Victim Resource Owner:
2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: IS SPID
:
81 ECID:0 Ec0x403995A0) Value:0x1e480020 Cost0/0)
2006-09-07 03:05:18.92 spid4
Thanks in ADVANCE!
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1read this article.
http://msdn.microsoft.com/library/d...
tabse_5xrn.asp
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"msqldba via droptable.com" <u11604@.uwe> wrote in message
news:65fced5a1d92d@.uwe...
> Here is the data i got...from trace flag 1204..Could you guys help me read
> this..like understand which is the problem child and which one is chosen
> as a
> victim any thing that helps me to understand this..
>
>
> Deadlock encountered ... Printing deadlock information
> 2006-09-07 03:05:18.92 spid4
> 2006-09-07 03:05:18.92 spid4 Wait-for graph
> 2006-09-07 03:05:18.92 spid4
> 2006-09-07 03:05:18.92 spid4 Node:1
> 2006-09-07 03:05:18.92 spid4 PAG: 9:1:5151886 CleanCnt:3
> Mode: IX Flags: 0x2
> 2006-09-07 03:05:18.92 spid4 Grant List 1::
> 2006-09-07 03:05:18.92 spid4 Owner:0x292a7440 Mode: IX Flg:0x0
> Ref:
> 1 Life:02000000 SPID:181 ECID:0
> 2006-09-07 03:05:18.92 spid4 SPID: 181 ECID: 0 Statement Type: UPDATE
> Line #: 260
> 2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_Upd;1
> 2006-09-07 03:05:18.92 spid4 Grant List 2::
> 2006-09-07 03:05:18.92 spid4 Requested By:
> 2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:62
> ECID:0 Ec0x2C2D75A0) Value:0x7877bfa0 Cost0/0)
>
> 2006-09-07 03:05:18.92 spid4
> 2006-09-07 03:05:18.92 spid4 Node:2
> 2006-09-07 03:05:18.92 spid4 KEY: 9:453576654:1 (6100f6c31ca6)
> CleanCnt:3
> Mode: U Flags: 0x0
> 2006-09-07 03:05:18.92 spid4 Convert List:
> 2006-09-07 03:05:18.92 spid4 Owner:0x28d98fa0 Mode: X Flg:0x2
> Ref:
> 1 Life:02000000 SPID:210 ECID:0
> 2006-09-07 03:05:18.92 spid4 SPID: 210 ECID: 0 Statement Type: UPDATE
> Line #: 1
> 2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_upd_Finished;1
> 2006-09-07 03:05:18.92 spid4 Requested By:
> 2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: X
> SPID:
> 181 ECID:0 Ec0x5D6435A0) Value:0x787ad1c0 Cost0/0)
>
> 2006-09-07 03:05:18.92 spid4
> 2006-09-07 03:05:18.92 spid4 Node:3
> 2006-09-07 03:05:18.92 spid4 KEY: 9:453576654:1 (6100f6c31ca6)
> CleanCnt:3
> Mode: U Flags: 0x0
> 2006-09-07 03:05:18.92 spid4 Grant List 1::
> 2006-09-07 03:05:18.92 spid4 Owner:0x327cc680 Mode: S
> Flg:0x0
> Ref:1 Life:00000000 SPID:81 ECID:0
> 2006-09-07 03:05:18.92 spid4 SPID: 81 ECID: 0 Statement Type:
> SELECT
> Line #: 1
> 2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_Sea_Sel;1
> 2006-09-07 03:05:18.92 spid4 Grant List 2::
> 2006-09-07 03:05:18.92 spid4 Requested By:
> 2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: X
> SPID:
> 210 ECID:0 Ec0x1EF9D5A0) Value:0x28d98fa0 Cost0/1BC)
>
> 2006-09-07 03:05:18.92 spid4
> 2006-09-07 03:05:18.92 spid4 Node:4
> 2006-09-07 03:05:18.92 spid4 PAG: 9:1:5151886 CleanCnt:3
> Mode: IX Flags: 0x2
> 2006-09-07 03:05:18.92 spid4 Wait List:
> 2006-09-07 03:05:18.92 spid4 Owner:0x7877bfa0 Mode: S
> Flg:0x0
> Ref:1 Life:00000000 SPID:62 ECID:0
> 2006-09-07 03:05:18.92 spid4 SPID: 62 ECID: 0 Statement Type:
> SELECT
> Line #: 6
> 2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_sel_ToSam;1
> 2006-09-07 03:05:18.92 spid4 Requested By:
> 2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: IS
> SPID:81 ECID:0 Ec0x403995A0) Value:0x1e480020 Cost0/0)
> 2006-09-07 03:05:18.92 spid4 Victim Resource Owner:
> 2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: IS
> SPID:
> 81 ECID:0 Ec0x403995A0) Value:0x1e480020 Cost0/0)
> 2006-09-07 03:05:18.92 spid4
> Thanks in ADVANCE!
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200609/1
>

Help Me read the data collected from Trace Flag 1204

Here is the data i got...from trace flag 1204..Could you guys help me read
this..like understand which is the problem child and which one is chosen as a
victim any thing that helps me to understand this..
Deadlock encountered ... Printing deadlock information
2006-09-07 03:05:18.92 spid4
2006-09-07 03:05:18.92 spid4 Wait-for graph
2006-09-07 03:05:18.92 spid4
2006-09-07 03:05:18.92 spid4 Node:1
2006-09-07 03:05:18.92 spid4 PAG: 9:1:5151886 CleanCnt:3
Mode: IX Flags: 0x2
2006-09-07 03:05:18.92 spid4 Grant List 1::
2006-09-07 03:05:18.92 spid4 Owner:0x292a7440 Mode: IX Flg:0x0 Ref:
1 Life:02000000 SPID:181 ECID:0
2006-09-07 03:05:18.92 spid4 SPID: 181 ECID: 0 Statement Type: UPDATE
Line #: 260
2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_Upd;1
2006-09-07 03:05:18.92 spid4 Grant List 2::
2006-09-07 03:05:18.92 spid4 Requested By:
2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:62
ECID:0 Ec0x2C2D75A0) Value:0x7877bfa0 Cost0/0)
2006-09-07 03:05:18.92 spid4
2006-09-07 03:05:18.92 spid4 Node:2
2006-09-07 03:05:18.92 spid4 KEY: 9:453576654:1 (6100f6c31ca6) CleanCnt:3
Mode: U Flags: 0x0
2006-09-07 03:05:18.92 spid4 Convert List:
2006-09-07 03:05:18.92 spid4 Owner:0x28d98fa0 Mode: X Flg:0x2 Ref:
1 Life:02000000 SPID:210 ECID:0
2006-09-07 03:05:18.92 spid4 SPID: 210 ECID: 0 Statement Type: UPDATE
Line #: 1
2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_upd_Finished;1
2006-09-07 03:05:18.92 spid4 Requested By:
2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:
181 ECID:0 Ec0x5D6435A0) Value:0x787ad1c0 Cost0/0)
2006-09-07 03:05:18.92 spid4
2006-09-07 03:05:18.92 spid4 Node:3
2006-09-07 03:05:18.92 spid4 KEY: 9:453576654:1 (6100f6c31ca6) CleanCnt:3
Mode: U Flags: 0x0
2006-09-07 03:05:18.92 spid4 Grant List 1::
2006-09-07 03:05:18.92 spid4 Owner:0x327cc680 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:81 ECID:0
2006-09-07 03:05:18.92 spid4 SPID: 81 ECID: 0 Statement Type: SELECT
Line #: 1
2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_Sea_Sel;1
2006-09-07 03:05:18.92 spid4 Grant List 2::
2006-09-07 03:05:18.92 spid4 Requested By:
2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:
210 ECID:0 Ec0x1EF9D5A0) Value:0x28d98fa0 Cost0/1BC)
2006-09-07 03:05:18.92 spid4
2006-09-07 03:05:18.92 spid4 Node:4
2006-09-07 03:05:18.92 spid4 PAG: 9:1:5151886 CleanCnt:3
Mode: IX Flags: 0x2
2006-09-07 03:05:18.92 spid4 Wait List:
2006-09-07 03:05:18.92 spid4 Owner:0x7877bfa0 Mode: S Flg:0x0
Ref:1 Life:00000000 SPID:62 ECID:0
2006-09-07 03:05:18.92 spid4 SPID: 62 ECID: 0 Statement Type: SELECT
Line #: 6
2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_sel_ToSam;1
2006-09-07 03:05:18.92 spid4 Requested By:
2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: IS
SPID:81 ECID:0 Ec0x403995A0) Value:0x1e480020 Cost0/0)
2006-09-07 03:05:18.92 spid4 Victim Resource Owner:
2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: IS SPID:
81 ECID:0 Ec0x403995A0) Value:0x1e480020 Cost0/0)
2006-09-07 03:05:18.92 spid4
Thanks in ADVANCE!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1read this article.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"msqldba via SQLMonster.com" <u11604@.uwe> wrote in message
news:65fced5a1d92d@.uwe...
> Here is the data i got...from trace flag 1204..Could you guys help me read
> this..like understand which is the problem child and which one is chosen
> as a
> victim any thing that helps me to understand this..
>
>
> Deadlock encountered ... Printing deadlock information
> 2006-09-07 03:05:18.92 spid4
> 2006-09-07 03:05:18.92 spid4 Wait-for graph
> 2006-09-07 03:05:18.92 spid4
> 2006-09-07 03:05:18.92 spid4 Node:1
> 2006-09-07 03:05:18.92 spid4 PAG: 9:1:5151886 CleanCnt:3
> Mode: IX Flags: 0x2
> 2006-09-07 03:05:18.92 spid4 Grant List 1::
> 2006-09-07 03:05:18.92 spid4 Owner:0x292a7440 Mode: IX Flg:0x0
> Ref:
> 1 Life:02000000 SPID:181 ECID:0
> 2006-09-07 03:05:18.92 spid4 SPID: 181 ECID: 0 Statement Type: UPDATE
> Line #: 260
> 2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_Upd;1
> 2006-09-07 03:05:18.92 spid4 Grant List 2::
> 2006-09-07 03:05:18.92 spid4 Requested By:
> 2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:62
> ECID:0 Ec0x2C2D75A0) Value:0x7877bfa0 Cost0/0)
>
> 2006-09-07 03:05:18.92 spid4
> 2006-09-07 03:05:18.92 spid4 Node:2
> 2006-09-07 03:05:18.92 spid4 KEY: 9:453576654:1 (6100f6c31ca6)
> CleanCnt:3
> Mode: U Flags: 0x0
> 2006-09-07 03:05:18.92 spid4 Convert List:
> 2006-09-07 03:05:18.92 spid4 Owner:0x28d98fa0 Mode: X Flg:0x2
> Ref:
> 1 Life:02000000 SPID:210 ECID:0
> 2006-09-07 03:05:18.92 spid4 SPID: 210 ECID: 0 Statement Type: UPDATE
> Line #: 1
> 2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_upd_Finished;1
> 2006-09-07 03:05:18.92 spid4 Requested By:
> 2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: X
> SPID:
> 181 ECID:0 Ec0x5D6435A0) Value:0x787ad1c0 Cost0/0)
>
> 2006-09-07 03:05:18.92 spid4
> 2006-09-07 03:05:18.92 spid4 Node:3
> 2006-09-07 03:05:18.92 spid4 KEY: 9:453576654:1 (6100f6c31ca6)
> CleanCnt:3
> Mode: U Flags: 0x0
> 2006-09-07 03:05:18.92 spid4 Grant List 1::
> 2006-09-07 03:05:18.92 spid4 Owner:0x327cc680 Mode: S
> Flg:0x0
> Ref:1 Life:00000000 SPID:81 ECID:0
> 2006-09-07 03:05:18.92 spid4 SPID: 81 ECID: 0 Statement Type:
> SELECT
> Line #: 1
> 2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_Sea_Sel;1
> 2006-09-07 03:05:18.92 spid4 Grant List 2::
> 2006-09-07 03:05:18.92 spid4 Requested By:
> 2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: X
> SPID:
> 210 ECID:0 Ec0x1EF9D5A0) Value:0x28d98fa0 Cost0/1BC)
>
> 2006-09-07 03:05:18.92 spid4
> 2006-09-07 03:05:18.92 spid4 Node:4
> 2006-09-07 03:05:18.92 spid4 PAG: 9:1:5151886 CleanCnt:3
> Mode: IX Flags: 0x2
> 2006-09-07 03:05:18.92 spid4 Wait List:
> 2006-09-07 03:05:18.92 spid4 Owner:0x7877bfa0 Mode: S
> Flg:0x0
> Ref:1 Life:00000000 SPID:62 ECID:0
> 2006-09-07 03:05:18.92 spid4 SPID: 62 ECID: 0 Statement Type:
> SELECT
> Line #: 6
> 2006-09-07 03:05:18.92 spid4 Input Buf: RPC Event: sp_sel_ToSam;1
> 2006-09-07 03:05:18.92 spid4 Requested By:
> 2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: IS
> SPID:81 ECID:0 Ec0x403995A0) Value:0x1e480020 Cost0/0)
> 2006-09-07 03:05:18.92 spid4 Victim Resource Owner:
> 2006-09-07 03:05:18.92 spid4 ResType:LockOwner Stype:'OR' Mode: IS
> SPID:
> 81 ECID:0 Ec0x403995A0) Value:0x1e480020 Cost0/0)
> 2006-09-07 03:05:18.92 spid4
> Thanks in ADVANCE!
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1
>