InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 06:58:10 GMT20http://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxTom many thanks for your comment.Since you have not dealt with Access extensively and at the risk of repeating things that everyone knows please note the following from someone who has been dealing with Access for many years. Access is not a db. It is a software development tool. It so happens that in MS Office, it is bundled with a db called JET/ACE. In place of JET/ACE you can have SQL Server, Oracle or any other db. The Access/Jet/ACE combo has many advantages, like ease of deployment, development, debugging, speed and disadvantages like security and multi-user issues. It depends on the application. In my case (www.VisualDentist.com) Access/Jet is a good choice based on the advantages above. As you can surely appreciate, it depends on the project.Having said the above and based on your comments, I have been looking into using another FE with SQL Server. JohnFri, 08 Feb 2013 00:13:05 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspx[quote][b]jcrawf02 (2/7/2013)[/b][hr]Perhaps Tom is also suggesting you ditch MS Access? :hehe:[/quote]No, through being so fortunate as to have avoided having to learn about it I know far too little about MS Access to have a view on it (and hope I can keep things that way, because I like MS SQLServer far too much to want to switch horses). :w00t:Besides, I didn't actually suggest he switch from VBA, because I was fairly sure that it wouldn't be a realistic option. Switching language is sometimes the right thing to do, but a problem with displaying particular data is not really a good indication that this is one of those times (despite the awfulness of the current language).Thu, 07 Feb 2013 13:04:52 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxThanks for your input. Unfortunately ditching Access is not an option for the specific project.Thu, 07 Feb 2013 08:30:32 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspx[quote][b]GilaMonster (2/6/2013)[/b][hr][quote][b]L' Eomot Inversé (2/6/2013)[/b][hr]I'll refrain from giving in to my hatred of VB and suggesting you ditch VB and switch to either C# or Jscript.[/quote]It's an MS Access form, so VBA's the only choice.[/quote]Perhaps Tom is also suggesting you ditch MS Access? :hehe:Thu, 07 Feb 2013 08:01:45 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxGail thanks again, Tom merci,Switching to c# or something else is easier said than done and as Gail mentioned, not an option in this case.ROWVERSION is the right way to go and I will investigate further.JohnWed, 06 Feb 2013 23:44:57 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspx[quote][b]L' Eomot Inversé (2/6/2013)[/b][hr]I'll refrain from giving in to my hatred of VB and suggesting you ditch VB and switch to either C# or Jscript.[/quote]It's an MS Access form, so VBA's the only choice.Wed, 06 Feb 2013 14:45:14 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspx[quote][b]JohnPapa (2/6/2013)[/b][hr]Gail, there is no need to display it, I just need to know the value. In the recordset the RV value is not Null, nothing prints when you try to print the value and the field is empty on the form. I will look into this a bit more.[/quote]Maybe your FE has difficulty displaying (or printing) whatever array variant varbinary(8) data (or binary(8) if the column was declared not null) turns up in VBS as. I'll refrain from giving in to my hatred of VB and suggesting you ditch VB and switch to either C# or Jscript.Wed, 06 Feb 2013 14:06:47 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspx[quote][b]JohnPapa (2/6/2013)[/b][hr]In the recordset the RV value is not Null, nothing prints when you try to print the value and the field is empty on the form. I will look into this a bit more.[/quote]Then you're going to have to sit and debug and research. Not a SQL Server issue there.[quote]What do you think about using, instead of the RV field, a DATETIME2 field with SYSDATETIME() entered in it?[/quote]Needs a trigger, otherwise won't get updated when the row changes. Rowversion is automatic, datetime is manual.Wed, 06 Feb 2013 09:27:57 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxGail, there is no need to display it, I just need to know the value. In the recordset the RV value is not Null, nothing prints when you try to print the value and the field is empty on the form. I will look into this a bit more.What do you think about using, instead of the RV field, a DATETIME2 field with SYSDATETIME() entered in it?Wed, 06 Feb 2013 08:34:23 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxAgain, don't display it. There's absolutely no value in what it looks like and if all you need to do is compare it, it does not need to be displayed.Have you checked programatically what that column is in the recordset? Have you checked for errors or conversion problems? Have you done some basic debugging as to how that value is fetched?Wed, 06 Feb 2013 08:13:14 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxThe problem is that no value comes across in the FE. The contents of the RV column is empty as shown in attachments Gail1 an Gail2.Can you think of a way to make the RV values available in the FE?JohnWed, 06 Feb 2013 07:11:14 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxOk, so read it from the recordset into a variable (or leave it in the recordset) and compare when you're going to do the update.Wed, 06 Feb 2013 06:59:55 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxMany thanks Gail,I am not planning to display the RV binary number, but I need to know what it was when I read the record or records. How will the SP that does the update compare the original value to the current value. The original RV value has to be stored somewhere. Also, we may have a case that the record is updated several minutes after it was read so I need to know the original value that corresponds to the record read.JohnWed, 06 Feb 2013 06:50:46 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxIn the stored procedure that does your updates. In the VB code. Up to you.There's no point at all in displaying it though, no user's going to understand the meaning of it.Wed, 06 Feb 2013 06:41:39 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxRegarding Username=sa and Password = saPass, I can safely say "i am not doing that":-)Regarding ROWVERSION, I need this value if I want to check whether the specific row was changed or not. How can I perform the comparison?JohnWed, 06 Feb 2013 06:24:21 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxI'm not a VB programmer. Why would you want to display it anyway? It's a binary value, meaningless to any user.btw...[quote] & "Uid=sa;" _ & "Pwd=saPass;" _[/quote]Please, please, please tell me you're not actually doing that.Wed, 06 Feb 2013 02:29:15 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxI have been trying to use ROWVERSION. I created a simple table as can be seen in attachment Gail1.jpg.I use the following ADO code in the open event of the form shown in attachment Gail2.jpg[code="plain"]Private Sub Form_Open(Cancel As Integer)Dim con As New ADODB.ConnectionDim rst As New ADODB.RecordsetDim cmd As New ADODB.Command'OLE DB (ADO) Native CLientcon.ConnectionString = "Provider=SQLNCLI11;" _ & "DataSource=MSSQLSERVER;" _ & "Server=(local);" _ & "Database=JP2;" _ & "Uid=sa;" _ & "Pwd=saPass;" _ & "DataTypeCompatibility=80;" _ & "MARS Connection=True;"con.Opencon.CursorLocation = adUseClientcon.CommandTimeout = 0rst.Open "dbo.tblMyTest", con, adOpenForwardOnly, adLockOptimisticSet Me.Recordset = rstcon.CloseSet con = NothingSet cmd = NothingSet rst = Nothing[/code]As you can see the Rowversion field is empty. Any ideas as to how I can view it in my front end?JohnWed, 06 Feb 2013 01:14:39 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxGail thanks for the succinct analysis.It is also nice to have someone in the same timezone!!It appears that the "rowversion" data type would do the trick. It also appears that the "timestamp" data type has been deprecated and msdn advises the modification of existing timestamp to rowversion.For reference puproses we can use something like[code="sql"]CREATE TABLE tblTest (intID int PRIMARY KEY ,intValue int, RV rowversion)[/code]where rowversion must now be explicitly defined compared to timestamp.The resulting RV value can now be used to chack for any edits.JohnSat, 05 Jan 2013 06:12:00 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxYup, that's an alternative way of doing things. Your original version we would call pessimistic concurrency (I expect someone else to try to change the same data, hence I'll lock it). The version with a rowversion (don't call it a timestamp, that type name is deprecated) is optimistic concurrency (I don't thing anyone will change the data, but I'll check when I save, just in case)You can do it with a row version, you can do it with a datetime column last modification date, you can do it by comparing the columns with what they were when you read the row. All work.Sat, 05 Jan 2013 01:48:58 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxThanks Gail & Craig for your input,Would use of the TIMESTAMP data type be of any help? As I understand it, it is used for version-stamping table rows. I have not tried it yet, but an UPDATE could go ahead WHERE ID = specificID AND LOGTIMESTAMP = SpecificTimeStamp. If the record is changed while another user had it in edit mode the Update would generate an error. Will try it and report.JohnSat, 05 Jan 2013 01:25:10 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspx[quote][b]john 60195 (1/3/2013)[/b][hr]After some more read maybe this would lock a record (and only one record) until all work is done,[/quote]I just want to second what Gail said above. You cannot force the system to only lock a particular row. You can suggest and optimize to that's what it *wants* to do, but you can't force it. Cross platform transactions also come with their own issues, but I just wanted to present an alternative to rebuilding the wheel from scratch.You really want to use a self-releasing indicator, like a 'last updater' timestamp, on the record. This way it's self managing in case of user disconnects, network issues, and anything else of the like.Thu, 03 Jan 2013 13:24:30 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspx[quote][b]john 60195 (1/3/2013)[/b][hr]After some more read maybe this would lock a record (and only one record) until all work is done[/quote]No.Rowlock just says *start* with row locks. Not only ever take row locks. If there's a lot of row locks taken, SQL escalates to table locks. It is a very, very bad idea to start a transaction and then allow the user to input stuff, watch the screen, go for lunch, take the weekend off, etc. You'll end up with major blocking and probably a tonne of support calls for 'my session's hanging/timing out'. Transactions should always start and commit in a single database call.Thu, 03 Jan 2013 09:47:46 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxAfter some more read maybe this would lock a record (and only one record) until all work is done,[code="sql"]BEGIN TRANSELECT * FROM tblName WITH (HOLDLOCK, ROWLOCK)WHERE ID = someID/* While the record is locked do work*/COMMIT TRAN[/code]Thu, 03 Jan 2013 09:41:50 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxMany thanks for your replies. I did some research and found out the following:Scott regarding "sp_getapplock", it appears that from what I read, it should not be the first choice, especially if the record will be checked out for some time.Craig are you referring to something like this, where there should be a check if the record is not committed in a reasonable amount of time?[code="sql"]SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION; select * from ... update ...COMMIT TRANSACTION;[/code]Thu, 03 Jan 2013 06:49:02 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxYou could also add an "in-use" column to each row which is set when someone accesses it, and unset when they close it and/or after a certain period of time has elapsed.Mon, 31 Dec 2012 12:36:58 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxScott, the trouble with sp_applock is that it's designed around single user - single process aspects. My understanding of the OP's question revolves around multiple dentists accessing multiple clients simultaneously, but he wants to make sure that none can step on the other's work.Instead of rebuilding the wheel, there's another method of dealing with this but I personally hate it, because like the other method, so many things can go wrong. Just less moving parts.Look into Transactions and Serializable isolation. What you'll be looking to do is have the application code control your transactions (and do NOT share connections) so that you begin this process by setting the connection to serializable isolation, then opening a transaction, then reading the particular rows you need from all tables. This share-locks them to that account. But, what you do during that read is force the exclusive lock via join hints.From there, you let them do their work and close the transaction when they're done.Be aware, however, that this is NOT as granular as you think it is. This approach can hose you up very quickly depending on how much data you're pulling, as you won't be doing just row locks, but page and table locks because of the way memory and data storage is managed in SQL Server. It requires JUST as much testing and even more optimization than the other method to make sure you're locking as little as possible for each user's access.So, since we've been telling you just how hard this could be, let me make a recommendation for a much easier to approach method that is less likely to shoot you in both feet.Start with adding a last access datetime column to all the tables that you're looking to lock for a particular user's modification. Pull said data for the user. When they choose to edit any data, have the system go through and check to make sure noone's tried to edit in the last five minutes via this column, then update all the records just for the datetime piece. Have your code disallow editing to proceed blindly after five minutes, where it would have to go through and re-check the timestamps (to make sure another user didn't come in while they got their coffee and talked to the assistant) to make sure their allowable edits are still valid. If they're still allowed, let them commit the changes.Does this do exactly what you were looking for? No. But it is MUCH easier to implement and much less likely to end up with your system locked out requiring massive developer involvement to get the production system to re-release. At worst, everyone just has to wait five minutes.Mon, 31 Dec 2012 12:24:32 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxYou could take another approach and move the concept of locking or checking out a record and subsequent notifications of said lock to a middle tier, perhaps a WCF service or something similar. That would be far simpler, than reinventing the locking mechanisms and all that it entails.Mon, 31 Dec 2012 11:17:10 GMTDiverKashttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxMS provides "sp_getapplock", and the corresponding "sp_releaseapplock", to help you do this. With them, you can leverage SQL's already-built-and-tested locking mechanisms for your own [i]logical[/i] locking. You also want to look at APPLOCK_TEST() (and maybe APPLOCK_MODE()).Mon, 31 Dec 2012 09:33:36 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxGail, have taken your advice seriously. I will see whether the functionality can be modified to avoid the headaches.Thanks,JohnMon, 31 Dec 2012 06:41:19 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxEverything I've said stands. You are looking at a huge amount of development and testing and probably a tonne of bugs and irritations. This isn't a 5 minute job.Mon, 31 Dec 2012 05:35:49 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxMany thanks for your input to both of you.I have developed a Dental Practice Management Software package (www.VisualDentist.com) using Access and Jet. While this setup is adequate for a small to medium Dental Practice it is not for a large one, hence the interest to use SQL Server.The problem which I would like to address deals with accessing the dental record of a patient. This includes information relating to the graphical representation, work details, personal details, financial details etc. Assume the Patient records are in tblPatient (let's forget for the time being any one-to-many relationships with other tables). If a user accesses a record (a patient) in tblPatient, the user will have in front of him a graphical picture of teeth. If the user keeps this picture on his screen for some time (say 5 minutes) and subsequently makes a change, I would like any other users accessing this record during those 5 minutes, to be alerted that the record is under modification and maybe prevented from making any changes until the original user finishes with this patient record.Does this help?Regards,JohnMon, 31 Dec 2012 05:06:18 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspx[quote][b]john 60195 (12/31/2012)[/b][hr]One of the problems which I am trying to solve is once a user accesses (checks out) a secific record in a table, I would like all subsequent accesses to this specific record by other users to result in a message saying that the record has ben checked out by UserA and not allow changes if UserA does not release the record.[/quote] Technically this is possible (as gail commented above ) but not recommended. Can you share the actual requirement(businees requirement) so that we might help you.Mon, 31 Dec 2012 03:04:48 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspx[quote][b]john 60195 (12/31/2012)[/b][hr]I appreciate the fact that it would be easy to mess up. If a user needs to work on a specific record for a few minutes and during this time we want to disallow any modifications to the record can we introduce an extra boolean field in the table which would indicate whether the record is checked out or not?[/quote]You can.Every single piece of code that you have must then honour and check that (and there's no way SQL can enforce that, you will have to in your code). You need to take great care that two connections can't both think they locked the record (very easy to do if you haven't got the isolation levels, locks and transactions exactly right in your locking code). You need some process/method to unlock rows that have somehow remained locked even though the user is done (many, many possible causes) while not unlocking rows that the users are just taking their time over.You're looking at massive amounts of development here, huge testing requirements and very likely a lot of related bugs.Is it really, absolutely necessary to reimplement the database locking methodology yourself? Is it worth the time and effort?Grab that book I mentioned and have a read through the chapter on this. It's an entire chapter devoted to the myriad ways of getting this kind of process wrong.Mon, 31 Dec 2012 03:04:10 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxMany thanks for your reply Gail,I appreciate the fact that it would be easy to mess up. If a user needs to work on a specific record for a few minutes and during this time we want to disallow any modifications to the record can we introduce an extra boolean field in the table which would indicate whether the record is checked out or not?JohnMon, 31 Dec 2012 02:57:09 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxFor that you'd need to manually reimplement SQL's locking mechanism, probably with a locking table, manual checks, manual notifications in all code that you have. You then need to consider all the complications of rows getting locked but never unlocked, etc. It's a hell of a lot of work and very easy to get wrong.iirc there's a chapter on implementing something like this in Expert SQL Server 2005 Development (yes, the 2005 version)Mon, 31 Dec 2012 01:54:04 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxThanks for your reply and suggestion article. I am doing some reading in the mean time.One of the problems which I am trying to solve is once a user accesses (checks out) a secific record in a table, I would like all subsequent accesses to this specific record by other users to result in a message saying that the record has ben checked out by UserA and not allow changes if UserA does not release the record.Mon, 31 Dec 2012 01:37:10 GMTJohnPapahttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxYOu are basically asking about LOck mechanism. in your case when DML operation performs then exclusive locks are used by sql engine and it can get eslcalated from row level upto table level (decided by sql server internally) based on the amount of data that query is handling. see [url]http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx[/url]Sun, 30 Dec 2012 22:12:55 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1401247-391-1.aspxQuestion1: How can a user, say User1, be able to lock a row, say Row1, and any dependent rows, say Row11, Row12, until changes are made to the specific row by User1Question2: If another user, say User2, wants to access Row1 or Row11, Row12, when these rows are locked by User1, is there a way to let User1 know about it and in the worst case force the unlock, or have some kind of timeout whne User1 is inactiveThanks,JohnSun, 30 Dec 2012 07:01:25 GMTJohnPapa