I'm trying to find the actual data row that was locked in
a blocking situation. I have the info from the
syslockinfo table at the time the blocking was occuring as
well as the resource, as well as other assorted facts. It
is a key resource (clustered index). Is there a way to
pull out the rowid and find the row?
resource = (580168b11779)John,
The resource you cite appears to be the hash value of the index key. If you
could include the entire set of lock rows, it might be easier to figure out
how to find out the actual row. It might be a secondary index lock for the
row.
Ron
--
Ron Talmage
SQL Server MVP
"jehub" <john.hubbard@.oneneck.com> wrote in message
news:010801c3635c$0bd7f7f0$a501280a@.phx.gbl...
> I'm trying to find the actual data row that was locked in
> a blocking situation. I have the info from the
> syslockinfo table at the time the blocking was occuring as
> well as the resource, as well as other assorted facts. It
> is a key resource (clustered index). Is there a way to
> pull out the rowid and find the row?
> resource = (580168b11779)
>
Showing posts with label row. Show all posts
Showing posts with label row. Show all posts
Monday, March 12, 2012
Friday, March 9, 2012
KeepTogether not working
I have a main report that contains a table. One detail row of that table
calls to a subreport (which also contains a table). When the entire report
displays, if the main report and subreport data will all fit on one page it
will do so, but if not, the main report goes part of the way down a page
(leaving up to 3/4 of the page blank), then breaks to a new page to display
the subreport data. It seems to try and keep the subreport data table
together more than the main report table. I have KeepTogether set to False on
both tables in the main report and the subreport. The way it is now, the
display format is unacceptable. Please offer any ideas.Stotts, I have exactly the same problem as you. Have you found an answer yet?
"stotts" wrote:
> I have a main report that contains a table. One detail row of that table
> calls to a subreport (which also contains a table). When the entire report
> displays, if the main report and subreport data will all fit on one page it
> will do so, but if not, the main report goes part of the way down a page
> (leaving up to 3/4 of the page blank), then breaks to a new page to display
> the subreport data. It seems to try and keep the subreport data table
> together more than the main report table. I have KeepTogether set to False on
> both tables in the main report and the subreport. The way it is now, the
> display format is unacceptable. Please offer any ideas.|||No, not yet. I applied SP1 hoping it would fix it but no luck. Please let me
know if you find anything.
"Pierre" wrote:
> Stotts, I have exactly the same problem as you. Have you found an answer yet?
> "stotts" wrote:
> > I have a main report that contains a table. One detail row of that table
> > calls to a subreport (which also contains a table). When the entire report
> > displays, if the main report and subreport data will all fit on one page it
> > will do so, but if not, the main report goes part of the way down a page
> > (leaving up to 3/4 of the page blank), then breaks to a new page to display
> > the subreport data. It seems to try and keep the subreport data table
> > together more than the main report table. I have KeepTogether set to False on
> > both tables in the main report and the subreport. The way it is now, the
> > display format is unacceptable. Please offer any ideas.
calls to a subreport (which also contains a table). When the entire report
displays, if the main report and subreport data will all fit on one page it
will do so, but if not, the main report goes part of the way down a page
(leaving up to 3/4 of the page blank), then breaks to a new page to display
the subreport data. It seems to try and keep the subreport data table
together more than the main report table. I have KeepTogether set to False on
both tables in the main report and the subreport. The way it is now, the
display format is unacceptable. Please offer any ideas.Stotts, I have exactly the same problem as you. Have you found an answer yet?
"stotts" wrote:
> I have a main report that contains a table. One detail row of that table
> calls to a subreport (which also contains a table). When the entire report
> displays, if the main report and subreport data will all fit on one page it
> will do so, but if not, the main report goes part of the way down a page
> (leaving up to 3/4 of the page blank), then breaks to a new page to display
> the subreport data. It seems to try and keep the subreport data table
> together more than the main report table. I have KeepTogether set to False on
> both tables in the main report and the subreport. The way it is now, the
> display format is unacceptable. Please offer any ideas.|||No, not yet. I applied SP1 hoping it would fix it but no luck. Please let me
know if you find anything.
"Pierre" wrote:
> Stotts, I have exactly the same problem as you. Have you found an answer yet?
> "stotts" wrote:
> > I have a main report that contains a table. One detail row of that table
> > calls to a subreport (which also contains a table). When the entire report
> > displays, if the main report and subreport data will all fit on one page it
> > will do so, but if not, the main report goes part of the way down a page
> > (leaving up to 3/4 of the page blank), then breaks to a new page to display
> > the subreport data. It seems to try and keep the subreport data table
> > together more than the main report table. I have KeepTogether set to False on
> > both tables in the main report and the subreport. The way it is now, the
> > display format is unacceptable. Please offer any ideas.
keeping track of modification date of a row
timestamp type seems to be the design to keep tracking of the modification
date (at least it's convertible to datetime) of a row; but is there a better
way to keep track it with a datetime type? I hope to avoid doing the
conversion everytime I need to look at the data (many rows at a time.)
Creating trigger is obviously possible but I hope for something simpler.
thanks!
TIMESTAMP has absolutely nothing to do with date or time! Can you show how
you are converting it to a datetime value, and demonstrate a case where
TIMESTAMP is accurately tracking the last date/time a row was updated?
Use a LastUpdatedDate column and update it with a trigger or, if you control
access to the table via stored procedures, you can use the stored procedure
to include an update to that column whenever any other value in the row is
touched.
"Zester" <zeze@.nottospam.com> wrote in message
news:eteRVyfWIHA.5716@.TK2MSFTNGP05.phx.gbl...
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a
> better way to keep track it with a datetime type? I hope to avoid doing
> the conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>
|||Hi Zester,
If you are working either with SQL Server 2000 or 2005, you have no other
chance than triggers.
But if you can wait until SQL Server 2008 arrives, things will be different.
You will have tracking features included on the server, along with two new
datatypes: DATE and TIME. (Separated at last!!!)
Hope this would be helpful. Please, rate this post. Thanks!
May the bytes be with you!!!
Pedro López-Belmonte Eraso
MCAD, MCT
"Zester" wrote:
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a better
> way to keep track it with a datetime type? I hope to avoid doing the
> conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>
>
date (at least it's convertible to datetime) of a row; but is there a better
way to keep track it with a datetime type? I hope to avoid doing the
conversion everytime I need to look at the data (many rows at a time.)
Creating trigger is obviously possible but I hope for something simpler.
thanks!
TIMESTAMP has absolutely nothing to do with date or time! Can you show how
you are converting it to a datetime value, and demonstrate a case where
TIMESTAMP is accurately tracking the last date/time a row was updated?
Use a LastUpdatedDate column and update it with a trigger or, if you control
access to the table via stored procedures, you can use the stored procedure
to include an update to that column whenever any other value in the row is
touched.
"Zester" <zeze@.nottospam.com> wrote in message
news:eteRVyfWIHA.5716@.TK2MSFTNGP05.phx.gbl...
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a
> better way to keep track it with a datetime type? I hope to avoid doing
> the conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>
|||Hi Zester,
If you are working either with SQL Server 2000 or 2005, you have no other
chance than triggers.
But if you can wait until SQL Server 2008 arrives, things will be different.
You will have tracking features included on the server, along with two new
datatypes: DATE and TIME. (Separated at last!!!)
Hope this would be helpful. Please, rate this post. Thanks!
May the bytes be with you!!!
Pedro López-Belmonte Eraso
MCAD, MCT
"Zester" wrote:
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a better
> way to keep track it with a datetime type? I hope to avoid doing the
> conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>
>
keeping track of modification date of a row
timestamp type seems to be the design to keep tracking of the modification
date (at least it's convertible to datetime) of a row; but is there a better
way to keep track it with a datetime type? I hope to avoid doing the
conversion everytime I need to look at the data (many rows at a time.)
Creating trigger is obviously possible but I hope for something simpler.
thanks!TIMESTAMP has absolutely nothing to do with date or time! Can you show how
you are converting it to a datetime value, and demonstrate a case where
TIMESTAMP is accurately tracking the last date/time a row was updated?
Use a LastUpdatedDate column and update it with a trigger or, if you control
access to the table via stored procedures, you can use the stored procedure
to include an update to that column whenever any other value in the row is
touched.
"Zester" <zeze@.nottospam.com> wrote in message
news:eteRVyfWIHA.5716@.TK2MSFTNGP05.phx.gbl...
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a
> better way to keep track it with a datetime type? I hope to avoid doing
> the conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>|||Hi Zester,
If you are working either with SQL Server 2000 or 2005, you have no other
chance than triggers.
But if you can wait until SQL Server 2008 arrives, things will be different.
You will have tracking features included on the server, along with two new
datatypes: DATE and TIME. (Separated at last!!!)
Hope this would be helpful. Please, rate this post. Thanks!
--
May the bytes be with you!!!
Pedro López-Belmonte Eraso
MCAD, MCT
"Zester" wrote:
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a better
> way to keep track it with a datetime type? I hope to avoid doing the
> conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>
>
date (at least it's convertible to datetime) of a row; but is there a better
way to keep track it with a datetime type? I hope to avoid doing the
conversion everytime I need to look at the data (many rows at a time.)
Creating trigger is obviously possible but I hope for something simpler.
thanks!TIMESTAMP has absolutely nothing to do with date or time! Can you show how
you are converting it to a datetime value, and demonstrate a case where
TIMESTAMP is accurately tracking the last date/time a row was updated?
Use a LastUpdatedDate column and update it with a trigger or, if you control
access to the table via stored procedures, you can use the stored procedure
to include an update to that column whenever any other value in the row is
touched.
"Zester" <zeze@.nottospam.com> wrote in message
news:eteRVyfWIHA.5716@.TK2MSFTNGP05.phx.gbl...
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a
> better way to keep track it with a datetime type? I hope to avoid doing
> the conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>|||Hi Zester,
If you are working either with SQL Server 2000 or 2005, you have no other
chance than triggers.
But if you can wait until SQL Server 2008 arrives, things will be different.
You will have tracking features included on the server, along with two new
datatypes: DATE and TIME. (Separated at last!!!)
Hope this would be helpful. Please, rate this post. Thanks!
--
May the bytes be with you!!!
Pedro López-Belmonte Eraso
MCAD, MCT
"Zester" wrote:
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a better
> way to keep track it with a datetime type? I hope to avoid doing the
> conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>
>
Wednesday, March 7, 2012
keeping a lock on table or row
I need to update a row but keep a lock on the table (so no one else can update it) while I do run some more code. In Oracle, it always locks whatever you update until you hit commit, but sql server works opposite. How do I tell it not to commit a statement, or how would I explicitly get a lock and then release it later?You can do it with optimizer hints:
HOLDLOCK, UPDLOCK, PAGLOCK, TABLOCK, TABLOCKX|||thanks for your reply!
Would you elborate for me how to do that, I'm not familiar with SQL Server.
I would like to be able to do this in a single statement if possible, perhaps something to append to a regular update statement to hold the lock? (I tried appending 'holdlock' to the statement but that dosen't appear to be correct syntax)
thanks|||Not True
BEGIN TRAN
Some code....
won't COMMIT until you COMMIT the TRAN with COMMIT TRAN...
Sure in SQL you have to start the transaction..
and in Oracle that's a setting that you can change...
Also, I really hope this is not for an application your building and just in SQL+
In any event getting in and out as FAST as possible should be the way to think...regardless of what you're doing...|||begin tran
select * from your_table (TABLOCK) where 1=2
update your_table set...where...
commit tran
HOLDLOCK, UPDLOCK, PAGLOCK, TABLOCK, TABLOCKX|||thanks for your reply!
Would you elborate for me how to do that, I'm not familiar with SQL Server.
I would like to be able to do this in a single statement if possible, perhaps something to append to a regular update statement to hold the lock? (I tried appending 'holdlock' to the statement but that dosen't appear to be correct syntax)
thanks|||Not True
BEGIN TRAN
Some code....
won't COMMIT until you COMMIT the TRAN with COMMIT TRAN...
Sure in SQL you have to start the transaction..
and in Oracle that's a setting that you can change...
Also, I really hope this is not for an application your building and just in SQL+
In any event getting in and out as FAST as possible should be the way to think...regardless of what you're doing...|||begin tran
select * from your_table (TABLOCK) where 1=2
update your_table set...where...
commit tran
keep together
Hi. I have two groups and a detail row in a table.First is ProductGroup, second is ProductSubgroup and the detail area's name is ProductName.
My problem is that I want to keep the detail and the ProductSubgroup (second group area) together.For example sometimes ProductSubgroup is written at the end of the page and the detail area is on the next page or the ProductGroup (first group area) is written at the end of page and the ProductSubgroup (second group area) is written on the next page.And I don't want this.But for example the ProductSubgroup area contains too many detail rows and this time I want to split the records two pages.I hope you help me.Thanks!There are a couple way that you can mitigate this issue--at least part of it--of table groups being split across pages.
My problem is that I want to keep the detail and the ProductSubgroup (second group area) together.For example sometimes ProductSubgroup is written at the end of the page and the detail area is on the next page or the ProductGroup (first group area) is written at the end of page and the ProductSubgroup (second group area) is written on the next page.And I don't want this.But for example the ProductSubgroup area contains too many detail rows and this time I want to split the records two pages.I hope you help me.Thanks!There are a couple way that you can mitigate this issue--at least part of it--of table groups being split across pages.
The first way is to use nested Lists, where
the inner list is set to be kept together on the same page, if
possible. Unlike Tables where only the Table itself can be set to be kept together, each of the nested lists can be set to be kept together. This is a checkbox at the bottom of the general tab of the
List properties dialog in the ReportDesigner.
The second potential resolution would be
enable RepeatGroupHeaders and/or footers, so that at
least the continuation of the group on subsequent pages will contain the header and/or
footer on all pages the group spans. This is a checkbox at bottom of the
general tab of the Grouping and Sorting property dialog.|||
Hi Ian,
What would be the overhead in terms of performance for processing the nested lists ?
I tried adding a nested table for a similar issue and the performance overhead was about 10mSec for processing a report that contained that had about 44 Rows (rows returned by the query). I got this figures using the Report Execution Log.
Regards,
Ashish
keep together
Hi. I have two groups and a detail row in a table.First is ProductGroup, second is ProductSubgroup and the detail area's name is ProductName.
My problem is that I want to keep the detail and the ProductSubgroup (second group area) together.For example sometimes ProductSubgroup is written at the end of the page and the detail area is on the next page or the ProductGroup (first group area) is written at the end of page and the ProductSubgroup (second group area) is written on the next page.And I don't want this.But for example the ProductSubgroup area contains too many detail rows and this time I want to split the records two pages.I hope you help me.Thanks!There are a couple way that you can mitigate this issue--at least part of it--of table groups being split across pages.
My problem is that I want to keep the detail and the ProductSubgroup (second group area) together.For example sometimes ProductSubgroup is written at the end of the page and the detail area is on the next page or the ProductGroup (first group area) is written at the end of page and the ProductSubgroup (second group area) is written on the next page.And I don't want this.But for example the ProductSubgroup area contains too many detail rows and this time I want to split the records two pages.I hope you help me.Thanks!There are a couple way that you can mitigate this issue--at least part of it--of table groups being split across pages.
The first way is to use nested Lists, where
the inner list is set to be kept together on the same page, if
possible. Unlike Tables where only the Table itself can be set to be kept together, each of the nested lists can be set to be kept together. This is a checkbox at the bottom of the general tab of the
List properties dialog in the ReportDesigner.
The second potential resolution would be
enable RepeatGroupHeaders and/or footers, so that at
least the continuation of the group on subsequent pages will contain the header and/or
footer on all pages the group spans. This is a checkbox at bottom of the
general tab of the Grouping and Sorting property dialog.|||
Hi Ian,
What would be the overhead in terms of performance for processing the nested lists ?
I tried adding a nested table for a similar issue and the performance overhead was about 10mSec for processing a report that contained that had about 44 Rows (rows returned by the query). I got this figures using the Report Execution Log.
Regards,
Ashish
Subscribe to:
Posts (Atom)