I have have two Tables in SQLServer called "Contacts", and "Jobs"
Whenever someone changes a record in either table I update two fields
called "Modby" and "Moddt" with a trigger. The Trigger may also
contain other things as well.
Here's the wierd thing.
If the Modby Moddt statement is the first statement in the trigger I
don't get a "record changed by another user" but if it comes later in
the trigger I do.
I think it has something to do with the LockDelay Key but I'm not sure.
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks, Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
I need help in record locking. As soon as user retrieve records I want to lock those records in database. When other user try to retrieve records from the same table i want to retrieve records those are not locked by other user. How do I do this? Please help...
I have a database programmer who in his latest application has deployed manual record locking into code to release certain records to certain users of the application at specified times.
Does anyone know how manual record locking will affect the SQL 2K system? If at all? Are there some standard practices with manual record locking that should be enforced?
Project spec: If user is making Delivery Order for sales order, the other user cannot make Delivery order for same sales order
How to lock a sales-order record, so other user cant use it, except for reporting (read only). I dont want to use a field since it has a lot of weakness. I am using VB6 and connect with ADO
Currently I am working on asp.net Intranet system.I need some suggestion on records locking for Sqlserver & ASP.net. I want to prevent two user to open the same record for modify.Means if some one has open one record (id=xxxxx) for modify then others should not be able to open same same record (id=xxxxx) for modify .otherwise if both user will open/update same record then information in database will not be updated correctly. Any idea how we can do the record locking so that not two users can open same record(id=xxxxx) for modify.
I am writing a VB program, which needs to update record A in one table using transaction control (BeginTrans & CommitTrans). But due to the page-level locking, before the "CommitTrans", other users couldn't not read other records in the same table.
Is there any way that I can do the record/row level locking? If possible, could you provide me some VB source code? Such as how to use "DBCC ROWLOCK" or how to set for row-level locking.
I'm working on a Planning Databse for a factory that allows jobs to be allocated to machines and to be given a priority. This info. is displayed on an Access 2000 form e.g.
MACHINE ONE MACHINE TWO Priority Job Number Priority Job Number 1 J111 1 J654 2 J562 2 J865 3 j231 3 J821
Both the Production Manager and the Factory Manager have this form open at all times. The problem is this, if for example the Factory Manager changes the priority of a job on his PC, the change is not shown on the Production Managers PC (cos' his screen has not been refreshed with update) The Production Manager therefore has old data on his screen and could mistakenly think that the job priority had not changed at all... I have managed to write some VBA code to get round this but I was wondering if there was any way of 'pushing ' changes to the front-end when they are made or of using record-locking etc. to achieve the same affect...
I am running an Access 2000 front end and MS SQL2000 as a backend and I am having problems with record locking.
I have just found out that If you use ODBC then Access acts as if No Locks are set. In an ideal world I need the record to be locked by a user as soon as they move onto it. If not as soon as they start editing it?
Up until this point I've been quite lazy in trying to understand how record locking really works in SQL server however one of my applications now has a bug so the time has come for me to take the issue seriously.
Basically, a user clicks a button in my app and updates a field in a table in SQL Server (say at 10:34:55). At the almost exactly the same time (lets say 10:34:57) another user tries to update the same record (not necessarily the same field) but the second users update is not connected to the database.
How can I store the second users update and then commit to database once the first user is finished...is it possible or do i need to approach this from a different angle?
Is there a way using ADO.NET to lock a record so that you can performan atomic Test and Set operation on one of its fields?I want to set an application level lock bit on a record's field (set itfrom 0 to 1) and at the same time determine that it was 0 before Ibegan and that I was the one who set it to 1, or that it was 1 before Igot to it (and thus I do not own the lock to the record).I don't see anything that explains if this can be done using ADO.NETusing MSSQL. Any help is greatly appreciated.
I found this interesting paper at http://ftp.sas.com/techsup/download/v8papers/odbcdb.pdf, comparing OLEDB to ODBC.
It says that OLEDB provides record locking where ODBC doesn't. I can't quite reconcile that statement; what does it mean? How is the access layer involved in determining type of locks used by the server in executing a statement?
Can someone please tell me which type of Isolation Levels in the Database Engine to avoid the above side effects respectively.
For Record Locking issue: Example, two editors make an electronic copy of the same document. Each editor changes the copy independently and then saves the changed copy thereby overwriting the original document. The editor who saves the changed copy last overwrites the changes made by the other editor. This problem could be avoided if one editor could not access the file until the other editor had finished and committed the transaction.
For the above example, is it possible to do like this way: Editor 1: SELECT and Lock a record Editor 2: Before SELECT the record, check for the record whether it is locked or not. If it is not lock, then the record can be selected
I have built my own functions in the past that involve a timestamp and record lock column in each table (to support some other DB).
I am currently using SQL 2000 and was wondering if there is a better (OK, easier) way to lock records and even prevent edits from taking place when a record is open.
I've been considering implementing a P2P Transactional Replication concept using three different sites. My qustion is:
Instead of have records only available for editing by specific sql servers (partitioning), can I implement some sort of record locking? For exampel all sql nodes have r/w access to all data unless one sql node has the record locked?
I have a problem with record locking / blocking within an application.The app is quite straight forward. Written in Delphi 5 using BDE toaccess a SQL 7 database (Win2K server).Every so often the users (when they bother to tell me) find that theapplication locks up and they are unable to work. No errors areproduced (error trapping in the app is good). They 'shout round' toeach other and get someone to exit the data entry screen. This seemsto free up the locking/blocking issue.There are about 50,000 records in the table (script below) and it isaccessed through a simple query (script below). All users will accessthis in the same way. I'm assuming that a new record is being editedwhen the problem occurs, but this shouldn't cause locking/blockinguntil it gets committed (right ?).The problem is tracking down the source of this and finding thepattern which I can work back from. I've used Erland's aba_lockinfoscript (a few months back admittedly so will re-visit this), butnothing obvious is jumping out at me.No other tables should be in use at this point.Any suggestions ?ThanksRyan/* Code for query component - users navigate to the record they needto editthere could be 15,000 records showing as outstanding - perhaps thisis the area that I need to re-visit so that less records can be edited? */SELECT *FROMPostReceivedWHEREToDelete = 0 ANDCompleted <> 1ORDER BYPostID/* Table in question */if exists (select * from sysobjects where id =object_id(N'[dbo].[PostReceived]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[PostReceived]GOCREATE TABLE [dbo].[PostReceived] ([PostID] [int] IDENTITY (1, 1) NOT NULL ,[Type] [varchar] (100) NULL ,[ClientsName] [varchar] (100) NULL ,[DateReceived] [datetime] NULL ,[EnteredBy] [varchar] (100) NULL ,[AssignedTo] [varchar] (100) NULL ,[DateAssignedTo] [datetime] NULL ,[Adviser] [varchar] (100) NULL ,[TargetDate] [datetime] NULL ,[CompletionDate] [datetime] NULL ,[Completed] [bit] NULL ,[KeyAccount] [varchar] (100) NULL ,[Notes] [text] NULL ,[Specific1] [varchar] (20) NULL ,[Specific2] [varchar] (20) NULL ,[Specific3] [varchar] (20) NULL ,[Specific4] [varchar] (20) NULL ,[Specific5] [varchar] (20) NULL ,[ToDelete] [bit] NULL ,[EnterUser] [varchar] (20) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
can someone pls show me a way to get an unique sequence at below senario:
PC1 & PC2 using their own local client progam to access to Database Server at SERVER1. In the SERVER1, there is a table SEQUENCE in a database DATABASE1. And the table's structure of SEQUENCE are SeqType & SeqNo. Here is the sample data:
SeqType SeqNo Invoice 100 DeliveryOrder 200
Now, how to prevent PC1 & PC2 to get a same Invoice No. if they request the Invoice No. at the same time? Is it possible to lock the record Invoice when i perform a SELECT statement, then i update the Invoice to 101, lastly release the lock for Invoice?
We have a linked server configured on our SQL Server 2005 server that points to our iSeries. A step in a scheduled job successfully updates data in tables on the iSeries using an update query and the 4-part naming convention (job reports success and updates can be verified), but it appears to leave locked records and open connections on the iSeries. No open sessions showing in SQL Activity Monitor.
The linked server is configured using Microsoft's OLEDB Provider for DB2. Are there additional settings that can be configured on the linked server to prevent this behavior or is this an issue that need needs to be addressed on the iSeries? A timeout setting perhaps (not sure if this fixes the record locks though)?
I've been working on this problem for days and cannot seem to figure it out. I'm using the Microsoft Access Driver in an ASP web page to try to update a database. I'm using adLockPessimistic, but it is acting like I'm using adLockOptimistic.
Before you ask, I'm using MDAC 2.8 SP2 on my Windows 2003 Server.
If I open two web pages at the same time, both trying to increment a counter in a database 100 times, the end result should be a value of 200. But in most trials, I get a number less than 200, like 187. It almost seems like it is not locked at all, ecept for the fact that I sometimes get an error telling me I can't update the data because it is locked by another user.
So... I can read OK, but not save because sometimes its locked by another user and sometimes the increment doesn't really increment, therefore it seems like I have adLockOptimistic instead of adLockPessimistic. I do get adLockPessimistic when I read back the lock type.
Any help would be great!
Code Snippet
for i = 1 to 100 if ( len(Request.Form("zipcode")) > 0 ) then call LogImpression (38) end if next
Function LogImpression (CampID) 'This increments the impression counter for a campaign in the vendors database On Error Resume Next
Set adoCon = Server.CreateObject("ADODB.Connection") 'adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("accessdb/vendors.mdb") adoCon.Open "DSN=vendorDB" Set rsAddComments = Server.CreateObject("ADODB.Recordset") strSQL = "SELECT vendors.impressions,vendors.ID FROM vendors WHERE ID=" & CampID & ";" rsAddComments.CursorType = 2 rsAddComments.LockType = 2 rsAddComments.CursorLocation = 2
rsAddComments.Open strSQL, adoCon, 2, 2, adCmdTableDirect if (NOT rsAddComments.EOF) then Err.Clear
Multi user ASP.Net website, SQL Server backend.When using transactions to insert multiple rows into a table and then commiting them once you're happy that everything looks good, the table(s) gets locked before commiting.Basically, I want to perform updates and inserts under a transaction but still allow other users to carry on using the site and potentially the tables that are being used with the transaction.
Question:Â Is there a way to stop the table lock occurring and for SQL to just do a record lock?, thus avoiding blocking other users?I know that a possibility is to use Snapshot replication, but am a little worried about turning it on due to the small team we have for testing.
SQL2008R2...Have a SPID  that started yesterday and contained a command to drop a server audit.  This did not finish (after 3hrs) so I killed the SPID. Problem is it's still there (sp_who2) and I think it's now blocking access to viewing editing logins within SSMS as I'm getting "Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)" when double clicking a login.Do I need to restart the instance as killing the "alter server" transaction (SPID=62) is not working? Code to show blocking transactions returns this.
We are using an Access 2000 project to view our SQL Tables and using Access 2000 Runtime to Access the forms in the project. We have written in a locking system in VB and removed the video controls to prevent users from accessing the same records. But of course now we need to make the video controls available. This has now thrown up the problem of multiple users accessing the same records. We have tried to write code to lock records when then video controls are used but this is not working as well as we hoped. Can anyone please suggest any way of setting up locking on SQL using triggers from the Access frontend? or any other types of locking systems that could be written in the Access front end.
I have an Access db that has a linked table to a sql table. The sql table has a UniqueIdentifier field in it. How do I create a new record through access in that table and have the UNIQUEIDENTIFIER autogenerated?
I am using the following INSERT statement to add a new record in my table. I want to be able to get the Autonumber UserID field of the newly created record but I am getting the following error: "Characters found after end of SQL statement" Code:
INSERT INTO tblUser (LoginName, UserPassword, EmailAddress, City) Values (@User, @UsrPassword, @EmailAddress, @City); SELECT @@IDENTITY As 'Identity'
What is wrong in my statement? How do I retrieve the Autonumber field of the newly created record? How do I handle if the LoginName and/or EmailAddress already exists?
I want to be able save this INSERT statement as an Access Query and call it from my C# code.
Purpose I would like to know if it is possible and if so how to implement exclusive access to a given record within in a database table.
Example For example, you can open a file system file with exclusive access so other processes cannot access the file until it is released. I would like to implement a solution that provides this same exclusive access at the database record level. Preferably a solution that does not require creating and managing table access state flags. (i.e. €“ FieldName.State = (Closed, Opened, Exclusive)) I would like for the database server to mange this solution and return an error status when trying to access a record that has been opened exclusively. Furthermore, I would like to avoid using triggers.
Application Utilization I€™m working on a system that has multiple threads that are each responsible managing data from the same normalized table. Each of these system threads work on the same unique record in parallel. Therefore it is possible that thread (0)€™s changes could be overwritten by thread (n)€™s. I would like to have each thread open the unique record exclusively and release it when finished. All of the other threads would loop until the record is released.
Questions
1. Is it possible to implement this at the server level?
2. What would be the easiest best practice for implementing this functional requirement?
Ok, to start I will say I am a novice so detailed solutions are much appreciated. I believe I am on the right track. Here is what I am trying to accomplish. I have a page that uses an xml driven google map. When you click on the map marker, the info bubble displays brief information about a community and a link specified from the xml file. I have specified the links in the xml file to pass a query string variable ex: /community.aspx?name=uniqueCommunityNameHere. I tested this by simply placing a label on the community.aspx page that restated the "name" value - works fine (first commented out line of Page_Load). Now I need to complete my VB function that pulls the data record from the database - based on the parameter input from the query string - when the page loads. So if the user clicks the link on the marker for community1, it will populate community.aspx with info about community1 from the database... if they click community7 it will display that community info etc. Data Table: "Community" Data Fields: ID, Name, Description, Address, City, State, Phone, SalesRep. ASPX Page Design Elements: lblID, lblName, lblDesc, lblAddress.... etc. Here is my start... I will need help with the sql statement for sure - how do I create a where clause from the query string parameter passed in? Thanks so much for your help!
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load'My initial test to see that the query string is working lblName.Text = Request("name").ToString() ' Get the community info when loading community.aspx for the user selected communityIf Not IsPostBack ThenLoadCommunityInfo(Request("name").ToString())End IfEnd SubPrivate Sub LoadCommunityInfo(ByVal community)' Define data objectsDim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReader ' Read the connection string from Web.configDim connectionString As String = ConfigurationManager.ConnectionStrings("CONHTConnectionString").ConnectionString' Initialize connectionconn = New SqlConnection(connectionString)' Create commandcomm = New SqlCommand("SELECT * FROM Community WHERE Name = " & community, conn)' Enclose database code in Try-Catch-FinallyTry' Open the connectionconn.Open()' Execute the commandreader = comm.ExecuteReader()' Populate the list of community information -> Make the labels.text = the database fields --> lblName.text = table-Community field-"Name"lblName.Text = "Name"lblDesc.Text = "Description"....' Close the readerreader.Close()Catch' Display error messagedbErrorLabel.Text = "Error loading the community information!<br />"Finally' Close the connectionconn.Close()End TryEnd Sub
Can someone explain what happens when two users concurrently attempt tocreate a new record in a table with an autonumber primary key? For example,user 1 creates a new record and manipulates it within a transaction makinguse (perhaps) of the @@IDENTITY value when creating other, related records.Before this transaction is complete, user 2 creates a new record and doesthe same thing. Presumably they will both have the same @@IDENTITY? Ifthis is the case, how is it possible to manage such a situation?Thanks.
I can't delete records for a SQL Server table when I attached the table to Microsoft Access 97. I time out when I try to delete the record. However I can query the table.
I use the standard ODBC setup, do I need to do anything else beside using the standard Access link.
Anyone know if there is method that can insert all record from a tablein an MS Access 2000 database to a table in MS SQL Server 2000database by a SQL statement? (Therefore, I can execute the statementin my program)--Posted via http://dbforums.com
I have table contains more columns  and first column have ID  int not null primary key  and auto increment by 1 seed by 1 the ID 165000 record  and instant Jump to 166000 and increment by 1 ...
I like to give First Name and Last Name in two different text box and then hit the delete button (command button). Then it will do a query to find the person and delete the corresponding record from the table. Any kind of help will be appreciated. Thank you.
