Multiple Process In A Single Transaction Using SB
Jul 21, 2006
We have a scenarion in a batch job. There are 3 sp's which are executed for every record in a table. After the execution of first sp the second sp executes depending upon the result of first sp. Simillarly for 2nd and 3rd sp.
Now if any sp execution fails than the whole transaction for that record in the table has to be rolled back.
Can this whole process of executing the multplie sp's insides a single transaction be accomplished using service broker with either a single queue or multiple queues?
sp 1 (1 record)
__________ l_____________
l l l sp2 ( 3 records for 1 record in sp1)
Simillalry for the one record in sp2, sp3 executes for multiple records.
Or in other words if processing of any message in a queue fails all the messages that have been processed already shoould be rolled back and no further execution should happen?
Also i would like to know can a conversation group be rolled back if processing of any message in the conversation group fails. I am asking this as we can club sp2 and sp3 together to get the results directly and than try for parallel processing.
P.S. We have a multiprocessor 64 bit server.
thanks
View 1 Replies
ADVERTISEMENT
Mar 6, 2008
I have over 500 transaction records in a single DB process handling within SQL transaction (Begin, Commit, RollBack and End).
Is there any limitation for the following rollbacktransaction function to handle more records (eg. over 500 records)? Public Shared Sub RollBackTransaction()
Dim transactionObj As Object
Try
transactionObj = SqlTransaction.GetExistingTransaction
If (Not IsNothing(transactionObj)) Then
CType(transactionObj, SqlTransaction).RollBack()
End If
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Sub
View 2 Replies
View Related
Feb 26, 2008
Hi All,
I want to know that how we will be able to update multiple rows in single transaction.
e.g If original database is
S_No Data
1 -
2 -
3 -
4 -
After
S_No Data
1 1
2 3
3 6
4 10
View 4 Replies
View Related
Sep 27, 2006
First of all i do not know whether this is the right form to ask the question Let me describe the scenario iam using Iam generating xml files at a particular place and sending them to a server xml1|--------------------->dataset1------------------------------>adapter1.update(dataset1)xml2|----------------------->dataset2----------------------------->adapter2.update(dataset2)xml3|----------------------->dataset3------------------------------>adapter3.update(dataset3) all the three updates should happen in only one transaction if any one of the update fails then the transaction should rollbackcan anyone tell me a way to do iti am desperately in search of any ways to do it can anybody help please
View 2 Replies
View Related
Apr 11, 2008
Hello,
I have problem for insert multiple query for insert in differenr tabels for a single record.
I have mail record for candidate and now i wants to insert candiate labour info, candidate passport detail in diff tabel like candidatLabour and candidatePassport,
i used two store procedure for it and i write code for it.and it works fine,but i think that if one SP executed and one record inserted but then some problem occure and 2nd SP not executed then...........
so plz help me
Thanks
View 5 Replies
View Related
Nov 14, 2007
Hi,
I was trying to extract data from the source server using OLEDB Source and SQL Server Destination when i encountered this error:
"Transaction (Process ID 135) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
What must be done so that even if the table being queried is locked, i wouldn't experience any deadlock?
cherriesh
View 4 Replies
View Related
Feb 14, 2007
Hi Folks,
I am having this table locking issue that I need to start paying attention to as its getting more frequent.
The problem is that the data in the tables is live finance data that needs to be changed and viewed almost real time so what I have picked up so far is that using 'table Hints' may not be a good idea.
I have a guy at work telling me that introducing a data access layer is the only way to solve this, I am not convinced but havnt enough knowledge to back my own feeling up. (asp system not .net).
Thanks in advance
View 1 Replies
View Related
Jan 6, 2012
We are facing deadlock issue in our web application. The below message is coming:
> Session ID: pwdagc55bdps0q45q0j4ux55
> Location: xxx.xxx.xxx.xxx
> Error in: http://xxx.xxx.xxx.xxx:xxxx/Manhatta...Bar=&Mode=Edit
> Notes:
> Parameters:
> __EVENTTARGET:
> __EVENTARGUMENT:
[code].....
View 2 Replies
View Related
May 29, 2008
I have a need to process a single dimension at different points throughout the day. The rest of the cube is static and processed completely everynight.
I believe I can process just the single dimension via SSIS (have not verified this) but I was wondering if there is script I can run via an agent job cause I need to do this on 4 servers.
Any help/guidence is greatly appriciated.
View 4 Replies
View Related
Jun 21, 2007
I am writing an adapter for a two way integration between two databases. The table I am currently working on has an update and insert trigger to send data to the other database via service broker.
Asd I insert/update data via my adapter I don't want the triggers to fire but I do want them to fire if somebody else make data modifications while I am working with the table.
I am doing all mu modifications within a transaction but I found I couldn't disable the trigger within a stored procedure to keep the disable and enable as close together as possible.
Does anybody ahve any ideas how to overcome this scenario or am I worrying about nothing as my transaction will keep other users from modifying the data until I've finished.
Any advice would be great.
Cheers
View 4 Replies
View Related
Mar 3, 2008
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
View 8 Replies
View Related
Apr 21, 2015
I have a table with single row like below
 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0Â Â Â | Value1Â Â Â | Value2Â Â Â Â | Value3Â Â Â |Â Value4Â |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _
View 6 Replies
View Related
Aug 22, 2007
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
View 1 Replies
View Related
Nov 10, 2006
Hi,
I got the following error when I try running my “comments.aspx� page with visual studio 2005Exception Details: System.Data.SqlClient.SqlException: Transaction (Process ID 83) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
fExecuteQuery(String commandText, String dataSetName) +90 fExecuteQuerySet(String commandText, String dataSetName) +36 ASP.comments_aspx.GetNarComment() +618 ASP.comments_aspx.Page_Load(Object sender, EventArgs e) +476 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.BasePage.OnLoad(EventArgs e) +1013 System.Web.UI.PopupPage.OnLoad(EventArgs e) +4 System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750
The segment code was the problem sits in file "comments.aspx"::
…
string cmdText=��;
cmdText = string.Format(@"-- Get All Narative comments fo all students in the course from @selectedTermID down to its child terms
exec aagGetStudentSectionComments @companyID={0}, @sectionID={1}, @selectedTermID={2}, @StudentID={3}
", _companyID, sectionID, selectedTermID, studentID);
ds = fExecuteQuerySet(cmdText, "getMySet");
…
// the 2 functions to deal with ADO.NET to be called in above code segment
// return a dataset.
public DataSet fExecuteQuery(string commandText, string dataSetName)
{
DataSet mds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(commandText, _cn);
da.SelectCommand.CommandTimeout = 600; // 600 seconds
da.Fill(mds, dataSetName);
return mds; // return dataset
}
// Assume para commandText contains sql query which returns a table or more.
// return a DataSet.
public DataSet fExecuteQuerySet(string commandText, string dataSetName)
{
DataSet mds = new DataSet();
mds = fExecuteQuery(commandText, dataSetName);
return mds; // return DataSet
}
Please give me the reason why that dealock happens?Thanks in advance
View 2 Replies
View Related
Feb 27, 2006
ive seen this Deadlock Error message out on the internet being discussed, but no solution being offered.
i have a windows service that's running Select Statements [one at a time] - so unless there's some command in sql server that would re-run these - it could be a problem for me.
now if im running this select proc manually - of course i see the message and re-run the process, but how can this be accomplished programatically.
see msg below:
Transaction (Process ID 106) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
thanks for any help on this
rik
View 5 Replies
View Related
May 10, 2015
Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
View 4 Replies
View Related
Sep 3, 2014
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies
View Related
Nov 5, 2002
Hi,
My transaction log backup task for the production database which normally takes about 10 seconds has been running for almost about 2 hours now.
Is there any way to stop it without restarting sql services? The scheduled task was stopped from the jobs but spid is still in 'runnable' status. I should not kill 'backup log' or 'xp_sqlmaint' tasks.( It doesn't solve the problem anyway. the SPID goes in rollback status and stays like that until server is rebooted). Any way to find out what causes this problem and how to prevent it?
Thanks,
Shaili
View 2 Replies
View Related
Nov 29, 2004
;)
Hello Everybody,
My name is Fabio and I post from Italy.
First, I don't know if this argument was already discussed in the past, but I'm new in this group so ...
Second I'm not so expert in DB due to the fact that I'm using SQL for the first time in my life ...
I use a store procedure to pass to every single user in my intranet (more than 150), details of different clients taken from an SQL table containing around 30.000 names.
Users have an ASP page displaying the information Selected in the DB.
This means that 150 users display info of 150 different clients.
To to this I use this code in store procedure:
CREATE PROCEDURE sp_assign_name
@iduser int
AS
if exists(select top 1 * from recallornotes where tmkoperator= @iduser)
update nominativitelecom set tmkmotrecall=convert(nvarchar(1), tmkstatus), tmkstatus=7 where id in (select top 1 id from recallornotes where tmkoperator=@iduser)
else
begin
if exists(select top 1 id from nonotes)
update nominativitelecom set tmkmotrecall=convert(nvarchar(1), tmkstatus), tmkstatus=7, tmkoperator =@iduser where id in (select top 1 id from nonotes with (UPDLOCK) order by NewID())
end
GO
This is working quite well when the number of users are more ore less around 50/60, when the number grows, on the IIS server (Pentium IV server, with Win 2000 in English, MS SQL 2000, and 1 Giga of ram), a file called DLLHOST.exe start to use the 100% of the CPU, and the users cannot display any other ASP page on their screens.
It is not a virus (some newsgroup report this problem connect to a worm virus, but we have latest antivirus files installed and spyware detect/delete on).
The SQL log reports this error:
"transaction (process id 69) was deadlocked on (lock) resources with another process and has been chosen as the deadlock victim. rerun the transaction".
Is there a way to avoid the conflict that occur when different users are trying to select the same record in the DB ?
In other terms, which process will you use in the same situation to select one record per user ?
Thanks in advance for your precious help,
Fabio
View 1 Replies
View Related
Apr 29, 2015
The process could not execute 'sp_repldone/sp_replcounters' on 'sqldb2008'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011).The specified LSN {00000000:00000000:0000} for repldone log scan occurs before the current start of replication in the log {001317bf:0000f736:0008}. (Source:Â
MSSQLServer, Error number: 18768).The process could not set the last distributed transaction. (Source: MSSQL_REPL, The process could not execute 'sp_repldone/sp_replcounters' on 'sqldb2008'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
View 2 Replies
View Related
Oct 3, 2007
I've a weird problem in my application. In of the pages, while trying to update the text box "Name", when I enter Linda's test, it gets saved as Linda''s test. I'm not sure if this is a problem due to SQL server. When I look at the stored procedure, I don't anything different. Also, when I update the table directly in SQL Server, the result is displayed in single quote. But if I update the field thro' the application, the returned name is with double quotes instead of single quote. Has any of you faced problems like this? What am I missing? What do I need to do to get the name saved the way I entered (with single quotes) instead of double quotes?
View 1 Replies
View Related
Feb 15, 2006
I am afraid that just after @statusOfEmployee is retrieved from table1, but before table2 is updated, someone else (a second user) calls this same stored procedure and changes the @statusOfEmployee value. This would create an inconsistent update of table2 by first user, since the update of table2 'might' not have gone ahead if the latest value of @statusOF Employee was used. CAN SOMEONE PLEASE HELP ME WITH THIS SITUATION AND HOW I CAN BE SURE THAT ABOVE DOES NOT HAPPEN SINCE MULTIPLE USERS WILL BE HITTING THIS STORED PROCEDURE?
declare @status intbegin transet @status = (select statusOfEmployee from table1)if @@ERROR = 0 begin update table2 set destination = @destination /* @destination is an input parameter passed to the sp*/ where @currentStatus = @status if @ERROR = 0 commit tran else rollback tran endelse rollback tran
return
View 7 Replies
View Related
Oct 5, 2007
Hi:
On a production SQL2000 STD sp4 server,
1. I have dropped 10 tables with each around 1-2 gb in DB ABC
2. I had run DBCC ShrinkDatabase (ABC, 20) and it is failed after running 133 hours this morning. Yes, 133 hours.
It ran 72 hours last month and shrinked from 200 gb to 180 gb.
Thus, I expected it should be <= 72 hours to fnish since 10 more tables are dropped ?
Msg 1205 Transaction (Process ID 75) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
DBCC shrinkDatabase will cause deadlocking? how to avoid it?
Is there other ways to speed up?
thanks
-D
View 1 Replies
View Related
Jul 20, 2005
We are having a really big problem with a zombie process/transactionthat is blocking other processes. When looking at Lock/ProcessIDunder Current Activity I see a bunch of processes that are blocked byprocess 94 and process 94 is blocked by process -2. I assume -2 is azombie that has an open transaction. I cannot find this process tokill and it seems that this transaction is surviving databaserestarts. I know which table is locked up and when I run a select *from this table it never returns. Does anyone have any ideas as tohow to kill is transaction.Any help is appreciated.A. Tillman
View 4 Replies
View Related
Jan 2, 2006
In an SSIS package I am continually getting the same error:
"Transaction (Process ID 58) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The package is attempting to do a simple Execute SQL Task. Since this seems like a database and not integration services issue, does anybody have any thoughts or insight into this error and where to begin troubleshooting?
Thanks,
Adrian
View 5 Replies
View Related
Mar 22, 2002
Hi,
While watching through performance monitor the processor time often goes high above the memory.
Could you please tell me how to find out which process is doing that.
Thanks
John Jayaseelan
View 3 Replies
View Related
Apr 10, 2006
Hello,
I currently have a Transactional Log reader agent failing with the below error:
The process could not execute 'sp_replcmds'
Error: 14151, Severity: 18, State: 1
SQL Server Assertion: File: <logscan.cpp>, line=2223
Failed Assertion = 'm_noOfScAlloc == 0'.
Stack Signature for the dump is 0x24642FE5
Error: 3624, Severity: 20, State: 1.
SQL Server Assertion: File: <logscan.cpp>, line=1985
Failed Assertion = 'startLSN >= m_curLSN'.
Stack Signature for the dump is 0xD7150BD4
Now, I understand that SP4 is supposed to fix a similar issue. SP4 has been installed and the errors keep happening. I do notice that the hot fix mentions different line numbers than the above errors. Does anyone know if this is a new bug? If not can someone explain the fixes to me, thanks,
Tech Drone.
View 3 Replies
View Related
Nov 30, 2006
Hi Friends,
Is it possible to create single sql connection and running several transaction at the same time? If so, can you explain?
Thanks.
View 2 Replies
View Related
Jul 23, 2005
I have a query batch "update" script that upgrades my users database from,say version 0 to version 1, or from version 1 to version 2. I would like toknow how I can wrap the entire script in a transaction, so that either thewhole thing succeeds or none of it does.For example:BEGIN TRANSACTION.......... Alter some tables.....GO.......... Alter a stored procedure.....GO.......... Create a new stored procedure.....GOCOMMIT TRANSACTIONorROLLBACK TRANSACTIONGO(how do I get to the "ROLLBACK TRANSACTION" if an error occurs in the updatescript?)
View 2 Replies
View Related
Jul 23, 2005
Hi,I'm running SQL Server Version 8.00.194 on Windows 2000.I am am running this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere ReutersSymbol = 'IBM.N'and TheoVolImplied > 0.0TheoVolImplied is of type float, precision 15, length 8.When I run this query I get this error:Server: Msg 3628, Level 16, State 1, Line 1A floating point exception occurred in the user process. Currenttransaction is canceled.If I run this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere TheoVolImplied > 0.0It works fine with no problems.If I run this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere ReutersSymbol = 'IBM.N'It works fine with no problems.Anyone have any ideas about what might be wrong?
View 2 Replies
View Related
Jun 11, 2015
I have a transaction table to store all the transaction happened on a single day. as per my requirement I wrote the query like this select Currency Code,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where TransactionCode in ('BNT' ,'BCN','BTC','STC','SCN','SNT') group by TransactionCode,CurrencyCode,TransactionAmount order by CurrencyCode..I got the result like this
My I want to show this result like this
              Â
ARS
 BNT        0           0
 BCN       0           0
 SCN       1           12
 BTC       0            0
 STC       0            0
 SNT       0            0
   Â
[code]...
and so on for all the the currency lists.how can I achieve like this .
View 6 Replies
View Related
Feb 7, 2002
In DTS designer I need to solve the following problem:
Problem:
- Import x-number of flat files into SQL Server one at a time.
Issues:
- I can't call the package recursively to process
the files because I don't want to eat up computer resources.
(there is a possiblity that there could be hundreds of files
that need to be processed and I wouldn't want to have hundreds
of package instances running).
- The files names cannot be read into a list then processed
because while the list is being processed more files could
be "dumped" into the folder by the main frame. And, all files
must be processed.
I thought about scheduling a job at the end of the package
to run the package again, however, I haven't figured out
how to do that and am still looking. DTS is new to me.
Thanks,
Martin
View 3 Replies
View Related
Nov 1, 2007
Goofed up and ran an update query. It messed up all the data in a single table. I'm trying not to restore the table from a previous backup since the backup is more than 20 GB. It's going to take forever to restore it. Any advice would be much appreciated!
Thanks!
Charles.
View 3 Replies
View Related
