Also check your foreign keys. For example, if table Foo has a column
Bar_ID, where Bar_ID has a foreign key constraint to table Bar, then
inserting into Foo will also lock Bar as the engine verifies that the
new value of Bar_ID is valid. Without more info I've no idea if this is
your problem, but it's one I've run into.


> -----Original Message-----
> From: Subhash Chandra
> [mailto:mssqldba-ezmlmshield-x25525151.[Email address protected]
> Sent: Saturday, May 26, 2007 12:32 AM
> To: LazyDBA Discussion
> Subject: Deadlock Detection
>
>
> Hi All,
>
> Using trace 1204, 1305 I found details of many deadlocks in
> errorlog. But in
> some deadlock table that is locked is nowhere used by stored procedure
> executed by participating SPIDs.
> If there is no operation on a table by specified stored
> proceudre then how
> this table is causing deadlock.
>
> Thanks & Regards,
> Subhash
>
> -----Original Message-----
> From: Subhash Chandra [mailto:[Email address protected]
> Sent: Wednesday, May 23, 2007 3:20 PM
> To: 'Ashish Mohite '; 'LazyDBA Discussion'
> Subject: RE: query-urgent
>
> Hi Ashish,
>
> following query return the desired resultset.
>
> select distinct base.a, base.b,
> (select top 1 c from test1 where d = 'Telephone' and a =
> base.a and b =
> base.b order by c) as Telephone1, (select top 1 c from test1 where d =
> 'Telephone' and a = base.a and b = base.b and c not in
> (select top 1 c from
> test1 where d = 'Telephone' and a = base.a and b = base.b
> order by c) order
> by c) as Telephone2, (select top 1 c from test1 where d =
> 'Telephone' and a
> = base.a and b = base.b and c not in (select top 2 c from
> test1 where d =
> 'Telephone' and a = base.a and b = base.b order by c) order by c) as
> Telephone3, (select top 1 c from test1 where d = 'Fax' and a
> = base.a and b
> = base.b order by c) as Fax1, (select top 1 c from test1
> where d = 'Fax'
> and a = base.a and b = base.b and c not in (select top 1 c
> from test1 where
> d = 'Fax' and a = base.a and b = base.b order by c) order by
> c) as Fax2,
> (select top 1 c from test1 where d = 'Fax' and a = base.a
> and b = base.b
> and c not in (select top 2 c from test1 where d = 'Fax' and
> a = base.a and
> b = base.b order by c) order by c) as Fax3 from test1 base
>
> The numbers of columns depends on your requirement.
> You can use other way to return 2nd and 3rd (or more if
> required) highest
> values.
>
> Thanks & Regards,
> Subhash
>
> -----Original Message-----
> From: Ashish Mohite
> [mailto:mssqldba-ezmlmshield-x63997230.[Email address protected]
> Sent: Wednesday, May 23, 2007 2:22 PM
> To: LazyDBA Discussion
> Subject: query-urgent
>
>
>
>
> Hi, <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> I have some problem while writing query. I am providing you
> sample data can
> you help me? <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> Sample data <http://www.dnb.co.in>
>
> Column a b c d e
> f <http://www.dnb.co.in>
>
> 19 356 91 22 25243269
> Telephone <http://www.dnb.co.in>
>
> 19 356 91 22 25227847
> Telephone <http://www.dnb.co.in>
>
> 19 356 91 22 25222994
> Fax <http://www.dnb.co.in>
>
> 22 356 91 4111 256031
> Telephone <http://www.dnb.co.in>
>
> And output like <http://www.dnb.co.in>
>
> A b c d phone1
> phone2 fax1 <http://www.dnb.co.in>
>
> 19 356 91 22 25243269
> 25227847 25222994 <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> And strictly I want use query only. <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> Waiting for your reply <http://www.dnb.co.in>
>
> Thanks in advance. <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> Best Regards, <http://www.dnb.co.in>
>
> <http://www.dnb.co.in>
>
> Ashish <http://www.dnb.co.in>
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST
> REPLY To post a dba
> job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com To
> unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>

MS Sql Server LazyDBA home page