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.
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
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
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
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?
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).
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.
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.
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?
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
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.
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?
;) 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 ?
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)
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?
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
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?
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
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?
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,
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?)
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?
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 .
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.
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!
