Rowlock appears to be locking entire table.

Without going into a lot of history, I am using embedded SQL in a Micro-Focus

COBOL application.

I have a need to be able to select a specific row from my table and lock that row until I release it.

I have successfully used a declare cursor with a rowlock hint that does lock that row, unfortunately

it also locks the entire table.

I know that SQL Server is best utilized by allowing it it to manage locks, but this is a unique situation

and all I want is a clear answer as to how this should work when I have to do it.

here is a sample of what I have tried. I'm hoping someone has successfully done something

like this (maybe not even in cobol).

EXEC SQL DECLARE TECHLK CURSOR FOR
select
TECH_ID
from TECH_REC
with (rowlock,nowait)
where (TECH_ID = :SQL-TECH-ID)
END-EXEC.

EXEC SQL
OPEN TECHLK
END-EXEC.

EXEC SQL
FETCH TECHLK INTO
:SQL-TECH-ID
END-EXEC.

any help would be appreciated.. thx, Ray Dodson

1. How did you identify that entire table is locked?

2. What queries does your application actually sent to the server? You can figure that out using Profile tool.

3. SQL Server maintain record lock granularity untill it has enough resources for that. Otherwise lock granularity escalated to the table level.

|||

This reply asked more questions than it answered.

1. I used the management item in seql server enterprise manager to see the

locking status.

Actually, I found after this post was sent that my keys were not set up correctly (imported

from another database). that appears to be why I was locking the entire table. I have rectified that

and things seem to be working correctly now.

thanks for you time... Ray Dodson

ROWGUIDCOL

I've read everything BOL says about ROWGUIDCOL and it's still not clear to me why it is necessary. It appears to be similar to an IDENTITY property, except that it does not have a seed or an increment. Because of that, it appears to have no practical purpose other than annotating a CREATE TABLE script to let people know it is used to identify a row.

ROWGUIDCOL as described seems to fit the case of where a guid is the primary key (I do realize it does not enforce uniqueness).

IDENTITY can generate unique values for a key for a single table on a single machine. If you want to automatically generate UNIQUE values accross multiple tables or machines, use a uniqueidentifier column that defaults to NEWID() or is always given a new guid value by your application when your application creates a row. ROWGUIDCOL is just a convenient designator for a GUID-based unique ID column. It also gives you the ability to reference it using $ROWGUID. GUID-based identifiers are used extensively in replication scenarios.

|||Thanks for that clarification. $ROWGUID could be useful. What if the guid is only the leading edge of the primary key, not the entire primary key? In other words, does the guid have to uniquely identify the row for $ROWGUID to work?

NEWSEQUENTIALID() is an alternative to NEWID(). As I posted in http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=52136, Newid in C# caused reduced performance because of index page splits. Using NEWID() in T-SQL has the same problem. Using NEWSEQUENTIALID() instead of NEWID() greatly improved insert performance without any adverse effects on select performance. NEWSEQUENTIALID() provides an ascending sort order to the guids, which makes it more similar to IDENTITY than NEWID() is.sql

Row Size Exceeds - Help!

Hi ,
Can any one of you tell why the below warning message
appears. I have found this when i created a table. Also I
would like to know what are the side effects on this table
& DB, because of this warning.
Warning: The table 'RawMailList' has been created but its
maximum row size (13415) exceeds the maximum number of
bytes per row (8060). INSERT or UPDATE of a row in this
table will fail if the resulting row length exceeds 8060
bytes.
Best Regards
ThirumalSQL Server has a MAX row length of 8060 bytes. You can define a table that
exceeds this as you have done but should you UPDATE/INSERT and go over 8060
bytes in total then that INSERT/UPDATE will fail.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Thirumal" <treddym@.hotmail.com> wrote in message
news:05c701c33eda$39f0a250$a301280a@.phx.gbl...
> Hi ,
> Can any one of you tell why the below warning message
> appears. I have found this when i created a table. Also I
> would like to know what are the side effects on this table
> & DB, because of this warning.
> Warning: The table 'RawMailList' has been created but its
> maximum row size (13415) exceeds the maximum number of
> bytes per row (8060). INSERT or UPDATE of a row in this
> table will fail if the resulting row length exceeds 8060
> bytes.
> Best Regards
> Thirumal|||Thirumal,
You probably have several variable-length columns--type varchar() or
nvarchar(), in each row. For example, if you have three varchar(8000)
columns in a single row, they could account for 24,000 bytes of data in
that row if each contained an 8000-byte string. So long as all the data
in any single row does not exceed 8060 bytes, there won't be a problem,
but if you tried to update the varchar() strings to the point where the row
would contain more than 8060 bytes in all, you would get an error.
The warning is just that, a warning. There is no problem at all so long
as you keep each complete row of the table within the 8060 byte limit.
By the way, the reason for this restriction is that SQL Server uses data
pages of 8K bytes (8192 bytes), and the architecture does not allow a
row to span more than one data page. 8060 bytes of data, together with
the additional information that must be on a data page, fits on one 8K page.
More information simply doesn't fit.
Steve Kass
Drew University
Thirumal wrote:
>Hi ,
>Can any one of you tell why the below warning message
>appears. I have found this when i created a table. Also I
>would like to know what are the side effects on this table
>& DB, because of this warning.
>Warning: The table 'RawMailList' has been created but its
>maximum row size (13415) exceeds the maximum number of
>bytes per row (8060). INSERT or UPDATE of a row in this
>table will fail if the resulting row length exceeds 8060
>bytes.
>Best Regards
>Thirumal
>|||Hi Allan and Steve,
Thanks a ton for clearing my doubts!!
Warm Regards
Thirumal
>--Original Message--
>Thirumal,
> You probably have several variable-length columns--type
varchar() or
>nvarchar(), in each row. For example, if you have three
varchar(8000)
>columns in a single row, they could account for 24,000
bytes of data in
>that row if each contained an 8000-byte string. So long
as all the data
>in any single row does not exceed 8060 bytes, there won't
be a problem,
>but if you tried to update the varchar() strings to the
point where the row
>would contain more than 8060 bytes in all, you would get
an error.
> The warning is just that, a warning. There is no
problem at all so long
>as you keep each complete row of the table within the
8060 byte limit.
> By the way, the reason for this restriction is that SQL
Server uses data
>pages of 8K bytes (8192 bytes), and the architecture does
not allow a
>row to span more than one data page. 8060 bytes of data,
together with
>the additional information that must be on a data page,
fits on one 8K page.
>More information simply doesn't fit.
>Steve Kass
>Drew University
>Thirumal wrote:
>>Hi ,
>>Can any one of you tell why the below warning message
>>appears. I have found this when i created a table. Also
I
>>would like to know what are the side effects on this
table
>>& DB, because of this warning.
>>Warning: The table 'RawMailList' has been created but
its
>>maximum row size (13415) exceeds the maximum number of
>>bytes per row (8060). INSERT or UPDATE of a row in this
>>table will fail if the resulting row length exceeds 8060
>>bytes.
>>Best Regards
>>Thirumal
>>
>.
>

ROW IN A SPESIFIC ORDER

I HAVE FOUR ROWS IN A TABLE & I WANT THEM TO APPEARS IN A SPESIFIC ORDER AND I DON'T WANT TO SORT THEM.
please help meCan you give more datails?
You don't want sort them by SQL sort by clause?
In what kind of order?|||Exp:
SQL Sort LIKE

ASAP

C.F.E.D

Others

Zama

And I want to Sort Like
C.F.E.D
ASAP
ZAMA
OTHERS|||

You'll have to add some additional column to your table, perhaps called SortOrder. Then assign values to it:

SortOrder Value

10 C.F.E.D
20 ASAP
30 ZAMA
40 OTHERS

Then you can ORDER BY SortOrder.

|||but i don't want to show SortOrder. What I have to do for hiding this colunm.

Another Question is that I want to show "pie chart " Data label with percantage and as well as the name of the states
Like

Newyork, Calefornia
(5%) (30%)

Round Trips on Drill Down

Can anyone confirm what's happening when a user click the drill down (+)
button to expand or collapse data.
It appears to be doing a round trip, but does not appear to be re-querying
the data. The round trip process is too fast.
I am using OnDemand reports (no cache or snapshot), and the theory says the
OnDemand dataset is only available for the initial rendering.
Is IIS, .net or RS caching the dataset for the expand process to work.
Does anyone have any good doco on what happens where?
Thanks
JasonI can't give you a good document on what is happening, however I can tell
you that it IS making a round trip.
I have a report with sub-reports, the sub-reports are hidden initially. The
user SHOULD click the + and the div tag that hids the sub report would
change status from invisible to visible. That's not what happens.
I know this because I use SOAP to render my reports from a web-server, not
the SQL server. When the user clicks the + it tries to take them to the SQL
server, not back to my site with my code that launched the thing in the
first place. (RATS!)
"Jason Buck" <jason@.REMOVE_THE_CAPScustombizsolutions.com> wrote in message
news:u9hseEVdEHA.3308@.TK2MSFTNGP11.phx.gbl...
> Can anyone confirm what's happening when a user click the drill down (+)
> button to expand or collapse data.
> It appears to be doing a round trip, but does not appear to be re-querying
> the data. The round trip process is too fast.
> I am using OnDemand reports (no cache or snapshot), and the theory says
the
> OnDemand dataset is only available for the initial rendering.
> Is IIS, .net or RS caching the dataset for the expand process to work.
> Does anyone have any good doco on what happens where?
> Thanks
> Jason
>|||Drilldown would indeed cause a post back to the report server but not a new
report execution. On the initial report execution, the report server creates
a session for you and stores a copy of the data in it. Further navigation in
that report, as well as images, drill-downs will work off that session.
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jason Buck" <jason@.REMOVE_THE_CAPScustombizsolutions.com> wrote in message
news:u9hseEVdEHA.3308@.TK2MSFTNGP11.phx.gbl...
> Can anyone confirm what's happening when a user click the drill down (+)
> button to expand or collapse data.
> It appears to be doing a round trip, but does not appear to be re-querying
> the data. The round trip process is too fast.
> I am using OnDemand reports (no cache or snapshot), and the theory says
the
> OnDemand dataset is only available for the initial rendering.
> Is IIS, .net or RS caching the dataset for the expand process to work.
> Does anyone have any good doco on what happens where?
> Thanks
> Jason
>|||Thanks,
So is this correct.
1. I view an ondemand report, and it creates the Int. Rep (the data) in the
session variable.
2. This data is marked as available only to my session, with some indicator
that it was for this 'run' of the report
3. When I drill down, it re-renders using the data from above.
4. I hit the refresh button on the report, so (being ondemand) it
regenerates the data and creates a new item in the session object for this
run of the report.
5. Now I have 2 Int Reps, in the 1 session object, and the session object
is not going to be destroy until session timeout.
do the individual data sets get destroyed earlier at some time? Where do I
configure this and the session timeout?
thanks
Jason
"Tudor Trufinescu (MSFT)" <tudortr@.ms.com> wrote in message
news:ut4scTddEHA.2696@.TK2MSFTNGP09.phx.gbl...
> Drilldown would indeed cause a post back to the report server but not a
new
> report execution. On the initial report execution, the report server
creates
> a session for you and stores a copy of the data in it. Further navigation
in
> that report, as well as images, drill-downs will work off that session.
> --
> Tudor Trufinescu
> Dev Lead
> Sql Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Jason Buck" <jason@.REMOVE_THE_CAPScustombizsolutions.com> wrote in
message
> news:u9hseEVdEHA.3308@.TK2MSFTNGP11.phx.gbl...
> > Can anyone confirm what's happening when a user click the drill down (+)
> > button to expand or collapse data.
> >
> > It appears to be doing a round trip, but does not appear to be
re-querying
> > the data. The round trip process is too fast.
> >
> > I am using OnDemand reports (no cache or snapshot), and the theory says
> the
> > OnDemand dataset is only available for the initial rendering.
> >
> > Is IIS, .net or RS caching the dataset for the expand process to work.
> >
> > Does anyone have any good doco on what happens where?
> >
> > Thanks
> >
> > Jason
> >
> >
>

Rouge backup

I have an odd problem on a MS SQL2K box, one database on my server is being
backed up to a file; this appears to be an appended backup and the backup
file is now getting quite big.
The thing is, there isn't a maintenance plan to do the backup of this
database.
My problem is I want to stop the back up, but if there?s no job how can I
do that? Theres nothing to stop
I think I did once have a maintenance plan to back up this database but
that would have been deleted quit some time ago. I've only found this
because I'm running out of space on one of my partitions.
Any help/ideas/suggestions will be very very appreciated.
Message posted via http://www.sqlmonster.com
The only thing that a maint plan does is to create a number of SQL Server agent jobs. My guess is
that you have Agent jobs that are performing the backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"James Green via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ae94125dde5c4afc806c9dbcfd71a49b@.SQLMonster.c om...
>I have an odd problem on a MS SQL2K box, one database on my server is being
> backed up to a file; this appears to be an appended backup and the backup
> file is now getting quite big.
> The thing is, there isn't a maintenance plan to do the backup of this
> database.
> My problem is I want to stop the back up, but if there?s no job how can I
> do that? Theres nothing to stop
> I think I did once have a maintenance plan to back up this database but
> that would have been deleted quit some time ago. I've only found this
> because I'm running out of space on one of my partitions.
> Any help/ideas/suggestions will be very very appreciated.
> --
> Message posted via http://www.sqlmonster.com
|||Hello James,
As Tibor mentioned, the Maintenance Plan merely creates Jobs as per your
need. Since there are no Maintenance Jobs (as per your mail)
chances are that the backups might be scheduled through OS bacth files
(scheduled from the OS Scheduler itself or executed from a
remote machine also)
Gopi
"James Green via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ae94125dde5c4afc806c9dbcfd71a49b@.SQLMonster.c om...
>I have an odd problem on a MS SQL2K box, one database on my server is being
> backed up to a file; this appears to be an appended backup and the backup
> file is now getting quite big.
> The thing is, there isn't a maintenance plan to do the backup of this
> database.
> My problem is I want to stop the back up, but if there?s no job how can I
> do that? Theres nothing to stop
> I think I did once have a maintenance plan to back up this database but
> that would have been deleted quit some time ago. I've only found this
> because I'm running out of space on one of my partitions.
> Any help/ideas/suggestions will be very very appreciated.
> --
> Message posted via http://www.sqlmonster.com
|||Adding to my earlier reply, Since you can find out the time at which the
backup happens,
you might want to start the Profiler around that time and find out from
where the Backup job is getting
triggered.
It is not good to have a Production DB being backed up without knowing from
where the
backup is getting triggered.
Gopi
"gopi" <rgopinath@.hotmail.com> wrote in message
news:OBOIBrOFFHA.3736@.TK2MSFTNGP10.phx.gbl...
> Hello James,
> As Tibor mentioned, the Maintenance Plan merely creates Jobs as per your
> need. Since there are no Maintenance Jobs (as per your mail)
> chances are that the backups might be scheduled through OS bacth files
> (scheduled from the OS Scheduler itself or executed from a
> remote machine also)
> Gopi
> "James Green via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:ae94125dde5c4afc806c9dbcfd71a49b@.SQLMonster.c om...
>

Rouge backup

I have an odd problem on a MS SQL2K box, one database on my server is being
backed up to a file; this appears to be an appended backup and the backup
file is now getting quite big.
The thing is, there isn't a maintenance plan to do the backup of this
database.
My problem is I want to stop the back up, but if there?s no job how can I
do that? Theres nothing to stop
I think I did once have a maintenance plan to back up this database but
that would have been deleted quit some time ago. I've only found this
because I'm running out of space on one of my partitions.
Any help/ideas/suggestions will be very very appreciated.
--
Message posted via http://www.sqlmonster.comThe only thing that a maint plan does is to create a number of SQL Server agent jobs. My guess is
that you have Agent jobs that are performing the backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"James Green via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ae94125dde5c4afc806c9dbcfd71a49b@.SQLMonster.com...
>I have an odd problem on a MS SQL2K box, one database on my server is being
> backed up to a file; this appears to be an appended backup and the backup
> file is now getting quite big.
> The thing is, there isn't a maintenance plan to do the backup of this
> database.
> My problem is I want to stop the back up, but if there?s no job how can I
> do that? Theres nothing to stop
> I think I did once have a maintenance plan to back up this database but
> that would have been deleted quit some time ago. I've only found this
> because I'm running out of space on one of my partitions.
> Any help/ideas/suggestions will be very very appreciated.
> --
> Message posted via http://www.sqlmonster.com|||Hello James,
As Tibor mentioned, the Maintenance Plan merely creates Jobs as per your
need. Since there are no Maintenance Jobs (as per your mail)
chances are that the backups might be scheduled through OS bacth files
(scheduled from the OS Scheduler itself or executed from a
remote machine also)
Gopi
"James Green via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ae94125dde5c4afc806c9dbcfd71a49b@.SQLMonster.com...
>I have an odd problem on a MS SQL2K box, one database on my server is being
> backed up to a file; this appears to be an appended backup and the backup
> file is now getting quite big.
> The thing is, there isn't a maintenance plan to do the backup of this
> database.
> My problem is I want to stop the back up, but if there?s no job how can I
> do that? Theres nothing to stop
> I think I did once have a maintenance plan to back up this database but
> that would have been deleted quit some time ago. I've only found this
> because I'm running out of space on one of my partitions.
> Any help/ideas/suggestions will be very very appreciated.
> --
> Message posted via http://www.sqlmonster.com|||Adding to my earlier reply, Since you can find out the time at which the
backup happens,
you might want to start the Profiler around that time and find out from
where the Backup job is getting
triggered.
It is not good to have a Production DB being backed up without knowing from
where the
backup is getting triggered.
Gopi
"gopi" <rgopinath@.hotmail.com> wrote in message
news:OBOIBrOFFHA.3736@.TK2MSFTNGP10.phx.gbl...
> Hello James,
> As Tibor mentioned, the Maintenance Plan merely creates Jobs as per your
> need. Since there are no Maintenance Jobs (as per your mail)
> chances are that the backups might be scheduled through OS bacth files
> (scheduled from the OS Scheduler itself or executed from a
> remote machine also)
> Gopi
> "James Green via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:ae94125dde5c4afc806c9dbcfd71a49b@.SQLMonster.com...
>>I have an odd problem on a MS SQL2K box, one database on my server is
>>being
>> backed up to a file; this appears to be an appended backup and the backup
>> file is now getting quite big.
>> The thing is, there isn't a maintenance plan to do the backup of this
>> database.
>> My problem is I want to stop the back up, but if there?s no job how can I
>> do that? Theres nothing to stop
>> I think I did once have a maintenance plan to back up this database but
>> that would have been deleted quit some time ago. I've only found this
>> because I'm running out of space on one of my partitions.
>> Any help/ideas/suggestions will be very very appreciated.
>> --
>> Message posted via http://www.sqlmonster.com
>

Rouge backup

I have an odd problem on a MS SQL2K box, one database on my server is being
backed up to a file; this appears to be an appended backup and the backup
file is now getting quite big.
The thing is, there isn't a maintenance plan to do the backup of this
database.
My problem is I want to stop the back up, but if there?s no job how can I
do that? Theres nothing to stop
I think I did once have a maintenance plan to back up this database but
that would have been deleted quit some time ago. I've only found this
because I'm running out of space on one of my partitions.
Any help/ideas/suggestions will be very very appreciated.
Message posted via http://www.droptable.comThe only thing that a maint plan does is to create a number of SQL Server ag
ent jobs. My guess is
that you have Agent jobs that are performing the backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"James Green via droptable.com" <forum@.droptable.com> wrote in message
news:ae94125dde5c4afc806c9dbcfd71a49b@.SQ
droptable.com...
>I have an odd problem on a MS SQL2K box, one database on my server is being
> backed up to a file; this appears to be an appended backup and the backup
> file is now getting quite big.
> The thing is, there isn't a maintenance plan to do the backup of this
> database.
> My problem is I want to stop the back up, but if there?s no job how can I
> do that? Theres nothing to stop
> I think I did once have a maintenance plan to back up this database but
> that would have been deleted quit some time ago. I've only found this
> because I'm running out of space on one of my partitions.
> Any help/ideas/suggestions will be very very appreciated.
> --
> Message posted via http://www.droptable.com|||Hello James,
As Tibor mentioned, the Maintenance Plan merely creates Jobs as per your
need. Since there are no Maintenance Jobs (as per your mail)
chances are that the backups might be scheduled through OS bacth files
(scheduled from the OS Scheduler itself or executed from a
remote machine also)
Gopi
"James Green via droptable.com" <forum@.droptable.com> wrote in message
news:ae94125dde5c4afc806c9dbcfd71a49b@.SQ
droptable.com...
>I have an odd problem on a MS SQL2K box, one database on my server is being
> backed up to a file; this appears to be an appended backup and the backup
> file is now getting quite big.
> The thing is, there isn't a maintenance plan to do the backup of this
> database.
> My problem is I want to stop the back up, but if there?s no job how can I
> do that? Theres nothing to stop
> I think I did once have a maintenance plan to back up this database but
> that would have been deleted quit some time ago. I've only found this
> because I'm running out of space on one of my partitions.
> Any help/ideas/suggestions will be very very appreciated.
> --
> Message posted via http://www.droptable.com|||Adding to my earlier reply, Since you can find out the time at which the
backup happens,
you might want to start the Profiler around that time and find out from
where the Backup job is getting
triggered.
It is not good to have a Production DB being backed up without knowing from
where the
backup is getting triggered.
Gopi
"gopi" <rgopinath@.hotmail.com> wrote in message
news:OBOIBrOFFHA.3736@.TK2MSFTNGP10.phx.gbl...
> Hello James,
> As Tibor mentioned, the Maintenance Plan merely creates Jobs as per your
> need. Since there are no Maintenance Jobs (as per your mail)
> chances are that the backups might be scheduled through OS bacth files
> (scheduled from the OS Scheduler itself or executed from a
> remote machine also)
> Gopi
> "James Green via droptable.com" <forum@.droptable.com> wrote in message
> news:ae94125dde5c4afc806c9dbcfd71a49b@.SQ
droptable.com...
>