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?
Cheers.
Hi,
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.
Code snippet - getting the record thru JDBC:
DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();
// wsCon.setAutoCommit(false); //have tried with and without this flag - same results
Statements stmt = wsCon.createStatement();
String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println(rs.getString("lastName"));
}
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (wsCon != null) {
wsCon.close();
}
Hello,
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...
Thanks in a millian.
Sarika
Hello,
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?
Thanks,
Brent.
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
Thanks
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.
Thanks in Advance
Arvind
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.
Thanks for your kind help.
Hi all,
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...
Thanks
M
Hi there,
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?
Thanks
I'm trying to figure out what happened.I have have two Tables in SQLServer called "Contacts", and "Jobs"Whenever someone changes a record in either table I update two fieldscalled "Modby" and "Moddt" with a trigger. The Trigger may alsocontain other things as well.Here's the wierd thing.If the Modby Moddt statement is the first statement in the trigger Idon't get a "record changed by another user" but if it comes later inthe trigger I do.I think it has something to do with the LockDelay Key but I'm not sure.Any Ideas?
View 3 Replies View RelatedIs 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.
View 3 Replies View RelatedI 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?
.B ekiM
From SQL Server Books Online, there is a topic: Concurrency Effects, and did mention the following side effects:
- Lost updates.
- Uncommitted dependency (dirty read).
- Inconsistent analysis (nonrepeatable read).
- Phantom reads.
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
Please advise.
Thanks.
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.
Any input appreciated.
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
View 3 Replies View Relatedcan 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?
pls advise. thanks.
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)?
Any help is greatly appreciated.
KS
my colleague is using visual studio (VB) and making windows applications. we used same connection string but i can't connect to the sql server.
i am using visual studio(2003) .net(C#) and making web applications. this is the code.
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection ();
// TODO: Modify the connection string and include any
// additional required properties for your database.
//conn.ConnectionString = "integrated security=SSPI;data source=cerebrum;persist security info=False;initial catalog=dals";
conn.ConnectionString="Server=cerebrum;Initial Catalog=dals;User Id=sa;";
try
{
conn.Open();
// Insert code to process data.
Label1.Text="Connection successful";
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
and i'm getting this error
SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
Source Error:
Line 91: throw ex;
Line 92: }
Line 93: finally
OK I have been trying on my own to move from Access to SQL express/Developer. I have not found much in the way of guidence. Any suggestions? I would rank myself as fairly advanced with Access but just a newby to the SQL products... I keep blasting into walls and issues in the SQL world and would rather learn from someone elses' hardship rather than re-invent what has been undoubtably been already discovered.
I do fairly advanced reports and Large imports, Hence the need for developer rather than express, since the express import facility is hopelessly crippled. Even the developer SSIS is not well doccumented and seems pretty buggy and hard to use, even with the wizzards, As for reports.... well I'm expecting that to be a fairly had road to climb also...
I've got a website with authentication mode "forms". Now I need access to the database to authenticate the user. The SQL Server is located in another domain than IIS.I don't know how to establish a trusted connection with the SQL Server. How can I establish this?
View 2 Replies View Relatedhi
i have a stored procedure witch Returns 0 or 1 dependig if exists some rows
how can i acces that value in code behind? i tryied all, command.ExecuteScalar(), command.ExecuteNonQuery() but none works
i need something like if(command.GetReurnValue) .... else ....
thanks in advance
Hi
I would like to acces a SQL Server that is on a web server.
I want to to this to be able to control the sql server and database to create table, trigger...
For "MySql" i use DBTools to acces my database via the web, is there something i can use like BdTools to acces the Sql Server 2000 database
I'm new to SQL so can somebody can tell me what to do!
Thanks
use this-is-a-stupid-namereturns an errorFor that matterselect *from this-is-a-stupid-name.dbo.anytablereturns an errorI've tried single quoted, double quotes, escaping. In the SQL editor I canselect the name from the dropdown and I'm OK - but I'm writing a perlscript to connect to the database.I didn't create the database and don't know how it was created.--Message posted via http://www.sqlmonster.com
View 2 Replies View RelatedHi,
I need to access a table from another server in my procedure...I am now connected to say SERVERA...I need to access another table SERVERB.TABLE2 in the same procedure...
Is there a way to do that...
I need to connect to two servers from the same procedure to get data..
Please get back
Thanks,
I try to access an OLAP Server from a remote compute, using ADO MD Library with following string conection :
"Datasource=SERVNAME; Provider=msolap; Initial Catalog=FoodMart 2000;"
I recive the following error code: -2147467259 (0x80004005), "Unspecified error"
Can sombody help me
I have migrated an Access database to SQLserver,
all seems ok,
but I found that an "order by" on a boolean Access field, is reverse under
SQLserver ?
I had to add "desc" to my code "order by field desc" to retrieve the correct display.
Could someone confirm this ? I am very surprised.
Thanks, Pierre.
Hi, I'm using Sql 2000 and I have a linked server called SRVSRV.
The link works because I can list all tables.
How can I access the data i'm trying:
SELECT * FROM [SRVSRV].Database.Table
And it don't works.
What am i doing bad?
Thanks.
Hi there.
How can i convert a Ms Access Database file (.mdb) to an SQL Server 2005 Database ????
is there any tools ?? or what ever can help me .
Thank you.
I am writing a package to process perfmon logs. The issue I have come across is that the perfmon process holds onto the log file and SSIS fails because it wants to exclusive read access. Is there any way of getting SSIS to not take an exclusive read on the file.
I can read the file in notepad fine but not SSIS.
After an internet search I have some questions regarding SQL Server access from a mobile device via LAN / WLAN.
I will write a application in C that write data to an SQL server. First if I correct that's not possible to acces an SQL server via ODBC on Windows CE? What alternatives are there?
Stef
Hi All,
I have MS Access 97 database and SQL Server 2005 Express Edition (or SQL Server 2005 Developer Edition).
I want online get data from MS Access and upload to SQL Server.
Help me, which way I select ?
Regards
Hi friends,
I have developed a website and uploaded the database to Remote server through Export wizard in Sqlserver 2005.everything is working fine in Sqlserver enterprise manager, but when i tried to connect this database via web.config file to my website it is giving me error...
Server Error in '/Sales_offer' Application.
Invalid connection. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Invalid connection.Source Error:
Line 29: {
Line 30: SqlCommand mycmd=new SqlCommand("select distinct(city) from master order by city asc",myconn);
Line 31: myconn.Open();
Line 32: SqlDataReader sdr=mycmd.ExecuteReader();
Line 33: ArrayList arr=new ArrayList();Source File: d:inetpubwwwrootsales_offerindex.aspx.cs Line: 31 Stack Trace:
[SqlException: Invalid connection.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
System.Data.SqlClient.SqlConnection.Open()
Sales_offer.WebForm1.bind() in d:inetpubwwwrootsales_offerindex.aspx.cs:31
Sales_offer.WebForm1.Page_Load(Object sender, EventArgs e) in d:inetpubwwwrootsales_offerindex.aspx.cs:67
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032
After that when i tried to access the database through Qury analyzer it is giving me error
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'salecumoffers'. No entry found with that name. Make sure that the name is entered correctly.
But it is showing me the tables and their Contents from Remote server in enterprise manager...
waiting for ur quick response
Neeraj