.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links


Posted By:      Posted Date: September 14, 2010    Points: 0   Category :Sql Server
Hi guys I have 2 stored procedure sp_tlock and sp_tIncrease. In sp_tIncrease, increase cb while its less than budget. And in th sp_tLock, copy budget on cb, so no one can increase it any more. The sp_tLock run via one client but another one can run with multi-user at same rime. My question is: is it possible when sp_tLock is running from manager, sp_tIncrease run, too?. If this is possible, it will make Collision, so Best regards, Morteza   CREATE PROCEDURE sp_tLock AS BEGIN UPDATE tb SET CB = budget where id = 5 END ---------------------------------------------------------------------- CREATE PROCEDURE sp_tIncrease AS BEGIN declare @budget money declare @cb money declare @param money begin tran select @budget = budget, @cb = cb from tb where id = 5 if(@cb<@budget) begin if(@cb + 10 > @Budget) select @param= @budget else select @param= @cb + 10 UPDATE tb SET CB = @param where id = 5 end commit tran END

View Complete Post

More Related Resource Links

SQL Server Database Transaction

A transaction is a group of SQL commands executed together as a single component of work to be accomplished. If all of these commands succeed, then a transaction is committed - the changes to the data are made final. If any of the commands within a transaction fail, then the entire transaction is cancelled, or in SQL Server terms, the transaction will be rolled back.

Transactions are one of the things that keep your data safe, but they're not necessarily easy to understand. Here's some help:

Multiple web methods in one transaction


Hi everyone

Sorry I am kinda new in web services, so it may sound a bit newbish question.

We have a web service that comunicates with the database and exposes web methods that enables the application to access the database. Each method does only one operation (Insert, Update or Delete). What I need to do is create a transaction that will be consisted of multiple calls to the database, meaning multiple calls of more then one web methods. Can I do that? (I read that if the web method does not throw exception - or ContextUtil.SetAbort() is not called - the transaction will be commited automatically when the web method finishes executing, which I don't want to happen because I want to call another web method in the same transaction, and then manually commit the transaction)

Thank you in advance.

Web Services: Capturing and Analyzing Client Transaction Metrics for .NET-Based Web Services


This article presents a general-purpose client quality reporting mechanism that can be used in any .NET-based transaction system that employs HTTP/SOAP. The design uses client response time and quality recording, upload of logs as SOAP headers attached to new transaction requests, and server handoff of these headers to a low priority queue for logging and analysis. This technique gives an enterprise near real-time information on actual end-user response times. These response times reflect network delays, client application overhead and server delays. By using this technique, enterprises can avoid the need to develop custom software to mine HTTP logs.

Brian Connolly

MSDN Magazine July 2004

SSIS Package Transaction locks entire table

Hello, I have SSIS package that using transactions and working as expected. But this locks all the tables involved in the data flow that are get inserted/loaded. If I query those tables during package execution the query waits till SSIS releases the lock. But I can still query those tables using NOLOCK hint. Is this possible SSIS will lock only the rows that it insert/update/delete etc? So that other user can still use that table or another instance of same SSIS Package can load data into those tables? My Package: In the package level I have TransactionOption = Required. The Sequence (Data Flow is inside this sequence) TransactionOption = Supported. I had some issue in my package that I posted here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97 and also resolved. Now I descovered this issue and trying to resolve. Thanks, Prabhat

CLR Trigger with stored procedures - transaction error

Hi, I've developed sample CLR trigger which call stored procedure but if execution of stored procedure fail I got following exception The context transaction which was active before entering user defined routine, trigger or aggregate "CskTest" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting. The statement has been terminated.  My trigger [SqlTrigger(Event = "FOR INSERT", Name = "CskTest", Target = "Requirement")] public static void HandleRequirement() { using ( SqlConnection conn = new SqlConnection("Context Connection=true") ) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "Test"; conn.Open(); try { cmd.ExecuteNonQuery(); } catch ( Exception ex ) { SqlContext.Pipe.Send(ex.Message); } } } I wanna handle this exception in my catch block. How can I do this?

WF4 for High Volume Transaction Apps

Hi! I am in the middle of redesigning my application and I would like to consider WF4 as the center piece for my application logic. My application is an sms gateway service and it can reach up to millions of hits within a short period of time. I would like to know whether WF4 is suitable to be used for millions of hits type of application. Thank You :) Best Regards, Serena

Transaction Error!!!

<!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-alt:"Calisto MT"; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1107304683 0 0 159 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-alt:"Century Gothic"; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman";} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> Main StoredProcedure STARTS Begin Try Begin Transaction                 Sub StoredPr

Reinitialize transaction replication clear the subcriber data and replicating again

Hi all, I add new table in to mu publisher database and reinitialize the subscriber.And i select the option to create new snapshot and marked as reinitialize.When it starts the reinitializing it clear all data from subscriber and coping again.Am i missing any thing here to add new table into existing replication ? Regards, Theesh

How to lock tables during a transaction

Hi, is there a way do something like this:  BEGIN TRANSACTION --lock table1 --do work here --unlock table1 COMMIT TRANSACTION

how to handle 2 with this SP

below the 2 different XML that implies I have one or more than one <Transaction> under single <Header>, XML - 1  DECLARE @StrXML XML SET @StrXML = '<PDI_Approval_Message> <Header> <SourceSystem Value="PDI/Workforce" Name="WF"></SourceSystem> </Header> <Transactions> <Transaction> <Attributes> <WF_Regular_Hrs>12</WF_Regular_Hrs> <WF_Overtime_Hrs>3</WF_Overtime_Hrs> </Attributes> </Transaction> </Transactions> </PDI_Approval_Message>' EXEC test @StrXML XML - 2  DECLARE @StrXML XML SET @StrXML = '<PDI_Approval_Message> <Header> <SourceSystem Value="PDI/Workforce" Name="WF"></SourceSystem> </Header> <Transactions> <Transaction> <Attributes> <WF_Regular_Hrs>12</WF_Regular_Hrs> <WF_Overtime_Hrs>3</WF_Overtime_Hrs> </Attributes> </Transaction> <Transaction> <Attributes> <WF_Regular_Hrs>18</WF_Regular_Hrs> <WF_Overtime_Hrs>4</WF_Overtime_Hrs> </Attributes> </Transaction> </Transactions> </PDI_Approval_Message>' EXEC test @StrXML below SP return 1 row perfectly with XML - 1  and it won't retrieve 2 rows for XML - 2 ,&nbs

[OLE/DB provider returned message: [MERANT][ODBC Sybase driver]Distributed transaction enlistment fa

Ok, i have seen tons of articles on the error, but still can't figure it out. This is being executed through nested procedure calls, until it is roughly 3 levels deep, then makes a call to Sybase using dynamic SQL and OPENROWSET. The message that is returned is listed below. Yes, i looked up all I could find on it. The thing I find interesting is I can simply take the dynamic SQL and the openrowset call out and run it in query analyzer just fine. but when I run it through the procedure it generates the errors. Any thoughts?   [OLE/DB provider returned message: [MERANT][ODBC Sybase driver]Distributed transaction enlistment failed.] Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction. [OLE/DB provider returned message: [MERANT][ODBC Sybase driver]Load of XA library failed.] OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a]. John M. Couch Insight Enterprises Inc. 6820 S. Harl Ave, Tempe, Az 85283 Email: john.couch@insight.com

multiple executions of MERGE statement: Help with suitable TRANSACTION ISOLATION LEVEL

Folks, I am reasonably new to SQL Server. I am using SQL Server 2008 (no SP) on Windows XP. I am using the MERGE statement within a TSQL procedure to update a master/detail table pair (Master/Child), in which the MERGE inserts into the MASTER if a record based on the primary key doesn't exist and does, effectively nothing, if it does (well, it does an UPDATE set PK=PK so the record is passed on to the OUTPUT statement for insertion into the CHILD). Regardless as to the situation, the CHILD record has a record created when the MASTER exists or doesn't exist. Now, this code works fine with the standard TRANSACTION LEVELS. But I don't know what to do when I am running two instances of the same MERGE statement at the same time. One execution could create a record in the MASTER which the other process might try and create 5 minutes later. I really don't know what SET TRANSACTION ISOLATION LEVEL to use to allow both processes to run at the same time. I have looked at: ALTER DATABASE $(usedbname) SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE $(usedbname) SET ALLOW_SNAPSHOT_ISOLATION ON; But these don't seem to work with their associated TSQL calls. I know this is a complex issue, but as a new SQL Server user, I didn't know where else to go. regards Simon                              

Transaction Handling in SQL Server 2008

Hi there,        I am using .NET (C#) and sql server 2008 for my application, I am fetching records from a table and using gridview to show them.        Now saving selected records (using checkbox to select records) into another table row by row. before saving the records if records with same ID is present need to delete those records. could anyone tell me how to use transaction in SQL server 2008. any blog or website............... anything   Thanks, Sadaf

SQL 2008 Transaction Log too big from Replication

How do I clear transactions from log due to a failed attempt to setup replication?

Doesn't restart sql server rollback uncommitted transaction?

hi, i executed a long-running sql in the management studio. The sql updates many rows and all updates are within a transaction. After runing 30 mins, the hard disk is full. That's because of the uncommitted updates. Then, i restarted the sql server because I thought after restarting, the uncommitted transaction is rollback and the occupied hard disk space is also released. But i found that the transaction is rollbacked but the hard disk space is not released. Why the hard disk space is not released? how can i release the disk space now? Thank you for your help.

SQL 2005 LSBackup_SID transaction logs hung...

Hello folks, I've searched google dry.. I feel as if I am the only one with this issue (hope not)!!.. for some reason my transaction logs stopped generating files via the job LSBackup_sid, i had my log shipping very nicely setup my secondary DB was in (restoring mode) LScopy and LS restore were running quite nicely but not the LSbackup_sid job on the primary simply stopped creating transaction logs to any shared folder I point it to. I have tried to redo all the shipping log process, I have tried pointing it to a different path, i even restored my secondary DR database and brought it up with SAP to see if I can get my trn files back on the primary.. there simply is no error on the LSBackup job it just says running forever: see below   Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted 09/07/2010 00:22:01,LSBackup_PAB,Unknown,,,LSBackup_PAB,,,In progress,00:00:18.4770000,,,,,, 09/07/2010 00:21:43,LSBackup_PAB,Unknown,1,DBGRPNEW,LSBackup_PAB,Log shipping backup log job step.,,2010-09-07 00:21:48.30 Backing up transaction log. Primary Database: 'PAB'<c/> Log Backup File: '\\sapprd2\bckp_drc\PAB_20100906212148.trn',00:00:05,0,0,,,,0 09/07/2010 00:21:43,LSBackup_PAB,Unknown,1,DBGRPNEW,LSBackup_PAB,Log shipping backup log job step.,
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend