Rollback In A Trigger??? (explicit/autocommit Transactions)
Jun 21, 2006
I€™m using triggers for some more advanced integrity check. The problems is that the same trigger can be run from explicit transaction (this is when I start transaction from .NET) and as autocommit transaction ( very rare, only when we do some maintenance directly with SQL statements).
Currently if I want to rollback transaction from trigger I only issue RAISERROR statements, then .NET application catches this error and generates rollback. But the problem is if trigger is raised from some SQL statements outside .NET application (normally some maintenance work direct from SQL manager ) in that case error is generated but there is no rollback.
Is there any way to distinguish if transaction in trigger is explicit or autocommited, because for autocommited transaction I also need use ROLLBACK TRANSACTION?
I€™m using SQL 2005!
Best regards
edvin
View 6 Replies
ADVERTISEMENT
Feb 27, 2006
I have an cursor that loops 720 times.
Each FETCH does 6 INSERTS.
Each FETCH the 6 INSERTs a total of 200-300 records appended.
Would explicit transactions speed up the cursor?
In Oracle, I would keep track of how many times I've looped, and commit every nth time. I would put a catch remainder COMMIT at the end of the procedure.
I've got the IF @@TRANCOUNT > 19 COMMIT TRAN (about 6k records), but say I had <= 19 remaining at the end of the cursor, how do you get those to commit? I tried to put an IF @@TRANCOUNT > 1 COMMIT TRAN, but that didn't work.
What's a good @@TRANCOUNT to commit at in this case?
Thanks crew,
Carl
View 4 Replies
View Related
Jul 23, 2005
When I need to perform an update against multi-million row table Itypically specify @@rowcount, to reduce locks.e.g.set @@rowcount 1000while exists (select * from myTable where col2 is null)update myTableset col2 = col1 + 'blahblah'where col2 is nullHowever, my boss' script does something like this. I think it works OKbut it seems overly complicated to me. Any thoughts?while exists (....)begin traninsert into #tableselect ...update myTableset ...from myTable join #table ...(@numberOfRows is a counter variable, tracking #rows that have beenupdated since last batch)if @numberOfRows > 1000begincommitbegin tranendend
View 2 Replies
View Related
Dec 17, 2004
Hello:
I am just starting to falmiliarize myself with SQL transactions... I just created an SQL transaction... The first statement gets a value, if the value equals "" then the second statement executes... So if the value <> "" Then the second statement wont execute... What would happen in this scenario if the .Rollback is triggered? Heres my code:
Try
conSqlConnect.Open()
objTransaction = conSqlConnect.BeginTransaction
cmdSelect.Transaction = objTransaction
cmdInsert.Transaction = objTransaction
dtrdatareader = cmdSelect.ExecuteReader()
While dtrdatareader.Read()
varCheckNumber1 = dtrdatareader("Status")
End While
dtrdatareader.Close()
If varCheckNumber1 = "" Then
cmdInsert.ExecuteNonQuery()
End If
objTransaction.Commit
Catch
objTransaction.RollBack
Return "00"
Finally
If conSQLConnect.State = ConnectionState.Open Then
conSqlConnect.Close()
End If
End Try
Any Ideas?
View 5 Replies
View Related
Apr 20, 2007
hi everybody,
i have 4 flat files from a source folder which updates four different tables, this has to be done parallely,on success of this transaction the files have to be moved to another folder.
my problem comes here,now if there is any problem in moving any file to another folder,that particular transaction has to be rolledback without affecting others.i tried setting the transaction property of the control flow,but it rollbacks all the transaction..
please help me on this
View 4 Replies
View Related
Mar 11, 2004
may be an idiotic question
I have created a table test1 with primary key as given below. I have written a procedure to insert rows. is the rollback transaction given under is correct?. (OR) shall i give the rollback only once at the end?
need more explanation on the rollback transaction.
CREATE TABLE [TEST1] (
[COL1] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [TEST1] WITH NOCHECK ADD
CONSTRAINT [PK_TEST1] PRIMARY KEY CLUSTERED
(
[COL1]
) ON [PRIMARY]
GO
ALTER PROCEDURE T AS
BEGIN TRANSACTION
INSERT INTO TEST1 VALUES('A')
IF (@@ERROR <> 0) GOTO ERR
INSERT INTO TEST1 VALUES('B')
IF (@@ERROR <> 0) GOTO ERR
INSERT INTO TEST1 VALUES('B')
IF (@@ERROR <> 0) GOTO ERR
INSERT INTO TEST1 VALUES('C')
IF (@@ERROR <> 0) GOTO ERR
INSERT INTO TEST1 VALUES('D')
IF (@@ERROR <> 0) GOTO ERR
ERR:
IF (@@ERROR <> 0)
ROLLBACK TRANSACTION
else
COMMIT TRANSACTION
View 5 Replies
View Related
Jun 10, 2015
I am working on of the T-sql statement that do updates. This statement is running in the job. We set up the notification reached to operator when the job failed.
,
But I need whenever the transactions are rolled back, it has to notify to the team. Below are the steps in the job.
DECLARE @NextRunDate DATETIME = DATEADD(hh,2,CAST(CAST(DATEADD(day,1,GETUTCDATE()) as DATE) as DATETIME))
BEGIN TRY
BEGIN TRANSACTION
UPDATE [RECompanyTask]
SET NextRunDate = @NextRunDate
WHERE SetupOptions = 0 AND [Enabled] = 1
[Code] .....
View 3 Replies
View Related
Nov 28, 2007
greetings from germyn,
heres my question:
someone deleted data from our sql server in several tables. our backup is from yesterday evening and 150 people worked with the database for 7 hours today so it is not possible to restore the database from backup. is it possible to use the ldf to rollback the transactions deleted the data? Can someone give me an idea?
thx
Kaeppi
View 1 Replies
View Related
Sep 18, 2007
I have a series of questions about SSIS and transactions. The answers to these questions are probably so obvious that I can't see them, so please feel free to just point out what it is that I'm missing. My transaction-processing experience is very low-level, so I'm probably just not seeing how it's done at the high level of SSIS.
The first question is one that I may know the answer to, so please confirm:
Consider a package with TransactionOption set to Supported. It contains a single Execute SQL Task with TransactionOption set to Required. Is it true that if that Execute SQL Task succeeds, that the transaction commits, and that if the task fails, the transaction rolls back?
Consider another package with TransactionOption set to Supported. It contains a Sequence Container with TransactionOption set to Required. That container contains our same Execute SQL Task, but that is joined to a script task by a "success" precedence constraint. The script task simply returns Dts.Results.Failure. Is it the case that the transaction will roll back? That is, is it truly a simple failure result that would initiate the rollback?
If a DataFlow Task is the one that is set to Required, does that mean that every transactional operation within that task will commit in a single transaction? For instance, if I'm inserting five rows for each input record from a flat file, and if my flat file has 1000 records in it, will I see a single transaction with 5,000 rows?
Thanks for your patience!
View 5 Replies
View Related
May 5, 2008
hi,
I have wriiten the code cn.Open();
SqlCommand CmdInsertAct1 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName1.Text.ToString() + "','" + txtActAmt1.Text.ToString() + "','" + ddlAct1.SelectedValue + "' )", cn);SqlCommand CmdInsertAct2 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName2.Text.ToString() + "','" + txtActAmt2.Text.ToString() + "','" + ddlAct2.SelectedItem.Value + "')", cn);
SqlCommand CmdInsertAct3 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName3.Text.ToString() + "','" + txtActAmt3.Text.ToString() + "','" + ddlAct3.SelectedItem.Value + "')", cn);SqlCommand CmdInsertAct4 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName4.Text.ToString() + "','" + txtActAmt4.Text.ToString() + "','" + ddlAct4.SelectedItem.Value + "')", cn);
SqlCommand CmdInsertAct5 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName5.Text.ToString() + "','" + txtActAmt5.Text.ToString() + "','" + ddlAct5.SelectedItem.Value + "')", cn);
CmdInsertAct1.ExecuteNonQuery();
CmdInsertAct2.ExecuteNonQuery();
CmdInsertAct3.ExecuteNonQuery();
CmdInsertAct4.ExecuteNonQuery();
CmdInsertAct5.ExecuteNonQuery();
cn.Close();....................................................................
in this code I want to put Commit,Begin,Rollback Transactions.Plz help me.send replies urgently.
View 3 Replies
View Related
Jul 26, 2007
Hi All,
I have set of stored procedures under one transaction where as each SP saves the data in one table and any error occurs then it saves the error details in errorlog table in the same SP. Whenever error occurs, all my transactions including error logging also getting rolled back. I want to keep the error logging part alone when my transaction is getting rolled back.....
That is,
try
{
Transaction A
{
Storedprocedure A
StoredProcedure B
StoredProceudreC
commit Transaction A
}
}
catch()
{
rollback A
}
Where as,
My each SP might contains the statements like,
StoredProcedure A
{
insert values in table A
if ( @@Rowcount =0)
insert values in errorlog ('Error occurred in table A')
}
Now, if any error occurs in Transaction A, all my values stored in 'errorlog' also getting rolled back. Is that any way to stop rollback only for errorlog table in the entire transaction?
(Tried using triggers, that was also not helpfu
SavePoint will save unnecessary part also.. ).
Is that any way to track the error and maintain in errorlog?
ManyThanks in advance,
Jaya
View 4 Replies
View Related
Apr 30, 2007
Hi all,
Sometimes when I do "alter database ABCD set partner failover" I get the following message: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
In 99 percent of the cases after such message the first attempt to use an open connection would also raise an error such as "Exception: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"
After the first error all subsequent queries would run perfectly.
What am I missing?
Avi
View 4 Replies
View Related
Jan 15, 2007
Hi,
I need help for this:
I need to make trigger which makes rollback
if INSERT- or UPDATE-statement try to make
dublicate row (col1+col2)
I try this, but it don't work:
CREATE TRIGGER trg_col1_col2_check
ON TABLE1
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON
IF (SELECT DISTINCT 1
FROM TABLE1
GROUP BY col1, col2
HAVING count(*) > 1) = 1
BEGIN
IF (SELECT
case
when (i.col1 = ''
AND i.col2 = '') then 0
else 1
end
FROM INSERTED i) = 1
BEGIN
Print 'Col1 and col2 Already exist'
ROLLBACK
END
END
END
View 8 Replies
View Related
Jul 20, 2005
Hi, Everybody,I'm a Hungarian SQL user and I need a little help for SQL Server 7 !I protect my table against bad data with a trigger. I use ROLLBACK andRAISERROR statement in this trigger. Users can get my error messageafter manual input, but the stored procedure always cancel because ofROLLBACK. So the input program dont't have chance to analyze the errormessage. I could work without ROLLBACK, but it wouldn't be the bestsolution.What's the correct solution with ROLLBACK statement?
View 3 Replies
View Related
Nov 14, 2006
I'm receiving the below error when trying to implement Execute SQL Task.
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'
I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.
Anyone know of the reason?
View 1 Replies
View Related
Feb 26, 2008
Recently I created a DDL Server-scope trigger using the following:
create trigger stop_alter_database on all server
for ALTER_DATABASE
as
rollback;
print 'database change stopped by stop_alter_database';
go
Then I ran the following script:
alter database [test] modify file (name=test', maxsize=2028mb);
The result was:
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
database change stopped by stop_alter_database
The problem is that when I checked the max size of the data file it had changed. So, the statement was never rolled back. Is there something I'm missing because I can't find any documentation or articles that state the inability to rollback alter database statements. Whats going on?
View 2 Replies
View Related
Jun 18, 2014
I have two tables called ECASE and PROJECT
In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .
When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.
When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.
I need to prevent the deadlock .
View 3 Replies
View Related
Aug 2, 2001
I want to know how to set not autocommit at SQL Server 6.5 database ?
Please Help
View 1 Replies
View Related
Jul 8, 2004
Hi,
What is the SQL Server equivalent for "set autocommit on" of Oracle ?
Thanks,
Sam
View 4 Replies
View Related
Feb 6, 2008
How do i find the current status of autocommit. whether it is set to ON or OFF.
Is there any way to check this out thru management studio ?
View 4 Replies
View Related
May 9, 2006
Hi:
The first stmts in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_35bq.asp are
"Autocommit mode is the default transaction management mode of Microsoft® SQL Server„¢. Every Transact-SQL statement is committed or rolled back when it completes."
"if a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A SQL Server connection operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions."
My question is, how do we change autocommit mode. I don't want the dml to be commited until i explicitly do commit. This should be default setting.
I want this change either to be made at database level, maybe by doing some setting or through some T-SQL stmt.
I DO NOT want to use anything that has to be done in query windows like set ... off | on in each and every query window i use.
The change has to be done only once and should persist through-out.
Any solution will be of help to me.
Thanking you,
Regards,
kumar
View 4 Replies
View Related
Sep 8, 2015
I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:
create TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'Smith'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end
I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.
View 8 Replies
View Related
Jun 8, 2006
Hello,Is there anyway.. we can disable AUTOCOMMIT option in MSSQL serverwhile executing the SQL queries via SQL ANALYZER
View 2 Replies
View Related
May 22, 2005
Hi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated
View 3 Replies
View Related
Feb 4, 2008
is anybody using this? it looks like a lot of trouble to return a formatted string. I am considering it for something. Any opinions?
http://technet.microsoft.com/en-us/library/aa226532(SQL.80).aspx
caution: you have to X out of the Do You Want to Download Silverlight popup.
View 12 Replies
View Related
Jul 23, 2005
i have a situation, where i need to group my information by a certainid, but that information which should already be grouped, i need toorder based on a number in one of the columnsexample:<ex id=2 listorder=1><description>desc 2</description></ex><ex id=1 listorder=2><description>desc 1</description></ex>in my order clause i first put order by the id, and it will group theinformation accordingly, but will put the lowest number id first, notcaring about the listorderbut if i order by the listorder column first, the information isn'tgroupd properlyi don't know if i made myself clear enough?hope someboyd can help methnx in advance
View 1 Replies
View Related
Jun 8, 1999
Hi ,
I've tried to switch MS-Sql/Server 6.5 on explicit_transactions
without success.
(set implicit_transaction on/off)
What is the exact syntax for doing that ?
Herve
(PS: Thanks Gregory for your quick answer )
View 1 Replies
View Related
Jun 3, 1999
Hi All,
I don't know MS-SQLserver internal system at all. I 've just used Oracle
a couple years ago and so in some cases (e.g using TP-monitor MTS or Tuxedo)
you can switch off the implicit transaction by using
the option AUTOCOMMIT ON/OFF.
How can switch off the implicit transaction system on MS-SQLServer ?
In advance thanks,
Herve
View 1 Replies
View Related
Sep 7, 2004
I created a SELECT statement that uses the FOR XML EXPLICIT clause so that SQL Server data can be exported to another system. Format thios system accepts is XML. What should I define within a DTS package so that this Select statement is executed and an XML file is created?
View 12 Replies
View Related
Jan 30, 2007
Hello,
In the SP below, I get the following error when I run it:
Explicit value must be specified for identity column in table 'Tests' when IDENTITY_INSERT is set to ON.
From what I've read of the INSERT method, it says you can add a variable value. So I don;t understand why it won't take mine.
CREATE PROC qaspAddTestRecord AS
DECLARE @aKey bigint
DECLARE @aTestSuiteId bigint
exec qaspGetNewID 'Tests', @aKey OUT
SELECT @aTestSuiteId= NewID FROM ID WHERE TableName='TestSuite'
SET IDENTITY_INSERT Tests ON
INSERT INTO Tests (TestSuiteId, TestIDInternal) VALUES (@aTestSuiteId, @aKey)
SET IDENTITY_INSERT Tests OFF
GO
--PhB
View 4 Replies
View Related
Jan 7, 2008
Hello all,I'm trying to generate some XML directly from MS SQL with thefollowing codeSELECT1 AS tag, NULL AS parent, NULL AS [GoogleCustomizations!1], NULL AS [Annotations!2], NULL AS [Annotation!3], NULL AS [Annotation!3!about], NULL AS [Annotation!3!score], NULL AS [Label!4], NULL AS [Label!4!name]UNIONSELECT2 AS tag, 1 AS parent, NULL, NULL, NULL, NULL, NULL, NULL, NULLUNIONSELECTTOP 503 AS tag, 2 AS parent, NULL, NULL, NULL, 'www.' + domainName, 1 -- score, NULL, NULLFROM tbl_auDomainNameUNIONSELECT4 AS tag, 3 AS parent, NULL, NULL, NULL, NULL, NULL, NULL, '_cse_ad-o6lgdody'FOR XML EXPLICITThe XML it needs to create is as following<GoogleCustomizations><Annotations><Annotation about="www.clickfind.com.au/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation><Annotation about="www.lookle.com/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation><Annotation about="www.sensis.com.au/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation></Annotations></GoogleCustomizations>It is currently creating<GoogleCustomizations><Annotations><Annotation about="www.10000steps.org.au" score="1" /><Annotation about="www.101fm.asn.au" score="1" /><Annotation about="www.aao.gov.au" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation></Annotations></GoogleCustomizations>I cannot get my head around how I can get the label <Labelname="_cse_ad-o6lgdody" />in each element. Does anyone know?Thanks in advance.
View 2 Replies
View Related
Jan 31, 2007
HiI have to create an XML file based on a SQL SERVER 2005 table.Everything works fine:SELECT 1 as Tag,
NULL as Parent,
Ex_Id as [Exam!1!Ex_Id],
NULL as [Ex_Title!2!!cdata]
FROM Exams
WHERE ex_State = 'P'
UNION ALL
SELECT 2 as Tag,
1 as Parent,
Ex_Id,
ex_Title
FROM Exams
WHERE ex_State = 'P'
order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata]
FOR XML EXPLICIT, ROOT('Exams')
BUT when i add another column, a text column, I get into trouble: SELECT 1 as Tag,
NULL as Parent,
Ex_Id as [Exam!1!Ex_Id],
NULL as [Ex_Title!2!!cdata],
NULL as [Ex_Situation!3!!cdata]
FROM Exams
WHERE ex_State = 'P'
UNION ALL
SELECT 2 as Tag,
1 as Parent,
Ex_Id,
ex_Title,
ex_Situation
FROM Exams
WHERE ex_State = 'P'
order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata], [Ex_Situation!3!!cdata]
FOR XML EXPLICIT, ROOT('Exams')
The problem is clearly the fact that i have to sort on the text column, plus the fact that this column requires the CDATA tag enclosurePlease help me....i'm desperate :(
View 1 Replies
View Related
Jan 19, 2004
Hi:
How can I force MsSQL to a explicit DateFormat.
Like: 'DD/YY'
View 4 Replies
View Related
