help for a search procedure

hello,
please help me to give me solution
i have a table
CREATE TABLE T_STUDENT_MT (ID INT PRIMARY KEY, NAME VARCHAR2(50) NOT
NULL, FATHERNAME VARCHAR2(50), COUNTRY VARCHAR2(50), STATE VARCHAR2(20),
CITY VARCHAR2(30), STREET VARCHAR2(40))
MY DATA IS
INSERT INTO T_STUDENT_MT VALUES (1,
'ABC1','FABC1','CCC1','SSS1','CTT1','STT
1');
INSERT INTO T_STUDENT_MT VALUES (2,
'ABC2','FABC2','CCC2','SSS2','CTT2','STT
2');
INSERT INTO T_STUDENT_MT VALUES (3,
'ABC3',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO T_STUDENT_MT VALUES (4,
'XXBC3','FCDD','ABDD',NULL,NULL,NULL,NUL
L);
PLEASE SUGGEST TO WRITE A PROCEDURE FOR
'CONDITION 1: If i pass iNAME to null it should NOT check in where
condition
'CONDITION 2:if i pass iNAME Parameter as 'A' IT HAS TO CHECK AS NAME
LIKE iNAME || '%'
CREATE PROCEDURE STUDENTSEARCH(iNAME VARCHAR2, iFATHERNAME VARCHAR2 ,
COUNTRY VARCHAR2, STATE VARCHAR2, CITY VARCHAR2, STREET VARCHAR2)
AS
BEGIN
--SINGE QUERY TO RETURN STUDENTT DATA
END;
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!kamal hussain wrote:
> hello,
> please help me to give me solution
> i have a table
> CREATE TABLE T_STUDENT_MT (ID INT PRIMARY KEY, NAME VARCHAR2(50) NOT
> NULL, FATHERNAME VARCHAR2(50), COUNTRY VARCHAR2(50), STATE VARCHAR2(20),
> CITY VARCHAR2(30), STREET VARCHAR2(40))
>
> MY DATA IS
> INSERT INTO T_STUDENT_MT VALUES (1,
> 'ABC1','FABC1','CCC1','SSS1','CTT1','STT
1');
> INSERT INTO T_STUDENT_MT VALUES (2,
> 'ABC2','FABC2','CCC2','SSS2','CTT2','STT
2');
> INSERT INTO T_STUDENT_MT VALUES (3,
> 'ABC3',NULL,NULL,NULL,NULL,NULL,NULL);
> INSERT INTO T_STUDENT_MT VALUES (4,
> 'XXBC3','FCDD','ABDD',NULL,NULL,NULL,NUL
L);
>
> PLEASE SUGGEST TO WRITE A PROCEDURE FOR
> 'CONDITION 1: If i pass iNAME to null it should NOT check in where
> condition
> 'CONDITION 2:if i pass iNAME Parameter as 'A' IT HAS TO CHECK AS NAME
> LIKE iNAME || '%'
>
> CREATE PROCEDURE STUDENTSEARCH(iNAME VARCHAR2, iFATHERNAME VARCHAR2 ,
> COUNTRY VARCHAR2, STATE VARCHAR2, CITY VARCHAR2, STREET VARCHAR2)
> AS
> BEGIN
> --SINGE QUERY TO RETURN STUDENTT DATA
> END;
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I'm assuming condition 1 means if the variable @.Name is NULL, then you
want all the rows.
Try this:
CREATE PROCEDURE STUDENTSEARCH(
@.NAME VARCHAR(50) = NULL
)
AS
SELECT [ID]
,[NAME]
,FATHERNAME
,COUNTRY
,STATE
,CITY
,STREET
FROM T_STUDENT_MT
WHERE ((@.NAME IS NOT NULL AND [NAME] = @.NAME)
OR (@.NAME IS NULL))
OR (LEN(@.NAME)=1 AND [NAME] LIKE @.NAME + '%')
Note: In SQL Server there isn't a data type of VARCHAR2 and the
concatenation symbol is the plus sign (+).
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQi0xPoechKqOuFEgEQJmOwCg1I3RunB5ASmn
7rtRnj7EZDpoKTkAoMrL
wTkd98HDDTfkao35SpHRNH7S
=WkyP
--END PGP SIGNATURE--

Help ! Error after (windows update) of .NET Framework

Hi all,
today i updated my .NET framework to 1.1 SP 1 (via windows update).
After than, my reporting services failed with the error key
"rsReportServerDisabled".
I have just a german full error message, but it is something with
"...decode symetric key ...". I use Win XP Professional as operating system.
Please apologize my english.
Thank your help in advance.
ThomasMost likely the ASP.NET account got reset. If you have the encryption key
saved, you can restore it using rskeymgmt. If not, you will need to reset
the encryption via rskeymgmt -d.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:D5EEE105-6F19-411C-A820-FBCE3940E699@.microsoft.com...
> Hi all,
> today i updated my .NET framework to 1.1 SP 1 (via windows update).
> After than, my reporting services failed with the error key
> "rsReportServerDisabled".
> I have just a german full error message, but it is something with
> "...decode symetric key ...". I use Win XP Professional as operating
> system.
> Please apologize my english.
> Thank your help in advance.
> Thomas
>
>|||I just tried it and you will probably also need to reconfigure the
connection string via rsconfig before you restore the encryption key.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:uCvrjDElEHA.3428@.TK2MSFTNGP14.phx.gbl...
> Most likely the ASP.NET account got reset. If you have the encryption key
> saved, you can restore it using rskeymgmt. If not, you will need to reset
> the encryption via rskeymgmt -d.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> news:D5EEE105-6F19-411C-A820-FBCE3940E699@.microsoft.com...
>> Hi all,
>> today i updated my .NET framework to 1.1 SP 1 (via windows update).
>> After than, my reporting services failed with the error key
>> "rsReportServerDisabled".
>> I have just a german full error message, but it is something with
>> "...decode symetric key ...". I use Win XP Professional as operating
>> system.
>> Please apologize my english.
>> Thank your help in advance.
>> Thomas
>>
>>
>|||OK, one more correction. :) I was told by the developers that you won't need
to restore the encryption key. After resetting the connection string, you
just need to do an rsactivate -r -c and things will start working again.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ePMQzNFlEHA.3452@.TK2MSFTNGP15.phx.gbl...
>I just tried it and you will probably also need to reconfigure the
>connection string via rsconfig before you restore the encryption key.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:uCvrjDElEHA.3428@.TK2MSFTNGP14.phx.gbl...
>> Most likely the ASP.NET account got reset. If you have the encryption key
>> saved, you can restore it using rskeymgmt. If not, you will need to reset
>> the encryption via rskeymgmt -d.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
>> news:D5EEE105-6F19-411C-A820-FBCE3940E699@.microsoft.com...
>> Hi all,
>> today i updated my .NET framework to 1.1 SP 1 (via windows update).
>> After than, my reporting services failed with the error key
>> "rsReportServerDisabled".
>> I have just a german full error message, but it is something with
>> "...decode symetric key ...". I use Win XP Professional as operating
>> system.
>> Please apologize my english.
>> Thank your help in advance.
>> Thomas
>>
>>
>>
>|||Thank you Brian !
I will try it at once...
Best regards from Germany
Thomas
"Brian Welcker [MSFT]" wrote:
> OK, one more correction. :) I was told by the developers that you won't need
> to restore the encryption key. After resetting the connection string, you
> just need to do an rsactivate -r -c and things will start working again.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:ePMQzNFlEHA.3452@.TK2MSFTNGP15.phx.gbl...
> >I just tried it and you will probably also need to reconfigure the
> >connection string via rsconfig before you restore the encryption key.
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> > news:uCvrjDElEHA.3428@.TK2MSFTNGP14.phx.gbl...
> >> Most likely the ASP.NET account got reset. If you have the encryption key
> >> saved, you can restore it using rskeymgmt. If not, you will need to reset
> >> the encryption via rskeymgmt -d.
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> >> news:D5EEE105-6F19-411C-A820-FBCE3940E699@.microsoft.com...
> >> Hi all,
> >> today i updated my .NET framework to 1.1 SP 1 (via windows update).
> >> After than, my reporting services failed with the error key
> >> "rsReportServerDisabled".
> >> I have just a german full error message, but it is something with
> >> "...decode symetric key ...". I use Win XP Professional as operating
> >> system.
> >> Please apologize my english.
> >>
> >> Thank your help in advance.
> >> Thomas
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>

Help - Unusual Problem

First, I can not change the data structure. I have a table called codesubsections that has an identity as the primary key. When a new codesubsection is entered the user can mark a formercodesubsection as a parent. The problem is that I need to be able to evaluate the table and identify the lowest CodeSubsectionID for a given row. so for instant when I look at row 7 it's text is 17 B p2 but because the CodeSubSectionID is not null I need to look at 6 then 5 and since 5 is null I need the text for 5,6,7 all to be 16:1 B. The only solution I have had any kind of luck with is to do a self join 3 times and coalesce the values up. See the code at the end. I am at my wits end, I had it working the other way assuming that the CodeSubSectionID was the parent of the record. This is the final piece of code on a project that is supposed to end tomorrow.

Any help is appreciated. I would normally post the procedures but I think that they are more confusing that the data explanation. This type of relationship is a first for me after 20 some years.

ID CodeSubSectionID Text

1 NULL 16:1 A

2 NULL 16:1 B

3 NULL 17 A

4 NULL 17 B p1

5 NULL 16:1 B

6 5 16:1 B

7 6 17 B p2

8 3 17 C

9 NULL 18

What is the version of SQL Server that you are using? You can use recursive CTEs in SQL Server 2005 to get your results. A generic solution on SQL Server 2000 requires some procedural code but you can expose it via non-inline table-valued function. Please specify your version and expected results. I am not sure how the concatenation is supposed to happen. Does this have to be in specific order for the related items.|||

This piece of code can get the result:

SELECT Y.id1 AS id, Y.minPid, text FROM codesubsections INNER JOIN

(SELECT Z.id1, COALESCE(Z.id4,Z.id3,Z.id2,Z.id1) AS minPid

FROM (SELECT A.id as id1, B.id as id2,C.id as id3, D.id as id4

FROM codesubsections AS A LEFT OUTER JOIN

codesubsections AS B ON B.id = A.CodeSubsectionID LEFT OUTER JOIN

codesubsections AS C ON C.id = B.CodeSubsectionID LEFT OUTER JOIN

codesubsections AS D ON D.id = C.CodeSubsectionID) AS Z) AS Y on Y.minPid=id

--You may keep adding the LEFT OUTER JOIN levels to move up.

|||

Sorry using 2000 and CTE not available.

The second solution would be fine and that is what I had done however, I don't know how deep I have to go beforehand, so I need to test the grandparent and if it has a code section and continue to loop. In order to do that I added an UnProcessed field and this is the loop that I set up and the UDF that I am using. The problem is that when the loop finishes that the bottom item's codesectionid is null and does not get included in the last loop so I never get the 5 text value to update the 6 and 7 value. it only goes to the 6 record. Hope this makes sense.

I really do appreciate the responses. Thanks in advance

DECLARE @.id INT
SET @.id = 1
WHILE @.id = 1

BEGIN
UPDATE CodeSectionsSummary SET
CodeSectionsSummary.UnProcessed = PCSUMMARY.UnProcessed,
CodeSectionsSummary.CodeSubSectionID =PCSUMMARY.CodeSubSectionID,
CodeSectionsSummary.CodeSection = PCSUMMARY.Parent,
CodeSectionsSummary.ParticipantTypeID = PCSUMMARY.ParticipantTypeID,
CodeSectionsSummary.CodeTypeID = PCSUMMARY.CodeTypeID
FROM
CodeSectionsSummary
INNER JOIN PCSUMMARY(0) PCSUMMARY ON CodeSectionsSummary.ID = PCSUMMARY.ID

IF EXISTS (SELECT COUNT(*) FROM CodeSectionsSummary WHERE UnProcessed = 1)
SET @.id = 0
ELSE
CONTINUE
END

Code for the UDF

CREATE FUNCTION dbo.PCSUMMARY
( @.CNT INT)

RETURNS @.RtnValue TABLE
(
[ID] INT ,
CodeSubSectionID INT,
Parent VARCHAR(10),
UnProcessed INT,
ParticipantTypeID INT,
CodeTypeID INT
)
AS
BEGIN
INSERT INTO @.RtnValue
SELECT CodeSectionsSummary.ID,
CodeSubsections_2.FormerCodeSubsectionID AS CodeSubSectionID,
COALESCE (CodeSections_2.CodeSection, CodeSections_1.CodeSection, CodeSections.CodeSection) AS Parent,
CASE TEST.FormerCodeSubsectionID
WHEN ISNULL(TEST.FormerCodeSubsectionID,0) THEN
CASE CodeSubsections_2.FormerCodeSubsectionID
WHEN isnull(CodeSubsections_2.FormerCodeSubsectionID, 0)
THEN 1 ELSE 0 END
ELSE 2
END AS UnProcessed,
COALESCE (CodeSubsections_2.ParticipantTypeID, CodeSubsections_1.ParticipantTypeID, CodeSubsections.ParticipantTypeID) AS ParticipantTypeID,
COALESCE (CodeSections_2.CodeTypeID, CodeSections_1.CodeTypeID, CodeSections.CodeTypeID) AS CodeTypeID

FROM CodeSectionsSummary INNER JOIN
CodeSubsections ON CodeSectionsSummary.ID = CodeSubsections.ID INNER JOIN
CodeSections ON CodeSubsections.CodeSectionID = CodeSections.ID AND
CodeSubsections.CodeSectionID = CodeSections.ID AND CodeSubsections.CodeSectionID = CodeSections.ID LEFT OUTER JOIN
CodeSections CodeSections_2 INNER JOIN
CodeSubsections CodeSubsections_2 ON CodeSections_2.ID = CodeSubsections_2.CodeSectionID LEFT OUTER JOIN
CodeSubsections TEST ON CodeSubsections_2.FormerCodeSubsectionID = TEST.ID RIGHT OUTER JOIN
CodeSubsections CodeSubsections_1 INNER JOIN
CodeSections CodeSections_1 ON CodeSubsections_1.CodeSectionID = CodeSections_1.ID AND
CodeSubsections_1.CodeSectionID = CodeSections_1.ID AND CodeSubsections_1.CodeSectionID = CodeSections_1.ID ON
CodeSubsections_2.ID = CodeSubsections_1.FormerCodeSubsectionID ON
CodeSubsections.FormerCodeSubsectionID = CodeSubsections_1.ID
WHERE (CodeSectionsSummary.CodeSubSectionID IS NOT NULL)
RETURN
END

|||

I once had a situation like yours...

Why don't you do this:

declare @.iSubSection as int,
@.vcText as varchar ( max ),
@.tbTable as table ( iID int )

set @.iSubSection = <<id of your starting section>>

while not @.iSubSection is null

begin
insert into @.tbTable
select @.iSubSection

select @.iID = ID , @.iSubSection = CodeSubSectionID , @.vcText = Text
From your_table
where @.iSubSection = id
end

update your_table
set text = @.vcText
from @.table
where iID = ID

|||

Miguelb, i think you are on to a possible solution to this nightmare. Business Analyst that use reverse logic had a special place in hades reserved. I played around with your suggestion. The problem is that in the following records 7 looks at 6, 6 at 6 and 5 never gets evaluated because of the null value. Note, the sample data does not show that the text for 5 will probably be different than 6, sorry about that. I will keep playing with this, most promising approach so far.

5 NULL 16:1 B

6 5 16:1 B

7 6 17 B p2

|||

Hi,

I read a few papers about this topic. I came across this solution by using "String-Based "Nested Sets" technique" from the following link. Here is the link to the resource by Dennis W. Forbes.

http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies2.htm

I tested your dataset with this solution and it worked pretty good.

|||

Ever since Heracles snatched Cerberus that they let anyone enter in hades, so I wouldn't be surprised to see those people there :)

You are right - I forgot the last leaf. Try this (you may have to tweak it a little more):

declare @.iID as int,

@.iSubSection as int,

@.vcText as varchar ( max ),

@.tbTable as table ( iID int )

set @.iSubSection = <<id of your starting section>>

set @.iID = null

while not @.iSubSection is null

begin

insert into @.tbTable

select @.iSubSection

select @.iID = ID , @.iSubSection = CodeSubSectionID , @.vcText = Text

From your_table

where @.iSubSection = id

end

if @.iID is not null

begin

insert into @.tbTable

values ( @.iID )

end

update your_table

set text = @.vcText

from @.table

where iID = ID

Help - deleting existing auto-generated primary key

hi guys,

just a question regarding database design

i have a table with an auto-generated primary key but the problem is this:

say i have 4 records,so logically they'll be numbered 1 to 4.so the problem is whenever i delete all records and add new ones,the numbering will start from 5 and not 1 again.

how do i remedy this?

thanx

Two ways:

1. Use TRUNCATE instead of delete if you are deleting *all* rows

2. Use "DBCC CHECKIDENT(TABLE_NAME_HERE, RESEED, 0)" to reset the current identiy value back to required position if you want to use "DELETE" instead of "TRUNCATE"

|||

Another common practice is to create some sort of an ID column as well as the auto-generated primary key. Sounds like this way work better for you in this case if you plan on frequently deleting, inserting, etc.

Thanks,
Sam Lester (MSFT)

|||

hi,

wanna ask.is TRUNCATE only used if i want to delete all records in a table?

how am i able to do this if i only want to delete a selected row and all rows? say i got 4 records and i only want to delete the 2nd record.

so will the 3rd record be numbered as 2 now or will it remain as 3?

Help

I am a student,come from china.now i will write a article about sql server and oricle 9i,i want know how to write ,i want to find the key word about it.but i do not know finding which words .i hope somebody help me .thank u.A little google'll do ya...

http://www.databasejournal.com/features/mssql/article.php/2170201

help

hi guys,

just a question regarding database design

i have a table with an auto-generated primary key but the problem is this:

say i have 4 records,so logically they'll be numbered 1 to 4.so the problem is whenever i delete all records and add new ones,the numbering will start from 5 and not 1 again.

how do i remedy this?

thanx

Two ways:

1. Use TRUNCATE instead of delete if you are deleting *all* rows

2. Use "DBCC CHECKIDENT(TABLE_NAME_HERE, RESEED, 0)" to reset the current identiy value back to required position if you want to use "DELETE" instead of "TRUNCATE"

|||

Another common practice is to create some sort of an ID column as well as the auto-generated primary key. Sounds like this way work better for you in this case if you plan on frequently deleting, inserting, etc.

Thanks,
Sam Lester (MSFT)

|||

hi,

wanna ask.is TRUNCATE only used if i want to delete all records in a table?

how am i able to do this if i only want to delete a selected row and all rows? say i got 4 records and i only want to delete the 2nd record.

so will the 3rd record be numbered as 2 now or will it remain as 3?

Help

Can anyone tell me what this is :
PAG: 7:1:165015
Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
ThanxsSQL Server stores tables, indexes etc internally on "pages", which are 8kb
disk structures in SQL 7 / 2000. Additionally, pages are allocated in
"Extents", which are groups of 8 pages (64kb).
PAG: 7:1:165015 denotes {db_id}:{file_id}:{page_no}, so database #7, file #
1 & page # 165015 in the internal storage system.
Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID) refers to database # 8,
Object # 1653632984 & Index #2. You can identify the index by looking up
that objectid in the sysobjects system table.. It's likely a table & the
index is the second index on that table.
This type of information is generally displayed in locking analysis tools
such as deadlock graphs or output from sp_lock. Where are you seeing it? And
do you have a wider problem?
Regards,
Greg Linwood
SQL Server MVP
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:059e01c3c54c$68883850$a101280a@.phx.gbl...
> Can anyone tell me what this is :
> PAG: 7:1:165015
> Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
> Thanxs|||I have a deadlock reported (DBCC Trace on 1204) where
the "lock" is this:
PAG: 7:1:165015 and
PAG: 7:1:183854
How can i find out what "thing" is that. Ex:
KEY 8:1653632984:2
I can find the table and index associated with this
number.. how can i do that for PAG: 7:1:183854
very grateful for your help
>--Original Message--
>SQL Server stores tables, indexes etc internally
on "pages", which are 8kb
>disk structures in SQL 7 / 2000. Additionally, pages are
allocated in
>"Extents", which are groups of 8 pages (64kb).
>PAG: 7:1:165015 denotes {db_id}:{file_id}:{page_no}, so
database #7, file #
>1 & page # 165015 in the internal storage system.
>Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID) refers
to database # 8,
>Object # 1653632984 & Index #2. You can identify the
index by looking up
>that objectid in the sysobjects system table.. It's
likely a table & the
>index is the second index on that table.
>This type of information is generally displayed in
locking analysis tools
>such as deadlock graphs or output from sp_lock. Where are
you seeing it? And
>do you have a wider problem?
>Regards,
>Greg Linwood
>SQL Server MVP
>
>":)" <anonymous@.discussions.microsoft.com> wrote in
message
>news:059e01c3c54c$68883850$a101280a@.phx.gbl...
>> Can anyone tell me what this is :
>> PAG: 7:1:165015
>> Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
>> Thanxs
>
>.
>|||Can you post the full deadlock graph? This is the output from trace 1204.
There is better information in other parts of that output that will help us
get closer to your problem faster.
Trying to reverse back from page # to the cause of the deadlock is doing it
the hard way. The graph will reveal importantly, what type of dealock is
occurring & where. I'm on Australian time, so if you can do this within an
hour or so I'll look again tonight, otherwise hopefully someone else can
help too..
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:055f01c3c553$54a988a0$a401280a@.phx.gbl...
> I have a deadlock reported (DBCC Trace on 1204) where
> the "lock" is this:
> PAG: 7:1:165015 and
> PAG: 7:1:183854
> How can i find out what "thing" is that. Ex:
> KEY 8:1653632984:2
> I can find the table and index associated with this
> number.. how can i do that for PAG: 7:1:183854
> very grateful for your help
> >--Original Message--
> >SQL Server stores tables, indexes etc internally
> on "pages", which are 8kb
> >disk structures in SQL 7 / 2000. Additionally, pages are
> allocated in
> >"Extents", which are groups of 8 pages (64kb).
> >
> >PAG: 7:1:165015 denotes {db_id}:{file_id}:{page_no}, so
> database #7, file #
> >1 & page # 165015 in the internal storage system.
> >
> >Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID) refers
> to database # 8,
> >Object # 1653632984 & Index #2. You can identify the
> index by looking up
> >that objectid in the sysobjects system table.. It's
> likely a table & the
> >index is the second index on that table.
> >
> >This type of information is generally displayed in
> locking analysis tools
> >such as deadlock graphs or output from sp_lock. Where are
> you seeing it? And
> >do you have a wider problem?
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >
> >":)" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:059e01c3c54c$68883850$a101280a@.phx.gbl...
> >> Can anyone tell me what this is :
> >>
> >> PAG: 7:1:165015
> >>
> >> Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
> >>
> >> Thanxs
> >
> >
> >.
> >|||Can you tell me what you get from this and how so i can
learn for the furtue:
Deadlock encountered ... Printing deadlock information
2003-12-17 03:55:49.99 spid4
2003-12-17 03:55:49.99 spid4 Wait-for graph
2003-12-17 03:55:49.99 spid4
2003-12-17 03:55:49.99 spid4 Node:1
2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:15
(1104e1b47512) CleanCnt:1 Mode: S Flags: 0x0
2003-12-17 03:55:49.99 spid4 Grant List 0::
2003-12-17 03:55:49.99 spid4 Owner:0x35b61e00 Mode:
S Flg:0x0 Ref:0 Life:00000001 SPID:513 ECID:0
2003-12-17 03:55:49.99 spid4 SPID: 513 ECID: 0
Statement Type: INSERT Line #: 35
2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
GetSubscription;1
2003-12-17 03:55:49.99 spid4 Requested By:
2003-12-17 03:55:49.99 spid4 ResType:LockOwner
Stype:'OR' Mode: X SPID:84 ECID:0 Ec:(0x0E741588)
Value:0xef529e0 Cost:(0/84)
2003-12-17 03:55:49.99 spid4
2003-12-17 03:55:49.99 spid4 Node:2
2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:1
(e100d289ae1c) CleanCnt:2 Mode: X Flags: 0x0
2003-12-17 03:55:49.99 spid4 Wait List:
2003-12-17 03:55:49.99 spid4 Owner:0x3b4d7e80 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:63 ECID:0
2003-12-17 03:55:49.99 spid4 SPID: 63 ECID: 0
Statement Type: INSERT Line #: 35
2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
GetSubscription;1
2003-12-17 03:55:49.99 spid4 Requested By:
2003-12-17 03:55:49.99 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:513 ECID:0 Ec:(0x3E761588)
Value:0x7e27b780 Cost:(0/0)
2003-12-17 03:55:49.99 spid4
2003-12-17 03:55:49.99 spid4 Node:3
2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:1
(e100d289ae1c) CleanCnt:2 Mode: X Flags: 0x0
2003-12-17 03:55:49.99 spid4 Grant List 3::
2003-12-17 03:55:49.99 spid4 Owner:0x3bb827e0 Mode:
X Flg:0x0 Ref:0 Life:02000000 SPID:84 ECID:0
2003-12-17 03:55:49.99 spid4 SPID: 84 ECID: 0
Statement Type: UPDATE Line #: 23
2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
SetEventSentDate;1
2003-12-17 03:55:49.99 spid4 Requested By:
2003-12-17 03:55:49.99 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x0A001588)
Value:0x3b4d7e80 Cost:(0/0)
2003-12-17 03:55:49.99 spid4 Victim Resource Owner:
2003-12-17 03:55:49.99 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x0A001588)
Value:0x3b4d7e80 Cost:(0/0)
2003-12-17 03:55:49.99 spid4
2003-12-17 03:55:49.99 spid4 End deadlock search
41725 ... a deadlock was found.
2003-12-17 03:55:49.99 spid4 --
--
Cheers :)
>--Original Message--
>Can you post the full deadlock graph? This is the output
from trace 1204.
>There is better information in other parts of that output
that will help us
>get closer to your problem faster.
>Trying to reverse back from page # to the cause of the
deadlock is doing it
>the hard way. The graph will reveal importantly, what
type of dealock is
>occurring & where. I'm on Australian time, so if you can
do this within an
>hour or so I'll look again tonight, otherwise hopefully
someone else can
>help too..
>Regards,
>Greg Linwood
>SQL Server MVP
><anonymous@.discussions.microsoft.com> wrote in message
>news:055f01c3c553$54a988a0$a401280a@.phx.gbl...
>> I have a deadlock reported (DBCC Trace on 1204) where
>> the "lock" is this:
>> PAG: 7:1:165015 and
>> PAG: 7:1:183854
>> How can i find out what "thing" is that. Ex:
>> KEY 8:1653632984:2
>> I can find the table and index associated with this
>> number.. how can i do that for PAG: 7:1:183854
>> very grateful for your help
>> >--Original Message--
>> >SQL Server stores tables, indexes etc internally
>> on "pages", which are 8kb
>> >disk structures in SQL 7 / 2000. Additionally, pages
are
>> allocated in
>> >"Extents", which are groups of 8 pages (64kb).
>> >
>> >PAG: 7:1:165015 denotes {db_id}:{file_id}:{page_no}, so
>> database #7, file #
>> >1 & page # 165015 in the internal storage system.
>> >
>> >Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
refers
>> to database # 8,
>> >Object # 1653632984 & Index #2. You can identify the
>> index by looking up
>> >that objectid in the sysobjects system table.. It's
>> likely a table & the
>> >index is the second index on that table.
>> >
>> >This type of information is generally displayed in
>> locking analysis tools
>> >such as deadlock graphs or output from sp_lock. Where
are
>> you seeing it? And
>> >do you have a wider problem?
>> >
>> >Regards,
>> >Greg Linwood
>> >SQL Server MVP
>> >
>> >
>> >":)" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:059e01c3c54c$68883850$a101280a@.phx.gbl...
>> >> Can anyone tell me what this is :
>> >>
>> >> PAG: 7:1:165015
>> >>
>> >> Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
>> >>
>> >> Thanxs
>> >
>> >
>> >.
>> >
>
>.
>|||I wasn't 100% sure with this particular deadlock graph, so I took this
discussion offline with the othher MVPs & Ron Talmage was good enough to
provide this thorough analysis (below)
Regards,
Greg Linwood
SQL Server MVP
*********
Greg,
(a) SPID 63 is waiting for an S lock on the row in table 517576882 with a
clustered key hash value of e100d289ae1c.
(b) The deadlock is formed as follows:
1. SPID 84 has been granted an exclusive lock on the row in table 517576882
('the table'), clustered key (:1) with hash value e100d289ae1c.(Node 3)
SPID 84 is seeking to update the table's secondary index 15, hash value
1104e1b47512, to finish the update of the row (see in the Requested By
section of Node 1)
2. SPID 513 has been granted an S lock on the table's secondary index 15,
hash value 1104e1b47512. (Node 1)
SPID 84 is blocked by SPID 513, waiting for it to release its S lock on
secondary index 15.
3. SPID 63 is waiting to get an S lock on the same row of the table (Node 2)
SPID 63 is blocked by SPID 84, waiting for it to release its X lock on
the clustered key (see Requested By in Node 3)
4. SPID 513 cannot release its S lock on secondary index 15 until it gets an
S lock on the table's clustered key row.
SPID 513 requests the S lock but is put in a wait state behind SPID 63,
which is waiting on SPID 84.
SPID 513 is now blocked by SPID 63.
5. A deadlock cycle is detected and SPID 63 is chosen as the victim.
My guess is that another deadlock occurs almost immediately aftwerwards
between SPIDs 84 and 513, with SPID 513 as the victim, and it also is in the
error log.
Even though the statement type for SPIDs 63 and 513 are INSERT, the deadlock
arises with their requesting S locks on the table's row that is being
updated by SPID 84. Most probably those S lock requests are due to a SELECT
statement in the stored procedure GetSubscription, the procedure involved in
both 63 and 513. Assuming that GetSubscription is using the default
isolation level of READ COMMITTED, adding a NOLOCK or READPAST hint to the
SELECT statement against the table in GetSubscription could resolve the
deadlocks.
Without having the stored procedures and tables available, it's hard to be
more definite.
BTW, 15 or more indexes on a table is quite a lot. If index 15 isn't needed,
that would also resolve this partiicular deadlock!
Hope this helps,
Ron
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:083801c3c558$cdbe4aa0$a601280a@.phx.gbl...
> Can you tell me what you get from this and how so i can
> learn for the furtue:
> Deadlock encountered ... Printing deadlock information
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Wait-for graph
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Node:1
> 2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:15
> (1104e1b47512) CleanCnt:1 Mode: S Flags: 0x0
> 2003-12-17 03:55:49.99 spid4 Grant List 0::
> 2003-12-17 03:55:49.99 spid4 Owner:0x35b61e00 Mode:
> S Flg:0x0 Ref:0 Life:00000001 SPID:513 ECID:0
> 2003-12-17 03:55:49.99 spid4 SPID: 513 ECID: 0
> Statement Type: INSERT Line #: 35
> 2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
> GetSubscription;1
> 2003-12-17 03:55:49.99 spid4 Requested By:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: X SPID:84 ECID:0 Ec:(0x0E741588)
> Value:0xef529e0 Cost:(0/84)
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Node:2
> 2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:1
> (e100d289ae1c) CleanCnt:2 Mode: X Flags: 0x0
> 2003-12-17 03:55:49.99 spid4 Wait List:
> 2003-12-17 03:55:49.99 spid4 Owner:0x3b4d7e80 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:63 ECID:0
> 2003-12-17 03:55:49.99 spid4 SPID: 63 ECID: 0
> Statement Type: INSERT Line #: 35
> 2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
> GetSubscription;1
> 2003-12-17 03:55:49.99 spid4 Requested By:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:513 ECID:0 Ec:(0x3E761588)
> Value:0x7e27b780 Cost:(0/0)
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Node:3
> 2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:1
> (e100d289ae1c) CleanCnt:2 Mode: X Flags: 0x0
> 2003-12-17 03:55:49.99 spid4 Grant List 3::
> 2003-12-17 03:55:49.99 spid4 Owner:0x3bb827e0 Mode:
> X Flg:0x0 Ref:0 Life:02000000 SPID:84 ECID:0
> 2003-12-17 03:55:49.99 spid4 SPID: 84 ECID: 0
> Statement Type: UPDATE Line #: 23
> 2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
> SetEventSentDate;1
> 2003-12-17 03:55:49.99 spid4 Requested By:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x0A001588)
> Value:0x3b4d7e80 Cost:(0/0)
> 2003-12-17 03:55:49.99 spid4 Victim Resource Owner:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x0A001588)
> Value:0x3b4d7e80 Cost:(0/0)
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 End deadlock search
> 41725 ... a deadlock was found.
> 2003-12-17 03:55:49.99 spid4 --
> --
> Cheers :)
> >--Original Message--
> >Can you post the full deadlock graph? This is the output
> from trace 1204.
> >There is better information in other parts of that output
> that will help us
> >get closer to your problem faster.
> >
> >Trying to reverse back from page # to the cause of the
> deadlock is doing it
> >the hard way. The graph will reveal importantly, what
> type of dealock is
> >occurring & where. I'm on Australian time, so if you can
> do this within an
> >hour or so I'll look again tonight, otherwise hopefully
> someone else can
> >help too..
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:055f01c3c553$54a988a0$a401280a@.phx.gbl...
> >> I have a deadlock reported (DBCC Trace on 1204) where
> >> the "lock" is this:
> >> PAG: 7:1:165015 and
> >> PAG: 7:1:183854
> >>
> >> How can i find out what "thing" is that. Ex:
> >> KEY 8:1653632984:2
> >> I can find the table and index associated with this
> >> number.. how can i do that for PAG: 7:1:183854
> >>
> >> very grateful for your help
> >>
> >> >--Original Message--
> >> >SQL Server stores tables, indexes etc internally
> >> on "pages", which are 8kb
> >> >disk structures in SQL 7 / 2000. Additionally, pages
> are
> >> allocated in
> >> >"Extents", which are groups of 8 pages (64kb).
> >> >
> >> >PAG: 7:1:165015 denotes {db_id}:{file_id}:{page_no}, so
> >> database #7, file #
> >> >1 & page # 165015 in the internal storage system.
> >> >
> >> >Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
> refers
> >> to database # 8,
> >> >Object # 1653632984 & Index #2. You can identify the
> >> index by looking up
> >> >that objectid in the sysobjects system table.. It's
> >> likely a table & the
> >> >index is the second index on that table.
> >> >
> >> >This type of information is generally displayed in
> >> locking analysis tools
> >> >such as deadlock graphs or output from sp_lock. Where
> are
> >> you seeing it? And
> >> >do you have a wider problem?
> >> >
> >> >Regards,
> >> >Greg Linwood
> >> >SQL Server MVP
> >> >
> >> >
> >> >":)" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:059e01c3c54c$68883850$a101280a@.phx.gbl...
> >> >> Can anyone tell me what this is :
> >> >>
> >> >> PAG: 7:1:165015
> >> >>
> >> >> Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
> >> >>
> >> >> Thanxs
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Reading deadlock graphs is not easy. Explaining it requires explaining quite
a deal about locking types, lock type compatabilities, lockable resources,
deadlock types etc. Each of these is a fairly substantial topic so I'd
encourage you to read up on these first before trying to work out deadlock
graphs.
One excellent resource is Kalen Delaney's "Inside SQL Server" which has a
whole chapter dedicated to locking and even a section in that chapter on
reading deadlock graphs. Kalen also has an e-book on the topic, available
here: http://tinyurl.com/puwn
Deadlocks can be cause by either cyclical resource acquisition or lock type
conversion. Read up on these in Books Online of Kalen's book first.
The nodes in the graph represent the contributing paths that form the
"deadly embrace" in a deadlock. Usually these will be from different
connections (spids) and it is far more common to see only two nodes in a
graph. Yours has 3 which puts it into the more unusual category. There can
be more but these are relatively rare.
Each node describes a locked resource (KEY: ... in your case) that a
particular connection has either acuired (grant list) or is waiting on (wait
list). There is also usually a Requested By section which shows which
connection is requesting the competing lock. All lock requests are displayed
with their lock types (eg X=Exclusive, S=Shard, IX=IntentExclusive etc, etc)
so that the reader can compare to the lock compatability matrix.
Additionally, the Input buffer is also displayed for the connection's last
statement in the node. In your case, you can see that the "GetSubscription"
and "SetEventSentDate" stored procedures are contributing to this deadlock
scenario. Two of the three connections were executing GetSubscription,
including spid 63, which was the one chosen as the deadlock victim. The
offending line number is also displayed in your case, allowing you to hone
in on the root of the problem.
The biggest problem you face is working out what to actually do next. This
cannot usually be established just by looking at these stored procs in
isolation. Usually a more holistic approach is required to reduce the
effects of deadlocking. It's also important to understand that deadlocking
cannot be 100% removed from SQL Server apps, so you need catch / re-try
logic in youur application. Often, this is the most effective means of
dealing with deadlocks - simple retry logic can be very effective.
Ron points out in his post that you appear to have 15 indexes on the table
involved in this deadlock. Perhaps analysing if this many indexes are really
needed is the best thing you can do.
To establish which database your deadlock is occurring in, run the following
query:
select name from master..sysdatabases where dbid = 7
I've chosen 7 because this is the DBID following the KEY: part in each node
of the deadlock graph.
Once you've got the database name, run the following query:
select object_name(517576882)
This shows the name of the table that the deadlock is occurring against.
Basically, your deadlock is occurring when an update occurs on that table.
First, the table's clustered index is updated, then a secondary index (index
#15) is updated. This is where the deadlock occurs. If you experience this
deadlock a lot, perhaps removing that index might help relieve the
situation.
Anyway, this response has become quite long-winded. I hope it's of some use
to you!
Regards,
Greg Linwood
SQL Server MVP
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:083801c3c558$cdbe4aa0$a601280a@.phx.gbl...
> Can you tell me what you get from this and how so i can
> learn for the furtue:
> Deadlock encountered ... Printing deadlock information
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Wait-for graph
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Node:1
> 2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:15
> (1104e1b47512) CleanCnt:1 Mode: S Flags: 0x0
> 2003-12-17 03:55:49.99 spid4 Grant List 0::
> 2003-12-17 03:55:49.99 spid4 Owner:0x35b61e00 Mode:
> S Flg:0x0 Ref:0 Life:00000001 SPID:513 ECID:0
> 2003-12-17 03:55:49.99 spid4 SPID: 513 ECID: 0
> Statement Type: INSERT Line #: 35
> 2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
> GetSubscription;1
> 2003-12-17 03:55:49.99 spid4 Requested By:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: X SPID:84 ECID:0 Ec:(0x0E741588)
> Value:0xef529e0 Cost:(0/84)
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Node:2
> 2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:1
> (e100d289ae1c) CleanCnt:2 Mode: X Flags: 0x0
> 2003-12-17 03:55:49.99 spid4 Wait List:
> 2003-12-17 03:55:49.99 spid4 Owner:0x3b4d7e80 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:63 ECID:0
> 2003-12-17 03:55:49.99 spid4 SPID: 63 ECID: 0
> Statement Type: INSERT Line #: 35
> 2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
> GetSubscription;1
> 2003-12-17 03:55:49.99 spid4 Requested By:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:513 ECID:0 Ec:(0x3E761588)
> Value:0x7e27b780 Cost:(0/0)
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Node:3
> 2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:1
> (e100d289ae1c) CleanCnt:2 Mode: X Flags: 0x0
> 2003-12-17 03:55:49.99 spid4 Grant List 3::
> 2003-12-17 03:55:49.99 spid4 Owner:0x3bb827e0 Mode:
> X Flg:0x0 Ref:0 Life:02000000 SPID:84 ECID:0
> 2003-12-17 03:55:49.99 spid4 SPID: 84 ECID: 0
> Statement Type: UPDATE Line #: 23
> 2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
> SetEventSentDate;1
> 2003-12-17 03:55:49.99 spid4 Requested By:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x0A001588)
> Value:0x3b4d7e80 Cost:(0/0)
> 2003-12-17 03:55:49.99 spid4 Victim Resource Owner:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x0A001588)
> Value:0x3b4d7e80 Cost:(0/0)
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 End deadlock search
> 41725 ... a deadlock was found.
> 2003-12-17 03:55:49.99 spid4 --
> --
> Cheers :)
> >--Original Message--
> >Can you post the full deadlock graph? This is the output
> from trace 1204.
> >There is better information in other parts of that output
> that will help us
> >get closer to your problem faster.
> >
> >Trying to reverse back from page # to the cause of the
> deadlock is doing it
> >the hard way. The graph will reveal importantly, what
> type of dealock is
> >occurring & where. I'm on Australian time, so if you can
> do this within an
> >hour or so I'll look again tonight, otherwise hopefully
> someone else can
> >help too..
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:055f01c3c553$54a988a0$a401280a@.phx.gbl...
> >> I have a deadlock reported (DBCC Trace on 1204) where
> >> the "lock" is this:
> >> PAG: 7:1:165015 and
> >> PAG: 7:1:183854
> >>
> >> How can i find out what "thing" is that. Ex:
> >> KEY 8:1653632984:2
> >> I can find the table and index associated with this
> >> number.. how can i do that for PAG: 7:1:183854
> >>
> >> very grateful for your help
> >>
> >> >--Original Message--
> >> >SQL Server stores tables, indexes etc internally
> >> on "pages", which are 8kb
> >> >disk structures in SQL 7 / 2000. Additionally, pages
> are
> >> allocated in
> >> >"Extents", which are groups of 8 pages (64kb).
> >> >
> >> >PAG: 7:1:165015 denotes {db_id}:{file_id}:{page_no}, so
> >> database #7, file #
> >> >1 & page # 165015 in the internal storage system.
> >> >
> >> >Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
> refers
> >> to database # 8,
> >> >Object # 1653632984 & Index #2. You can identify the
> >> index by looking up
> >> >that objectid in the sysobjects system table.. It's
> >> likely a table & the
> >> >index is the second index on that table.
> >> >
> >> >This type of information is generally displayed in
> >> locking analysis tools
> >> >such as deadlock graphs or output from sp_lock. Where
> are
> >> you seeing it? And
> >> >do you have a wider problem?
> >> >
> >> >Regards,
> >> >Greg Linwood
> >> >SQL Server MVP
> >> >
> >> >
> >> >":)" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:059e01c3c54c$68883850$a101280a@.phx.gbl...
> >> >> Can anyone tell me what this is :
> >> >>
> >> >> PAG: 7:1:165015
> >> >>
> >> >> Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
> >> >>
> >> >> Thanxs
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

help

hi i need some help.I tried using alter table tablename <modify columnname datatype> to change the primary key of the table but it didnt work.I was prompted that there is an error because of using the term modify.what is the command that i should use?

Quote:

Originally Posted by izzo

hi i need some help.I tried using alter table tablename <modify columnname datatype> to change the primary key of the table but it didnt work.I was prompted that there is an error because of using the term modify.what is the command that i should use?

Hi
I don't think you can modify the primary key. You need to delete it and then create a new one on a different column|||first drop the primary key constraint
alter table tablename drop pk_constraint
then add a new constraint to whatever column you want to add it on to, making sure that the values in the column obliges the rules of primary key.
alter table tablename add constraint pk_col1 primary key(col1)

hope this one helps u