Log Files - possible to read them ?

We have a user who we think is connecting to our DB through Access and
accidently changes records. (He needs to be able to make changes elsewhere
so he does need write access).
We have tossed around the idea of giving him a read only account that he
would connect with but that was rejected - he will use his account with
write privileges.
So my question is - can we use the transaction logs to see what the user is
doing? What format is the transaction log in? Anything useful.
TIA - Jeff.There are some 3rd party tools that can allow you to do this. If this is for
2000 then the one from Red-Gate is free.
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
Andrew J. Kelly SQL MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user
> is doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>|||In article <O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl>, jb@.nowhere.com
says...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user is
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
>
LogReader from Lumigent software -- very useful but I'm not sure of the
cost these days.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||I highly recommend ApexSQL Log. It is command-line driveable and has some
neat auditing capabilities. It is sql 2005 compatible and it also has an
API coming out soon.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SzOd%23$vHHA.5008@.TK2MSFTNGP05.phx.gbl...
> There are some 3rd party tools that can allow you to do this. If this is
> for 2000 then the one from Red-Gate is free.
> http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
>
> --
> Andrew J. Kelly SQL MVP
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
>> We have a user who we think is connecting to our DB through Access and
>> accidently changes records. (He needs to be able to make changes
>> elsewhere so he does need write access).
>> We have tossed around the idea of giving him a read only account that he
>> would connect with but that was rejected - he will use his account with
>> write privileges.
>> So my question is - can we use the transaction logs to see what the user
>> is doing? What format is the transaction log in? Anything useful.
>> TIA - Jeff.
>>
>|||Hi,
I think that this is the completely wrong approach. If you need to see
what the client is doing you need to be using SQL Profiler. Best of all
it comes with SQL Server so you do not have to fork out any more money :-)
Jonathan
Mufasa wrote:
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user is
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>

Log Files - possible to read them ?

We have a user who we think is connecting to our DB through Access and
accidently changes records. (He needs to be able to make changes elsewhere
so he does need write access).
We have tossed around the idea of giving him a read only account that he
would connect with but that was rejected - he will use his account with
write privileges.
So my question is - can we use the transaction logs to see what the user is
doing? What format is the transaction log in? Anything useful.
TIA - Jeff.There are some 3rd party tools that can allow you to do this. If this is for
2000 then the one from Red-Gate is free.
http://sqlserver2000.databases.aspf...
log-files.html
Andrew J. Kelly SQL MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user
> is doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>|||In article <O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl>, jb@.nowhere.com
says...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user i
s
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
>
LogReader from Lumigent software -- very useful but I'm not sure of the
cost these days.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||I highly recommend ApexSQL Log. It is command-line driveable and has some
neat auditing capabilities. It is sql 2005 compatible and it also has an
API coming out soon.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SzOd%23$vHHA.5008@.TK2MSFTNGP05.phx.gbl...
> There are some 3rd party tools that can allow you to do this. If this is
> for 2000 then the one from Red-Gate is free.
> http://sqlserver2000.databases.aspf...-log-files.html
>
> --
> Andrew J. Kelly SQL MVP
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
>|||Hi,
I think that this is the completely wrong approach. If you need to see
what the client is doing you need to be using SQL Profiler. Best of all
it comes with SQL Server so you do not have to fork out any more money :-)
Jonathan
Mufasa wrote:
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user i
s
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>

Log Files - possible to read them ?

We have a user who we think is connecting to our DB through Access and
accidently changes records. (He needs to be able to make changes elsewhere
so he does need write access).
We have tossed around the idea of giving him a read only account that he
would connect with but that was rejected - he will use his account with
write privileges.
So my question is - can we use the transaction logs to see what the user is
doing? What format is the transaction log in? Anything useful.
TIA - Jeff.
There are some 3rd party tools that can allow you to do this. If this is for
2000 then the one from Red-Gate is free.
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
Andrew J. Kelly SQL MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user
> is doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
|||In article <O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl>, jb@.nowhere.com
says...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user is
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
>
LogReader from Lumigent software -- very useful but I'm not sure of the
cost these days.
Graham (Pete) Berry
PeteBerry@.Caltech.edu
|||I highly recommend ApexSQL Log. It is command-line driveable and has some
neat auditing capabilities. It is sql 2005 compatible and it also has an
API coming out soon.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SzOd%23$vHHA.5008@.TK2MSFTNGP05.phx.gbl...
> There are some 3rd party tools that can allow you to do this. If this is
> for 2000 then the one from Red-Gate is free.
> http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
>
> --
> Andrew J. Kelly SQL MVP
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
>

Log File Help!

I have a database that has a data file of size 50GB (500 million records). It's a lot of data. For speed, we have set up a 9 disc SCSI RAID 5 (4 drives striped, mirrored, and parity). Total space of 130GB. As I said, the data file consumes 50GB.

I needed to run a pretty vanila SQL INSERT/UPDATE on the data. 7 hours later, the query had failed due to a "log file full" error. The log file was at 80+GB and shortly dropped to 1MB by itself.

I'm running SQL Server 2000 SP3. Recovery model is simple and auto shrink is enabled. What could possibly consume 80GB of disc? That is way larger than the amount of data that I have!

What is the best solution? Get a 200GB IDE disc to house the log file?

I need a solution fast! This project is way over schedule; these 7 hour statement run times are killing me.

Thank you in advance!you will be better off breaking your operation into separate insert and then update. but i'd recommend to replace insert with bulk insert, and update with bcp...out that would result in the contents of the file with desired values, then truncate the table, and then bulk insert clean "updated" data. from what you describe it sounds like you are updating the majority of 500 mil records.|||SCSI RAID 5 (4 drives striped, mirrored, and parity)

I don't know how you set this up, but this "is not" RAID 5. What you should probably have done from a performance perspective was have your logs on RAID 1 or 10 and the data files on RAID 5.

In addition, it does sound like you need to break your operation up into smaller steps. If you want to post the script, we can probably give you some pretty fast pointers on how to make it run faster and not use as much log.|||Thanks for the feedback!

Last night, I ran out and bought a 250GB IDE disc, put the log file on that and reran. It finished in 5.5 hours and consumed 150GB of log (which shortly after shrunk down to 1MB). That's a damn lot of log space! My entire data file is only 50GB!

Here are is the exact SQL I ran:

INSERT INTO Domains (Domain)
SELECT DISTINCT Domain FROM Stages WHERE Domain NOT IN (SELECT Domain FROM Domains)

UPDATE Stages SET DomainID = (SELECT Domains.[ID] FROM Domains WHERE Domains.Domain = Stages.Domain)

Now for the more complex TSQL... Hopefully the 250GB will hold and everything will be complete in 48 hours...|||I don't suppose you've wrapped a BEGIN TRANSACTION around this whole thing have you? Just for fun, put a CHECKPOINT between each one of your statement. This shouldn't help keep the log small, but it will force it to write to disk after each statement. You will want to begin and commit each statement seperately also.|||Now, I didn't use transactions at all. I ran the exact SQL that I posted in a Query Analyzer window.

That piece is done. However, if you can suggest an optimization for the dedup process, I would be really grateful. I'm posting that under a separate thread: "Optimize Dedup TSQL?"

Locks on a table

For some reason, one table in our database (SQL 2000) keeps getting locks on
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
>> For some reason, one table in our database (SQL 2000) keeps getting locks
>> on it so attempts to insert records on it seems to time out frequently.
>> Any ideas on what I might look for to keep this from happening? The
>> table has only a unique int for a PK and no other indexes. Thanks.
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
>> I'm not sure if this helps, but when I go into EM and look at the
>> Locks/process ID and click on a couple of the spid records, I see the
>> following entry in the objects list on a couple of the spid records:
>> Object = tempdb.dbo.##lockinfo67
>> Lock Type = TAB
>> Mode = X
>> Status = GRANT
>> Owner = Xact
>> Index = ##lockinfo67
>> Not sure if this helps.
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Locks on a table

For some reason, one table in our database (SQL 2000) keeps getting locks on
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks
on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the proble
m
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the proble
m
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Locks on a table

For some reason, one table in our database (SQL 2000) keeps getting locks on
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.
David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com

locks in a stored procedure

Are locks necessary in a stored procedure?
I have a short stored procedure where I want to update n records to indicate
their intended use in a non-database procedure. I want to select these
records based on the priority and scheduled process date, and according to a
parameter in the procedure. This is what I see as optimal...
----
CREATE PROCEDURE dbo.GetBatchForProcessing
@.thread_guid uniqueidentifier = Null OUTPUT,
@.batch_count int
AS
SET @.thread_guid = ISNULL(@.thread_guid, NewID())
UPDATE Queue SET lockID = @.thread_guid
WHERE queueID IN (SELECT TOP @.batch_count queueID FROM Queue WHERE
process_date IS NULL AND lockID IS NULL AND scheduled_date <= getdate()
ORDER BY priority, scheduled_date)
SELECT * FROM Queue WHERE lockID = @.thread_guid
----
...but I cannot do this because the @.batch_count parameter cannot be used
dynamically in the TOP statement, and just to say up front I am REALLY
against using dynamic SQL in stored procedures. I think it obviates the
point of using the procedure in the first place.
Since the limit on the rows I want affected in the UPDATE are in a subquery,
using SET ROWCOUNT @.batch_count won't work. Even if it did, SQL Server
won't allow the ORDER BY clause in the subquery without the TOP statement.
Which leaves me with using SET ROWCOUNT to select the set of queueID's into
a temp table, and then perform the update clause to match against what I've
put into the temp table. Now, while doing this shouldn't cause any problems
with the way my application works, design-wise it bothers me having that
step between creating the temp table and doing the update to the Queue
table. How can I temporarily lock the Queue table for these two steps?Random,
TOP @.Variable works fine in SQL Server 2000. What version are you running?
Or, did you upgrade to 2000, but your database remains in 70 mode? In that
case use sp_dbcmptlevel to set it to 80.
RLF
"Random" <cipherlad@.hotmail.com> wrote in message
news:eQHDkjlKFHA.4032@.TK2MSFTNGP14.phx.gbl...
> Are locks necessary in a stored procedure?
> I have a short stored procedure where I want to update n records to
> indicate their intended use in a non-database procedure. I want to select
> these records based on the priority and scheduled process date, and
> according to a parameter in the procedure. This is what I see as
> optimal...
> ----
> CREATE PROCEDURE dbo.GetBatchForProcessing
> @.thread_guid uniqueidentifier = Null OUTPUT,
> @.batch_count int
> AS
> SET @.thread_guid = ISNULL(@.thread_guid, NewID())
> UPDATE Queue SET lockID = @.thread_guid
> WHERE queueID IN (SELECT TOP @.batch_count queueID FROM Queue WHERE
> process_date IS NULL AND lockID IS NULL AND scheduled_date <= getdate()
> ORDER BY priority, scheduled_date)
> SELECT * FROM Queue WHERE lockID = @.thread_guid
> ----
> ...but I cannot do this because the @.batch_count parameter cannot be used
> dynamically in the TOP statement, and just to say up front I am REALLY
> against using dynamic SQL in stored procedures. I think it obviates the
> point of using the procedure in the first place.
> Since the limit on the rows I want affected in the UPDATE are in a
> subquery, using SET ROWCOUNT @.batch_count won't work. Even if it did, SQL
> Server won't allow the ORDER BY clause in the subquery without the TOP
> statement.
> Which leaves me with using SET ROWCOUNT to select the set of queueID's
> into a temp table, and then perform the update clause to match against
> what I've put into the temp table. Now, while doing this shouldn't cause
> any problems with the way my application works, design-wise it bothers me
> having that step between creating the temp table and doing the update to
> the Queue table. How can I temporarily lock the Queue table for these two
> steps?
>|||Random wrote:
> Are locks necessary in a stored procedure?
> I have a short stored procedure where I want to update n records to
> indicate their intended use in a non-database procedure. I want to
> select these records based on the priority and scheduled process
> date, and according to a parameter in the procedure. This is what I
> see as optimal...
> ----
> CREATE PROCEDURE dbo.GetBatchForProcessing
> @.thread_guid uniqueidentifier = Null OUTPUT,
> @.batch_count int
> AS
> SET @.thread_guid = ISNULL(@.thread_guid, NewID())
> UPDATE Queue SET lockID = @.thread_guid
> WHERE queueID IN (SELECT TOP @.batch_count queueID FROM Queue WHERE
> process_date IS NULL AND lockID IS NULL AND scheduled_date <=
> getdate() ORDER BY priority, scheduled_date)
> SELECT * FROM Queue WHERE lockID = @.thread_guid
> ----
> ...but I cannot do this because the @.batch_count parameter cannot be
> used dynamically in the TOP statement, and just to say up front I am
> REALLY against using dynamic SQL in stored procedures. I think it
> obviates the point of using the procedure in the first place.
> Since the limit on the rows I want affected in the UPDATE are in a
> subquery, using SET ROWCOUNT @.batch_count won't work. Even if it
> did, SQL Server won't allow the ORDER BY clause in the subquery
> without the TOP statement.
> Which leaves me with using SET ROWCOUNT to select the set of
> queueID's into a temp table, and then perform the update clause to
> match against what I've put into the temp table. Now, while doing
> this shouldn't cause any problems with the way my application works,
> design-wise it bothers me having that step between creating the temp
> table and doing the update to the Queue table. How can I temporarily
> lock the Queue table for these two steps?
If you want to lock the entire table you can use a TABLOCKX hint on the
query that retrieves the data for the temp table.
David Gugick
Imceda Software
www.imceda.com|||You could try using a table variable with rowcount.. Here's an example I
created using my data...
DECLARE @.varInt INT
SET @.varInt = 3
SET ROWCOUNT @.varInt
DECLARE @.table TABLE(BrochureModelID INT)
INSERT INTO @.table SELECT
BrochureModelID
FROM BrochureModel
UPDATE BrochureModel SET
CreatedBy = 'someone'
WHERE BrochureModelID IN (SELECT BrochureModelID FROM @.table)
SET ROWCOUNT 0
--update here...
This seemed to work..
"Random" wrote:

> Are locks necessary in a stored procedure?
> I have a short stored procedure where I want to update n records to indica
te
> their intended use in a non-database procedure. I want to select these
> records based on the priority and scheduled process date, and according to
a
> parameter in the procedure. This is what I see as optimal...
> ----
> CREATE PROCEDURE dbo.GetBatchForProcessing
> @.thread_guid uniqueidentifier = Null OUTPUT,
> @.batch_count int
> AS
> SET @.thread_guid = ISNULL(@.thread_guid, NewID())
> UPDATE Queue SET lockID = @.thread_guid
> WHERE queueID IN (SELECT TOP @.batch_count queueID FROM Queue WHERE
> process_date IS NULL AND lockID IS NULL AND scheduled_date <= getdate()
> ORDER BY priority, scheduled_date)
> SELECT * FROM Queue WHERE lockID = @.thread_guid
> ----
> ...but I cannot do this because the @.batch_count parameter cannot be used
> dynamically in the TOP statement, and just to say up front I am REALLY
> against using dynamic SQL in stored procedures. I think it obviates the
> point of using the procedure in the first place.
> Since the limit on the rows I want affected in the UPDATE are in a subquer
y,
> using SET ROWCOUNT @.batch_count won't work. Even if it did, SQL Server
> won't allow the ORDER BY clause in the subquery without the TOP statement.
> Which leaves me with using SET ROWCOUNT to select the set of queueID's int
o
> a temp table, and then perform the update clause to match against what I'v
e
> put into the temp table. Now, while doing this shouldn't cause any proble
ms
> with the way my application works, design-wise it bothers me having that
> step between creating the temp table and doing the update to the Queue
> table. How can I temporarily lock the Queue table for these two steps?
>
>|||I'm running SQL Server 2000. I ran the sp_dbcmptlevel procedure to ensure I
was running compatibility level correctly, but that didn't change the TOP
restriction. I'd read in an earlier message in this group that that feature
wasn't going to be available unitl SQL Server 2005.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:eX9huvlKFHA.3076@.tk2msftngp13.phx.gbl...
> Random,
> TOP @.Variable works fine in SQL Server 2000. What version are you
> running? Or, did you upgrade to 2000, but your database remains in 70
> mode? In that case use sp_dbcmptlevel to set it to 80.
> RLF
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:eQHDkjlKFHA.4032@.TK2MSFTNGP14.phx.gbl...
>