Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver,microsoft.public.sqlserver.programming Subject: Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Followup-To: comp.databases.ms-sqlserver Date: Tue, 31 Jul 2012 09:08:01 +0000 (UTC) Organization: Erland Sommarskog Lines: 87 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Injection-Date: Tue, 31 Jul 2012 09:08:01 +0000 (UTC) Injection-Info: mx04.eternal-september.org; posting-host="1bbf89cf6d97a98086f02eab6f51f760"; logging-data="1279"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/uiwCZ9k72RJ11q9vc+kJL" User-Agent: Xnews/2005.10.03 Mime-proxy/1.4.c.4 (Win32) Cancel-Lock: sha1:cXyhFJF6A2izG3STNI25dzbAgQY= Xref: csiph.com comp.databases.ms-sqlserver:1189 Gene Wirchenko (genew@ocis.net) writes: > 1) I expect to have to create and insert to #Error quite frequently. > I would like a stored procedure for each of these. Unfortunately, due > to the lifetime rules for temporary tables, if I create #Error in its > own SP, it will be deleted as soon as the creation SP terminates. The > above is more verbose than I like. I would prefer to be able to code > something like the following in-line in my SPs: > execute CreErrTbl > execute InsErrRow 0,'Key not found.' > Is there a cleaner way to do this? Why wouid you creaet the temp table at all? Why not just return a result set SELECT 0 AS OrderBy, 'Key not found.' AS ErrorReponse Also, most clients gets confused if a stored procedure returns two results sets with different shape. Of course, it's perfectly manageable if you program the client correctly, but it's a little dubious. > 2) I query Primus twice, once for the row count and once for real. I > do wish to catch no rows as an error condition. I wish to avoid race > conditions. (I think I have a race condition here in the case of the > row being deleted just after the first time, but before the second. > Am I correct in thinking this? If so, how do I correct it?) So the purpose with the above procedure is to check for existence? Why in that case not use SELECT @output = CASE WHEN EXISTS (select * from Primus where PKPri=@theKey) THEN 1 ELSE 0 END And this scraps the result set above entirely. The stored procedure have no reason to ponder whether existence is an error not. The job of the procedure is return whether there is a row or not. It's up to the caller to consider that to be an error or not. As for your actual question, the answer is that if you are in a transaction and you add the table hint (SERIALISABLE) you will prevent a row to be inserted until you commit. However, see the next point: > 3) The front-end will be a Web page. I want to retrieve a row, edit > it, and write it back. I would like that any changes in the meantime > are not overwritten. As far as I can see, this is not going to work > to protect meantime changes, and I need to do something more. > > Can I lock the read row? If so: > a) Can I release the lock after updating the row? This would be in > a separate stored procedure and a different XMLHttpRequest. > b) Can I have the row lock expire after a period of time (say, 15 > minutes)? Someone could close his browser after reading a row, and I > do not want the row lock kept indefinitely. Yes, you can lock a row if you read it in a transction and the isolation level is at least PEPEATABLE READ. The row will be released when you commit. BUT DON'T DO THIS! Having a transaction in progress when you are waiting for user input is an absolute no-no. The most commonly used solution is optimisitic concurrency. You add a timestamp (a.k.a rowversion) column to the table. This column is auto- matically updated with a database-unique monotonically growing value when a row is updated. Thus, you read the timestamp value and when you write back you compare, and if they are different someone else has come in between. If you want pessimistic concunrrency, you will have to roll your own. > 4) I would like to avoid using ActiveX to communicate between the > Web page and SQL Server and preferably without having to add more > software to the server. Otherwise, my app is going to be limited to > IE. I have been unable to find any details on how to do this. > Pointers would be appreciated. Sounds like a question for a different newsgroup. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx