Problem with Lesson 3 in the Analysis Services Tutorial

I am at the step of "Adding an Attribute and Defining Display Folders" where I am experiencing problems. The steps are:

2. In the Attributes pane, select the following attributes by holding down
the CTRL key to select multiple attributes, and then in the Properties
window, set the AttributeHierarchyDisplayFolder property for the selected attributes to "Location":

? City

? Country-Region

? Postal Code

? State-Province

3. In the Hierarchies and Levels pane, click Customer Geography, and then select "Location" as the value of the DisplayFolder property in the
Properties window.

The problem is that there are no choices in the DisplayFolder property of the Customer Geography hierarchy. I do not know how to force "Location" into the property drop down list. The rest of the tutorial has been fine up to this point. What am I missing?

I would guess probably you missed something when defining the cube (Lesson 2). All I can suggest is to start from scratch and do it again carefully following the tutorial instructions.

|||

hello,

i think you should be able to just type in the Location as the value for the display folder in the properties window (as i'd expect that in addition to picking from the list of available values, you should be able to also enter a new one).

hope this helps,

Problem with HTML slightly garbled sending email as web archive

I'm experiencing a problem with HTML code being slightly messed up when
sending a subscription via email in web archive format.
The report displays fine in the Report Manager. However, went emailed some
whitespace is seemingly randomly added to the HTML page. Depending on where
that whitespace is, this might not be a problem. But if the whitespace is
inserted in some places within an HTML tag, then the tag isn't rendered
properly.
Example: a space is added following the "<" in "</DIV>", thus giving "<
/DIV>". Since this is invalid HTML, it displays as text in the report.
Example: a cell in a table is defined as right-aligned and in a specific
font. All 25 rows in the table (x 3 columns = 75 cells) all display
correctly:
<P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
face="Eras Medium ITC" color=black size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
Medium ITC'">
*EXCEPT* one cell, where the following HTML is used:
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">
Why did RS render this cell differently?This might be an issue with the local SMTP. Are you using the pickup
directory to send email?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
> I'm experiencing a problem with HTML code being slightly messed up when
> sending a subscription via email in web archive format.
> The report displays fine in the Report Manager. However, went emailed
> some
> whitespace is seemingly randomly added to the HTML page. Depending on
> where
> that whitespace is, this might not be a problem. But if the whitespace is
> inserted in some places within an HTML tag, then the tag isn't rendered
> properly.
> Example: a space is added following the "<" in "</DIV>", thus giving "<
> /DIV>". Since this is invalid HTML, it displays as text in the report.
> Example: a cell in a table is defined as right-aligned and in a specific
> font. All 25 rows in the table (x 3 columns = 75 cells) all display
> correctly:
> <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
> face="Eras Medium ITC" color=black size=2><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
> Medium ITC'">
> *EXCEPT* one cell, where the following HTML is used:
> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
> style="FONT-SIZE: 12pt">
> Why did RS render this cell differently?|||Thanks for your reply, Daniel.
I've tried this with two SMTP mail servers, both with the same result. I
changed the definition via the RSReportServer.config file.
Neither SMTP server is on my local machine.
One resides on a Win2003 box and is a MS mail server; the other resides on a
Win2000 Server box and is a product called Merak Mail Server. Ultimately
they send via the Columbia University mail server, which is sendmail, I
believe.
(I'm not a systems guy so sorry if my answers aren't dead-on what you asked
for.)
Please also see my comments on another post title "Web archive subrsciption
gets messed up" (note misspelling of 'subscription'). I ran into an
identical problem like this when sending email from an Oracle PL/SQL program.
The resolution is to intersprese CRLF into the msg at least every 990 bytes.
Thanks Daniel. This is the one problem that's keeping me from implementing
RS and beginning to retire Crystal Enterprise.
"Daniel Reib [MSFT]" wrote:
> This might be an issue with the local SMTP. Are you using the pickup
> directory to send email?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
> news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
> > I'm experiencing a problem with HTML code being slightly messed up when
> > sending a subscription via email in web archive format.
> >
> > The report displays fine in the Report Manager. However, went emailed
> > some
> > whitespace is seemingly randomly added to the HTML page. Depending on
> > where
> > that whitespace is, this might not be a problem. But if the whitespace is
> > inserted in some places within an HTML tag, then the tag isn't rendered
> > properly.
> >
> > Example: a space is added following the "<" in "</DIV>", thus giving "<
> > /DIV>". Since this is invalid HTML, it displays as text in the report.
> >
> > Example: a cell in a table is defined as right-aligned and in a specific
> > font. All 25 rows in the table (x 3 columns = 75 cells) all display
> > correctly:
> > <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
> > face="Eras Medium ITC" color=black size=2><SPAN
> > style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
> > Medium ITC'">
> > *EXCEPT* one cell, where the following HTML is used:
> > <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
> > style="FONT-SIZE: 12pt">
> > Why did RS render this cell differently?
>
>|||I haven't done this yet but what was suggested to me (I too am having
problems and am using an external smtp server too) was to use Windows 2003
smtp service and have it forward/route (not sure the term) to the external
smtp server. The Windows 2003 smtp server is configured to be looking at a
directory and forwarding things on from there. Then you have your report
saved to the pickup directory. Note that I haven't done this so I am a
little vague on how it all works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Columbia Kai" <ColumbiaKai@.discussions.microsoft.com> wrote in message
news:8995F878-CB68-4766-BB00-654D7580BAF1@.microsoft.com...
> Thanks for your reply, Daniel.
> I've tried this with two SMTP mail servers, both with the same result. I
> changed the definition via the RSReportServer.config file.
> Neither SMTP server is on my local machine.
> One resides on a Win2003 box and is a MS mail server; the other resides on
> a
> Win2000 Server box and is a product called Merak Mail Server. Ultimately
> they send via the Columbia University mail server, which is sendmail, I
> believe.
> (I'm not a systems guy so sorry if my answers aren't dead-on what you
> asked
> for.)
> Please also see my comments on another post title "Web archive
> subrsciption
> gets messed up" (note misspelling of 'subscription'). I ran into an
> identical problem like this when sending email from an Oracle PL/SQL
> program.
> The resolution is to intersprese CRLF into the msg at least every 990
> bytes.
> Thanks Daniel. This is the one problem that's keeping me from
> implementing
> RS and beginning to retire Crystal Enterprise.
>
> "Daniel Reib [MSFT]" wrote:
>> This might be an issue with the local SMTP. Are you using the pickup
>> directory to send email?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
>> news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
>> > I'm experiencing a problem with HTML code being slightly messed up when
>> > sending a subscription via email in web archive format.
>> >
>> > The report displays fine in the Report Manager. However, went emailed
>> > some
>> > whitespace is seemingly randomly added to the HTML page. Depending on
>> > where
>> > that whitespace is, this might not be a problem. But if the whitespace
>> > is
>> > inserted in some places within an HTML tag, then the tag isn't rendered
>> > properly.
>> >
>> > Example: a space is added following the "<" in "</DIV>", thus giving "<
>> > /DIV>". Since this is invalid HTML, it displays as text in the report.
>> >
>> > Example: a cell in a table is defined as right-aligned and in a
>> > specific
>> > font. All 25 rows in the table (x 3 columns = 75 cells) all display
>> > correctly:
>> > <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
>> > face="Eras Medium ITC" color=black size=2><SPAN
>> > style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY:
>> > 'Eras
>> > Medium ITC'">
>> > *EXCEPT* one cell, where the following HTML is used:
>> > <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
>> > style="FONT-SIZE: 12pt">
>> > Why did RS render this cell differently?
>>sql

Problem with dead-locks

We are experiencing some problems with deadlocks in our system. We call
Sql server 2k from a .Net applicationlayer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every w. The external
system cannot give os delta-information, e.g. only new data, s they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
Findes din kiosk p nettet? Se http://ekiosk.dkHave you followed the basic recommendations on deadlocks in Books Online?
Start here, if you haven't:
http://msdn.microsoft.com/library/d... />
a_3hdf.asp
Tracing deadlocks might also be much help:
http://msdn.microsoft.com/library/d...
tabse_5xrn.asp
ML|||I have two suggestions. (1) change the mechanism so that the deletes are
committed prior to starting the bulk inserts, or (2) generate a temporary
stored procedure or dynamic SQL to execute the entire job in a single batch,
complete with error handling, etc.
You could save off the rows your deleting into a separate database, and
reinsert them if a failure occurs during the bulk inserts.
My preference is to migrate transaction processing to the data tier whenever
possible. It's a lot easier to modify a stored procedure than to recompile
and redeploy a middle-tier component or God forbid, a client application.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97105AA34EC40stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net applicationlayer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every w. The external
> system cannot give os delta-information, e.g. only new data, s they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
> Findes din kiosk p nettet? Se http://ekiosk.dk|||"Brian Selzer" <brian@.selzer-software.com> wrote in
news:epOZG6w6FHA.3416@.TK2MSFTNGP15.phx.gbl:

> I have two suggestions. (1) change the mechanism so that the deletes
> are committed prior to starting the bulk inserts, or (2) generate a
> temporary stored procedure or dynamic SQL to execute the entire job in
> a single batch, complete with error handling, etc.
I will look into this, thanks.

> My preference is to migrate transaction processing to the data tier
> whenever possible. It's a lot easier to modify a stored procedure
> than to recompile and redeploy a middle-tier component or God forbid,
> a client application.
We have since my first post moved the SQL to the database in a stored
procedure that creates some dynamic sql and executes it, so it is no longer
in the application layer. The problem, however, did not go away.
Also, we cannot do it all in the application layer. The entire process is a
ETL-process, where the data is (heavily) transformed before being loaded
into the (other) database. It is not possible to put some of the key parts
in the database-layer - for various purposes we need to do the
transformation in the application layer.
I talked to one of our DBAs, and he suggested that another benefit of
moving the sql to a stored procedure would be that it would complete the
current batch after each "GO"-statement at the end of executing the stored
procedure. We moved the SQL-code and the problem seems to have dissapeared
... for now at least. We are keeping our fingers crossed. If this works, we
are happy ... a bit nervous that we didn't find the cause of the error ...
but happy, none the less.
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>

Problem with dead-locks

We are experiencing some problems with deadlocks in our system. We call
Sql server 2k from a .Net application-layer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every week. The external
system cannot give os delta-information, e.g. only new data, s they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
Please turn on TF-1204 and see what is getting deadlocked.
thanks
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97107DFDEF6A4stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net application-layer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every week. The external
> system cannot give os delta-information, e.g. only new data, s they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
>
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in news:e2N
$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:

> Please turn on TF-1204 and see what is getting deadlocked.
It looks interesting - I didn't know of this.
I will look into it,
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:

> Please turn on TF-1204 and see what is getting deadlocked.
We have turned Trace (3605, 1205, 1204, -1) on and the results of
this is:
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID) 86.
2005-11-17 20:26:31.03 spid3 --
2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
2005-11-17 20:26:31.03 spid3 Target Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ... verifying cycle
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
Deadlock encountered ... Printing deadlock information
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Wait-for graph
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Node:1
2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1 Mode: IX Flags: 0x0
2005-11-17 20:26:31.03 spid3 Grant List 1::
2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK INSERT Line #: 1
2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT TAB_RENS_TILLADELSE FROM '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_03 06_TAB_RENS_TILLADELSE.tab' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1, KEEPIDENTITY
, KEEPNULLS, ROWTERMINATOR = '
', FIELDTERMINATOR = '')
2005-11-17 20:26:31.03 spid3 Requested By:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was found.
2005-11-17 20:26:31.03 spid3 --
The content is also available on
http://dotnet.stocholm.dk/database/trace.zip
Can you help us decipher this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
|||Here is the feedback from dev. One suggestion is to upgrade to SP4, if not
already done. If the problem still shows up, please contact PSS
"It looks like a self-deadlock - i.e. we're using two incompatible
transactions in the same thread. The cycle looks like:
Spid86-Xact_T1 holds an IX lock.
Spid86-Xact_T2 requests an SCH_M lock which is incompatible with the IX.
Thus spid 86 is waiting for spid 86 which completes a deadlock cycle.
This is a bug, we shouldn't get into these self-deadlocks."
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns9711D30914A23stocholmdk@.207.46.248.16...
> "Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
> news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
>
> We have turned Trace (3605, 1205, 1204, -1) on and the results of
> this is:
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID)
> 86.
> 2005-11-17 20:26:31.03 spid3 --
> 2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
> 2005-11-17 20:26:31.03 spid3 Target Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ...
> verifying cycle
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> Cost0/0)
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> Cost0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> Deadlock encountered ... Printing deadlock information
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Wait-for graph
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Node:1
> 2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1
> Mode: IX Flags: 0x0
> 2005-11-17 20:26:31.03 spid3 Grant List 1::
> 2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX
> Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
> 2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK
> INSERT Line #: 1
> 2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT
> TAB_RENS_TILLADELSE FROM
> '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_03 06_TAB_RENS_TILLADELSE.tab'
> WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1,
> KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
> ', FIELDTERMINATOR = ' ')
> 2005-11-17 20:26:31.03 spid3 Requested By:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
> 2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was
> found.
> 2005-11-17 20:26:31.03 spid3 --
>
> The content is also available on
> http://dotnet.stocholm.dk/database/trace.zip
> Can you help us decipher this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:#4himgJ7FHA.3276@.TK2MSFTNGP15.phx.gbl:

> Here is the feedback from dev. One suggestion is to upgrade to SP4, if
> not already done. If the problem still shows up, please contact PSS
We upgraded the server to SP4 and the problem has not ocurred since ... so
we are keeping our fingers crossed.
:o)
Thanks for your help.
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk

Problem with dead-locks

We are experiencing some problems with deadlocks in our system. We call
Sql server 2k from a .Net application-layer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every week. The external
system cannot give os delta-information, e.g. only new data, s they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>Please turn on TF-1204 and see what is getting deadlocked.
thanks
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97107DFDEF6A4stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net application-layer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every week. The external
> system cannot give os delta-information, e.g. only new data, s they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
>
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in news:e2N
$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:

> Please turn on TF-1204 and see what is getting deadlocked.
It looks interesting - I didn't know of this.
I will look into it,
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:

> Please turn on TF-1204 and see what is getting deadlocked.
We have turned Trace (3605, 1205, 1204, -1) on and the results of
this is:
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID (SPID)
86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID (SPID)
86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID (SPID)
86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID) 8
6.
2005-11-17 20:26:31.03 spid3 --
2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
2005-11-17 20:26:31.03 spid3 Target Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-
Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ... verifyi
ng cycle
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
Deadlock encountered ... Printing deadlock information
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Wait-for graph
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Node:1
2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt
:1 Mode: IX Flags: 0x0
2005-11-17 20:26:31.03 spid3 Grant List 1::
2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX Flg:0x0
Ref:1 Life:02000000 SPID:86 ECID:0
2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK IN
SERT Line #: 1
2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT T
AB_RENS_TILLADELSE FROM '\\MPDB03\Fileshares\MPReadyToUpload\000
0023764_0306
_TAB_RENS_TILLADELSE.tab' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar',
FIRSTROW = 1, KEEPIDENTITY
, KEEPNULLS, ROWTERMINATOR = '
', FIELDTERMINATOR = ' ')
2005-11-17 20:26:31.03 spid3 Requested By:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schem
a-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-
Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was f
ound.
2005-11-17 20:26:31.03 spid3 --
The content is also available on
http://dotnet.stocholm.dk/database/trace.zip
Can you help us decipher this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||Here is the feedback from dev. One suggestion is to upgrade to SP4, if not
already done. If the problem still shows up, please contact PSS
"It looks like a self-deadlock - i.e. we're using two incompatible
transactions in the same thread. The cycle looks like:
Spid86-Xact_T1 holds an IX lock.
Spid86-Xact_T2 requests an SCH_M lock which is incompatible with the IX.
Thus spid 86 is waiting for spid 86 which completes a deadlock cycle.
This is a bug, we shouldn't get into these self-deadlocks."
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns9711D30914A23stocholmdk@.207.46.248.16...
> "Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
> news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
>
> We have turned Trace (3605, 1205, 1204, -1) on and the results of
> this is:
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID)
> 86.
> 2005-11-17 20:26:31.03 spid3 --
> 2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
> 2005-11-17 20:26:31.03 spid3 Target Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ...
> verifying cycle
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> Cost0/0)
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> Cost0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> Deadlock encountered ... Printing deadlock information
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Wait-for graph
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Node:1
> 2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanC
nt:1
> Mode: IX Flags: 0x0
> 2005-11-17 20:26:31.03 spid3 Grant List 1::
> 2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX
> Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
> 2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK
> INSERT Line #: 1
> 2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT
> TAB_RENS_TILLADELSE FROM
> '\\MPDB03\Fileshares\MPReadyToUpload\000
0023764_0306_TAB_RENS_TILLADELSE.t
ab'
> WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1,
> KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
> ', FIELDTERMINATOR = ' ')
> 2005-11-17 20:26:31.03 spid3 Requested By:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
> 2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was
> found.
> 2005-11-17 20:26:31.03 spid3 --
>
> The content is also available on
> http://dotnet.stocholm.dk/database/trace.zip
> Can you help us decipher this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:#4himgJ7FHA.3276@.TK2MSFTNGP15.phx.gbl:

> Here is the feedback from dev. One suggestion is to upgrade to SP4, if
> not already done. If the problem still shows up, please contact PSS
We upgraded the server to SP4 and the problem has not ocurred since ... so
we are keeping our fingers crossed.
:o)
Thanks for your help.
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk

Problem with dead-locks

We are experiencing some problems with deadlocks in our system. We call
Sql server 2k from a .Net application-layer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every week. The external
system cannot give os delta-information, e.g. only new data, så they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>Please turn on TF-1204 and see what is getting deadlocked.
thanks
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97107DFDEF6A4stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net application-layer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every week. The external
> system cannot give os delta-information, e.g. only new data, så they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
>
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in news:e2N
$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
It looks interesting - I didn't know of this.
I will look into it,
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
We have turned Trace (3605, 1205, 1204, -1) on and the results of
this is:
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID) 86.
2005-11-17 20:26:31.03 spid3 --
2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
2005-11-17 20:26:31.03 spid3 Target Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ... verifying cycle
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
Deadlock encountered ... Printing deadlock information
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Wait-for graph
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Node:1
2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1 Mode: IX Flags: 0x0
2005-11-17 20:26:31.03 spid3 Grant List 1::
2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK INSERT Line #: 1
2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT TAB_RENS_TILLADELSE FROM '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_0306_TAB_RENS_TILLADELSE.tab' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1, KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
', FIELDTERMINATOR = ' ')
2005-11-17 20:26:31.03 spid3 Requested By:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was found.
2005-11-17 20:26:31.03 spid3 --
The content is also available on
http://dotnet.stocholm.dk/database/trace.zip
Can you help us decipher this?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||Here is the feedback from dev. One suggestion is to upgrade to SP4, if not
already done. If the problem still shows up, please contact PSS
"It looks like a self-deadlock - i.e. we're using two incompatible
transactions in the same thread. The cycle looks like:
Spid86-Xact_T1 holds an IX lock.
Spid86-Xact_T2 requests an SCH_M lock which is incompatible with the IX.
Thus spid 86 is waiting for spid 86 which completes a deadlock cycle.
This is a bug, we shouldn't get into these self-deadlocks."
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns9711D30914A23stocholmdk@.207.46.248.16...
> "Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
> news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
>> Please turn on TF-1204 and see what is getting deadlocked.
> We have turned Trace (3605, 1205, 1204, -1) on and the results of
> this is:
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID)
> 86.
> 2005-11-17 20:26:31.03 spid3 --
> 2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
> 2005-11-17 20:26:31.03 spid3 Target Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ...
> verifying cycle
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> Deadlock encountered ... Printing deadlock information
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Wait-for graph
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Node:1
> 2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1
> Mode: IX Flags: 0x0
> 2005-11-17 20:26:31.03 spid3 Grant List 1::
> 2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX
> Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
> 2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK
> INSERT Line #: 1
> 2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT
> TAB_RENS_TILLADELSE FROM
> '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_0306_TAB_RENS_TILLADELSE.tab'
> WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1,
> KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
> ', FIELDTERMINATOR = ' ')
> 2005-11-17 20:26:31.03 spid3 Requested By:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was
> found.
> 2005-11-17 20:26:31.03 spid3 --
>
> The content is also available on
> http://dotnet.stocholm.dk/database/trace.zip
> Can you help us decipher this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:#4himgJ7FHA.3276@.TK2MSFTNGP15.phx.gbl:
> Here is the feedback from dev. One suggestion is to upgrade to SP4, if
> not already done. If the problem still shows up, please contact PSS
We upgraded the server to SP4 and the problem has not ocurred since ... so
we are keeping our fingers crossed.
:o)
Thanks for your help.
--
Jesper Stocholm
http://stocholm.dk
Findes din kiosk på nettet? Se http://ekiosk.dk

Problem with dbo.sysindexkeys in SQL 2000

Hello,

I am experiencing some performance issues with the dbo.sysindexkeys.

I wonder if anyone can help me create an index on it?

Thanx!

SQL script:

set nocount on
set ansi_warnings off
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Create test database'
print
'--------------------------'

use master
go

if not db_id('test') is null
drop database test
go

create database test
go

use test
go

--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Create 4000 tables in test database'
print
'--------------------------'

declare @.i_iter int

set @.i_iter = 0
while @.i_iter < 4000
begin

if @.i_iter % 100 = 0
print 'Table -> ' + cast(@.i_iter as char(10))

exec ('
create table TABLE_' + @.i_iter + '
( COLUMN_0 int not null,
COLUMN_1 int not null,
COLUMN_2 datetime not null,
COLUMN_3 datetime not null,
COLUMN_4 varchar(100) not null,
COLUMN_5 smallint not null,
COLUMN_6 bit not null,
constraint TABLE_' + @.i_iter + '_PK primary key (COLUMN_0,
COLUMN_1, COLUMN_2)
)
create index TABLE_' + @.i_iter + '_I1 on TABLE_' + @.i_iter + '
(COLUMN_4, COLUMN_5)
')

set @.i_iter = @.i_iter + 1
end
go
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Dump dbo.sysindexkeys in temp. table and create a clustered
index on it'
print
'--------------------------'

if not object_id('dbo.t_sink') is null
drop table dbo.t_sink
go
select * into dbo.t_sink from dbo.sysindexkeys
alter table dbo.t_sink add constraint t_sink_pk primary key ([id] ,
[indid], [colid])
go

print
'--------------------------'
print ' Create a pivot table with all columns in the indexes and
primary keys using this'
print ' temp table'
print
'--------------------------'

if not object_id('tempdb..#dump') is null
drop table #dump
go

declare @.time datetime
set @.time = getdate()

select sobj.name as table_name,
sind.name as index_name,
max(case sink.keyno when 1 then scol.name end) as c01,
max(case sink.keyno when 2 then scol.name end) as c02,
max(case sink.keyno when 3 then scol.name end) as c03,
max(case sink.keyno when 4 then scol.name end) as c04,
max(case sink.keyno when 5 then scol.name end) as c05,
max(case sink.keyno when 6 then scol.name end) as c06,
max(case sink.keyno when 7 then scol.name end) as c07,
max(case sink.keyno when 8 then scol.name end) as c08,
max(case sink.keyno when 9 then scol.name end) as c09
into #dump
from dbo.sysobjects sobj,
dbo.sysindexes sind,
dbo.t_sink sink,
dbo.syscolumns scol
where sobj.xtype = 'u'
and sind.id = sobj.id
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
and sink.id = sobj.id
and sink.indid = sind.indid
and scol.id = sobj.id
and scol.colid = sink.colid
group by sobj.name,
sind.name
order by sobj.name,
sind.name

print 'Rows: ' + cast(@.@.rowcount as char(10))

set @.time = getdate() - @.time
print 'Time: ' + convert(char(25), @.time, 114)
go

if not object_id('dbo.t_sink') is null
drop table dbo.t_sink
go
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Create a pivot table with all columns in the indexes and
primary keys using the'
print ' system tables'
print
'--------------------------'

if not object_id('tempdb..#direct') is null
drop table #direct
go

declare @.time datetime
set @.time = getdate()

select sobj.name as table_name,
sind.name as index_name,
max(case sink.keyno when 1 then scol.name end) as c01,
max(case sink.keyno when 2 then scol.name end) as c02,
max(case sink.keyno when 3 then scol.name end) as c03,
max(case sink.keyno when 4 then scol.name end) as c04,
max(case sink.keyno when 5 then scol.name end) as c05,
max(case sink.keyno when 6 then scol.name end) as c06,
max(case sink.keyno when 7 then scol.name end) as c07,
max(case sink.keyno when 8 then scol.name end) as c08,
max(case sink.keyno when 9 then scol.name end) as c09
into #direct
from dbo.sysobjects sobj,
dbo.sysindexes sind,
dbo.sysindexkeys sink,
dbo.syscolumns scol
where sobj.xtype = 'u'
and sind.id = sobj.id
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
and sink.id = sobj.id
and sink.indid = sind.indid
and scol.id = sobj.id
and scol.colid = sink.colid
group by sobj.name,
sind.name
order by sobj.name,
sind.name

print 'Rows: ' + cast(@.@.rowcount as char(10))

set @.time = getdate() - @.time
print 'Time: ' + convert(char(25), @.time, 114)
goherman (herman@.hotmail.com) writes:
> I am experiencing some performance issues with the dbo.sysindexkeys.
> I wonder if anyone can help me create an index on it?

I guess you could if you change the configuration parameer "allow updates
to system tables", but I have no idea wether SQL Server would actually
look at the index.

Anyway, this query executes almosts as fast as your query with
the temp table:

select sobj.name as table_name,
sind.name as index_name,
index_col(sobj.name, sind.indid, 1) as c01,
index_col(sobj.name, sind.indid, 2) as c02,
index_col(sobj.name, sind.indid, 3) as c03,
index_col(sobj.name, sind.indid, 4) as c04,
index_col(sobj.name, sind.indid, 5) as c05,
index_col(sobj.name, sind.indid, 6) as c06,
index_col(sobj.name, sind.indid, 7) as c07,
index_col(sobj.name, sind.indid, 8) as c08,
index_col(sobj.name, sind.indid, 9) as c09
--into #direct
from dbo.sysobjects sobj
join dbo.sysindexes sind ON sind.id = sobj.id
where sobj.xtype = 'U'
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
order by sobj.name, sind.name

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp