Read, Modify Table (locking) Question
Feb 3, 2006
I am quite new to MS SQL, and I want to read rows from a todo table, and
when a row has been processed, I want to delete that row.
Ages ago in MySQL I would probably have locked the table, select, process
a row, delete a row, unlock the table.
I have been reading through the documentation from MS SQL, but it's not
clear what exactly I should do.
Since I want to lock only one table in the select (the others just provide
data, and are not modified), what's a good solution?
--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html
View 2 Replies
ADVERTISEMENT
Jul 20, 2005
Need some information regarding SQL Server locking.Requirement is to run SQL Statements for reports (read-only) that willnot lock-out other people trying to update (write lock). For this, wetried the following to make the db connection not deny write locks(our report code will not have a read-only lock).ADODB.ConnectionObj.Mode =adModeShareDenyNoneBut this made our connection bumped whenever someone tried to update.Tried enabling dirty reads by doing:ADODB.ConnectionObj.IsolationLevel =adXactBrowseEven this made our connection bumped whenever someone tried to update.Is there a way to achieve what is needed without having a seperatedatabase for reports?
View 1 Replies
View Related
Dec 27, 2007
I have a question on locking pattern of read committed with snapshot isolation level that when two transaction update two different records then why do they block to each other even if they have previous committed value (old version of record).
I executed the below batch from a query window in SSMS
--Session 1:
use adventureworks
create table marbles (id int primary key, color char(5))
insert marbles values(1, 'Black')
insert marbles values(2, 'White')
alter database adventureworks set read_committed_snapshot on
set transaction isolation level read committed
begin tran
update marbles set color = 'Black' where color = 'White'
--commit tran
Before committing the first transaction I executed below query from second query window in SSMS
--Session 2:
use adventureworks
set transaction isolation level read committed
begin tran
update marbles set color = 'White' where color = 'Black'
commit tran
Here the first session blocks to second session. These same transactions execute simultaneuosly in snapshot isolation level. So my question is why this blocking is required in read committed with snapshot isolation level?
Thanks & Regards,
Subhash Chandra
View 1 Replies
View Related
Apr 10, 2015
I created am inventory table with few columns say, Servername, version, patching details, etc
I want a tracking of the table.
Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.
View 1 Replies
View Related
Jul 20, 2005
Hello!I have an MS SQL-server with an database, that runs replication. In thisdatabase there is an table with an columni want to extend; varchar(50)->varchar(60).But I get this error (using design window of Enterprise Manager): Cannotdrop the table 'MytableName' because it is being used for replication.Thanks for helpBjoern
View 1 Replies
View Related
May 29, 2008
I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it
I need that query for alter table
in it
reply me soon
View 14 Replies
View Related
May 31, 2002
Hello
I have replicated 1 database.Now i need to change the datatype length of one feild from varchar 12 to varchar 20.But when i try to do that it shows error ODBC error !Cannot modify database because it is in replication mode.
Can any body help me to sort out this problem.
Thx
Bilal
View 1 Replies
View Related
Nov 22, 2007
Hi AllAm new to sql server to sobear with me, have checked around but cantfind an answer to this.I want to change fieldname from nvarchar(50) to nvarchar(255) as partof a scriptAm running this command :ALTER TABLE MYTABLE MODIFY fieldname nvarchar(255) nulli test this out in the sql window but cant get it to run error is:Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'MODIFY'.Can anyone help ?
View 1 Replies
View Related
Mar 19, 2008
I have a 3rd party program that creates some tables in my sql server2005 database, then it does inserts on the tables. Is there a way Icould create a system level trigger that would immediately andautomatically modfiy the table structure after the table is created(hopefully before the inserts occur)? I need to change a field fromnumeric(5, 0) to numeric(15, 0).Thanks,Roger
View 3 Replies
View Related
Jan 19, 2006
hi!
i encountered problems when running this code in SQL Query
ALTER TABLE [dbo].[amsSchedule]
MODIFY(CutOff1 datetime NULL,
[FileName] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
my aim is to modify the two fields to change its data type. BUt when im trying to run this command in the query analyzer, itsays "incorrect syntax error '(' "
What do i have to do? please help me...thanks
View 3 Replies
View Related
Oct 4, 2013
I've a table with an ID column, it is an auto increment id column that increases by 1.
ID
--
9724
9725
When my webhost restarts SQL, or some other random act of God, this ID jumps by 1000. Today it leapt from 9725 to 10725, which just doesn't work for me. It results in my having to upload the last known good copy of the database.
My host says they can't change the 1000 row buffer, MS apparently doesn't deem this an issue, and I'm quite frustrated. I used to be able to modify certain settings in SQL since I was using an SQL 2008 database under SQL Management Console but since my host upgraded to 2012, and I'm running XP, there is no forward compatibility and many options are no longer possible (eg. modifying a column, now it has to be done via clicking query and entering a SQL command rather than just right clicking on the column)
So long story made short... is there a way to modify my table (named "places") to turn OFF the autoincrement Id and perform a simple means to figure out the next possible value?
example:
id, place, locstion, gps, v2, v3, v4, comment
================================================
9720, Bill's House, Toronto, 43.4453, y, n, c, This was a good place to eat
9721, Dime a Dozen, Cambridge, 42.4453, n, e, d, A good place to take photos
I'd have to pull the highest value of ID (+1 to it) and then do a standard INSERT into places (id, place, location, gps, v2, v3, v4, comment) values (9722, etc...)I guess my concern is if the autoidentity is turned off, will SQL allow a new value to be entered via INSERT? I'm also using the @@identity to find the last known ID when I want to direct the user to the entry that they've just created and I don't know what that # is just yet. Would the @@identity still work?
View 5 Replies
View Related
Apr 10, 2008
Hi all I was wondering how to do an ALTER command on a Table but without specifying Column Names but rather attempting to overwrite the Table itself with the new fields specified? For instance if I have Table_1 consisting of the following fields:
IDFirstNameSurname
Then use the following ALTER command:
Code Snippet
ALTER Table Table_1
(
ID Int,
FirstName VarChar(50)
)
This would then drop Surname from the Table and leave only ID and FirstName inside it. Is this possible? I have been searching google but can't seem to find what I am looking for.
Thank for comments + suggestions. Regards, Onam.
View 2 Replies
View Related
Mar 2, 2007
Dear all
I have a problem about replication, so I want to modify some columns in a table, but my database using replicate. How to do it?
Please solution.
Thank.
PC Kaveesin.
View 4 Replies
View Related
Jun 9, 2008
Our ERP vendor is helping us migrate from one version of their product to another. In the new version, they have added some columns and changed a few data types in several existing tables.
They sent us a list of the modifications that were necessary, which we did (painstakingly) by hand. But, for other reasons the update failed and we have to start anew.
This got me thinking: is there any way to do this kind of update via a script/stored procedure? I don't have their exact changes handy, but it was stuff like the table "inmast" needs a column of varchar(50) called "lotc" added to it, and the "intime" table needs the datatype of columns "Mon" "Tue" "Wed" changed from text to varchar(50), stuff like that.
Is it possible to do stuff like that in a SP?
View 1 Replies
View Related
Jul 23, 2007
In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.
In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?
I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.
Please help, it seemed so much easier in 2000!
View 7 Replies
View Related
Feb 7, 2002
Gurus,
I am trying to execute this stored procedure when I try to change all occurences of a field in a table.
(
@Dept char(8),
@DDept char(8)
)
As
Set NoCOUNt On
Begin
Select '@DDept'
update phodept set fo_dept = @Ddept
where fo_dept = @Dept
update phone set fo_dept = @Ddept where fo_dept = @Dept
End
GO
The table/database is being used by others, generally in a read only mode.
via a VB 5.0 FE program.
The Stored procedure, when it is invoked, just hangs like it is waiting for exclusive use of the table.
Is there a way around it, without doing major surgery on the VB code?
Thanks.
Sam
View 3 Replies
View Related
May 24, 2002
Hello,
I am working on the implementation of a database for my company and I have
a simple (I hope) question to ask.
I have the following stored procedure that will be running when I want
(actually when I want to delete a customer).
Do not mind if you do not understand what this procedure does... ;-)
Actually the Level8View is a VIEW of a nested table
(CustomerData->CustomerData).
CREATE Procedure DeleteCustomer
@ClientID INT
AS
UPDATE Level8View
SET UpCode = Level2, Level1=Level2, Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level1=@ClientID
UPDATE Level8View
SET Level2=Level3, Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level2=@ClientID
UPDATE Level8View
SET Level3=Level4, Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level3=@ClientID
UPDATE Level8View
SET Level4=Level5, Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level4=@ClientID
UPDATE Level8View
SET Level5=Level6, Level6=Level7, Level7=Level8
WHERE Level5=@ClientID
UPDATE Level8View
SET Level6=Level7, Level7=Level8
WHERE Level6=@ClientID
UPDATE Level8View
SET Level7=Level8
WHERE Level7=@ClientID
DELETE FROM Customers
WHERE ClientID=@ClientID
GO
I checked this procedure and works perfectly.
What I want to do is to somehow lock the WHOLE TABLE (CustomerData) or the
view (Level8View) before the Update statements and unlock it after the
delete statements.
I do not want to do a Row by Row lock, or Page by Page lock since the updates
in this table occur in the whole table and during that operation I do not
want other threads to issue SELECT, INSERT or UPDATE statements.
Can someone answer me how I can lock and unlock the whole view or table
please?
I will appreciate it for your answer. Thanks.
Yours, sincerely
Efthymios Kalyviotis
ekalyviotis@comerclub.gr
View 4 Replies
View Related
Nov 11, 2005
I have a SP for an e-commerce site that creates an order doing the following fashion:
begin
-Fill out some variables
*Critical Section
-Create Order number
-While loop select on order table and recreate if ordernumber already exists
-Insert into order table
*End Critical Section
-Insert into order lines table, address table, etc.
commit
The problem is the Create Order number and While loop used to be at the top and occasionally I would get duplicate order numbers if two submitted at the same time. I rearranged it to the above and it happens less now.
My question is if there is a way to put an XLOCK on the orders table only during the critical section lines. Also my understanding is that once the insert into orders is done that the server will hold a lock until the commit in case of rollback.? I don't want to XLOCK the whole SP if I don't have to. It is quick though.
Thanks
View 3 Replies
View Related
Apr 25, 2008
I need 2 unique sequential numbers to be stored in the same table. Obviously, for the first number I can use an auto-number field. For the second number, I would like to use a 2nd table with just 1 field that would only ever store 1 record. The idea being that if a user needs to generate a new unique number, they lock the 2nd table, lookup the current value, store the value in a variable for use, add 1 to the value of the number in the table, then unlock the table.
Presumably, I'd use something like this:
SELECT OrderNo
FROM tblUniqueOrderNumber (WITH TABLOCK)
Does this lock the table so no other users can read/write the table? If so, how do I unlock the table once I've updated OrderNo? What happens if another user then reads the table while it has a TABLOCK applied? I'm using SQL Server 2005 and the database is a multi-user database. I have a feeling I'm not going about this the best way possible, but I'd like to know how the table lock works eitherway.
View 12 Replies
View Related
Apr 30, 2004
hi
can i lock my table while doing an updation so that all others in n/w can insert into the table only after my operation is completed?
if a user attempts to save/update the 'locked'table, will he get an error message or will his application (using ADO) will wait till the table gets unlocked?
View 2 Replies
View Related
May 11, 2004
Can any one please tell me how to lock & unlock a table a from inside a store procedure using T-SQL
thanks
View 6 Replies
View Related
May 6, 2008
hi,
I am running a sql job that select data from one database (multiple table) and inserts into another database (1 table)
I have a problem with the way it is locking the tables I am selecting from !
my query is like :
insert into testdb.dbo.tabletest
select * from
livedb.dbo.tablex
inner join liveb.dbo.table1 on tablex.id=table1.xid
It seems to be locking the table and blocking other users from accessing the table.
suppose my question is why is the select locking the table ?
View 1 Replies
View Related
Jul 20, 2005
I want to insert values into a table and the same time select a valuefrom the same table.I insert a record of a parent type and use a function to create asubscriberType for the record.Next i insert a record of child type and want to select thesubscriberType from the parent record.This do not work when i useINSERT TABLESELECT ,Name,"function to generate subscribertype",.............I DO NOT WANT TO USE CURSOR!!
View 1 Replies
View Related
Mar 20, 2008
What's the answer for this and how ? If it is yes then How ?
View 4 Replies
View Related
Aug 21, 2007
We have a MS SQL database with an Oracle linked server 'ALTTEST'
We can Select, Insert, Delete and Update tables on the Oracle Db using OpenQuery, but how do I apply a table lock with a transaction?
I've tried applying the code below, but it doesn't work.
Any help appreciated.
BEGIN TRAN
SELECT * from openquery(ALTTEST,'select LAST_PIN_NUMBER from sys_params') WITH (TABLOCKX)
COMMIT
View 5 Replies
View Related
Oct 3, 2007
So randomly every 1 to 6 days queries start timing out and I'm almost positive it's from an improperly terminated transaction
Is there a way to snoop this out the next time it happens? Like when a table's locked I can look and see yea this is the transaction it's in the middle of?
View 6 Replies
View Related
Mar 25, 2006
Hi,
im developing an application for my university final year project and i want to lock the table before the query executes to ensure that no other changes are allowed (sorry if im confusing)
So my question , really does anyone know how to do this and is it possible ?, for example in coldfusion a query is surrounded with cflock or something similiar.
Appreciate any and all help guys
Many thanks in advance
andy
View 1 Replies
View Related
Aug 5, 1999
Hi
Is a TABLOCK HOLDLOCK in a transaction the best way for locking a table for a brief period of time?
We need to get generate a unique serial number each time a certificate record is printed. We have in place a field called Field_SERIAL_NUMBER for each certificate record, which may be printed 1 time or 100 times. Each time it prints, we will insert a unique number into the Field_SERIAL_NUMBER field.
This is what I'm thinking to approach this.
- Create a new table called Table_SERIAL with one field called Field_NUMBER which has only 1 record set to 1.
- Every time a certificate is printed, we
1. Begin transaction
2. Select @number = Field_NUMBER from Table_SERIAL with a TABLOCK HOLDLOCK
3. Insert that number into the Field_SERIAL_NUMBER field in the certificate table
4. Increment @number by 1
5. Update Field_NUMBER with @number in Table_SERIAL
6. Unlock table
So, everytime the Field_NUMBER is pulled, it should be unique and incremented by 1.
Does this approach sound good?
Joyce
4. Increment
View 2 Replies
View Related
Oct 20, 2005
Hi,
is there anyway i can run delete statements on an audit table that will not lock the whole table?
As it is an audit table, when i run the delete query, the whole application freezes until the delete query is complete.
the process is taking several minutes as the table is approx 40M rows, the where clause in the delete is indexed but thats it really.
any thoughts greatly appreciated by my system users!
View 5 Replies
View Related
Apr 4, 2007
Hi,
I want to insert a record in a table having an identity column as primary key. I want to lock the table while inserting. so that no one should be able to insert, select, update, delete from the table. and once my insert is over, then will release the lock.
Can I have the code for the same. M using SQL SERVER 2005.
Thanks,
Rahul Jha
View 8 Replies
View Related
Jul 23, 2005
Hi All,I am using a SQLTransaction to insert records into a table. At onetime, there are 5000 or more records to be inserted one by one. Ittakes some 20-25 mins for the entire process to run.Another application accesses the same table.As long as the insert process within the transaction isn't completed,the second application is not getting any response from the server. Ieven tried to run a SELECT on the table in SQL Query Analyzer while theinsert process was running and it also did not respond till the timethe insert process finished!Is this normal that a transaction is locking up a table? How do Iovercome it? I am using IsolationLevel.ReadUncommitted for thetransaction.If I do not run the process within a SQLTransaction, the second processor running the SELECT in Query Analyzer does not hang.Thanks,Sanjeev Mahajan
View 3 Replies
View Related
Jul 20, 2005
SQL Server 2000, MSDE 2000I have a procedure in my application that I would like only one user at atime to be able to run. Is there a TSQL command I can run that willesentially lock a set of tables so others cannot access the table until theuser is done with the procedure or until the user disconnects from thatsession (in case of a hung app, I would like to lock released)TIA--Tim Morrison--------------------------------------------------------------------------------Vehicle Web Studio - The easiest way to create and maintain your vehiclerelated website.http://www.vehiclewebstudio.com
View 3 Replies
View Related
Oct 12, 2007
I feel like I read about a new ability in SSRS 2005 to be able to lock the position of a table's header row (when viewing through the report viewer) so that it does not scroll out of view when scrolling down long report. Now that I need it, I have not been able to find any information about it. Does anyone have any information about this feature or know if this feature exists?
View 1 Replies
View Related
