Error 266 - I'm Confused, Only One Transaction With COMMIT And ROLLBACK

Jun 15, 2008

I have a transaction that calls one other sproc and also executes another set of queries, but for some reason I'm getting error 266: "Msg 266, Level 16, State 2, Procedure AddUserHaveTag, Line 26.  Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.  Previous count = 0, current count = 1."
There is NO transaction in the sproc AddTag.  It is also included below.

Here is the sproc with the transaction:1 set ANSI_NULLS ON
2 set QUOTED_IDENTIFIER ON
3 go
4
5 ALTER PROCEDURE [dbo].[AddUserHaveTag]
6 (
7 @UserHaveID int,
8 @Tag varchar(24),
9 @UserHaveTagExists bit OUTPUT
10 )
11 AS
12 SET NOCOUNT OFF
13 DECLARE @ErrorCode int
14 DECLARE @TagID int
15 DECLARE @TagExists bit
16
17 BEGIN TRAN
18
19 -- Call proc to add tag to Tags table
20 EXEC AddTag @Tag, @TagID OUTPUT, @TagExists OUTPUT
21
22 -- Check for errors
23 IF @ErrorCode <> 0 GOTO ERROR
24
25 -- Check for existing record, otherwise insert
26 IF EXISTS (SELECT 1 FROM UserHaveTags WHERE UserHaveID = @UserHaveID AND TagID = @TagID)
27 BEGIN
28 SET @UserHaveTagExists = 1
29 RETURN 0
30 END
31 ELSE
32 BEGIN
33 INSERT INTO UserHaveTags (UserHaveID, TagID) VALUES (@UserHaveID, @TagID)
34 SET @UserHaveTagExists = 0
35 END
36
37 -- Check for errors
38 IF @ErrorCode <> 0 GOTO ERROR
39
40 COMMIT TRAN
41
42 ERROR:
43 IF (@ErrorCode <> 0)
44 BEGIN
45 PRINT 'Unexpected error occurred!'
46 ROLLBACK TRAN
47 END
  Here is the AddTag sproc:1 set ANSI_NULLS ON
2 set QUOTED_IDENTIFIER ON
3 go
4
5 ALTER PROCEDURE [dbo].[AddTag]
6 (
7 @Tag varchar(24),
8 @TagID int OUTPUT,
9 @TagExists bit OUTPUT
10 )
11 AS
12 SET NOCOUNT OFF
13
14 IF EXISTS (SELECT 1 FROM Tags WHERE Tag = @Tag)
15 BEGIN
16 SELECT @TagID = TagID FROM Tags WHERE Tag = @Tag
17 SET @TagExists = 1
18 RETURN 0
19 END
20 ELSE
21 BEGIN
22 INSERT INTO Tags (Tag) VALUES (@Tag)
23 SET @TagID = SCOPE_IDENTITY()
24 SET @TagExists = 0
25 ENDAny advice?Also if you see any glaring errors or things I could be doing better, I'm open to suggestions. I'm fairly new to sprocs and transactions. Thanks,
Travis
 

View 4 Replies


ADVERTISEMENT

Error: COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Why?

Nov 13, 2007

Hello:
I am implimenting the creation of sequence numbers .I use an insert proc on a table that generates the numbers using an identity field:
procedure usp_createidentity

begin transaction

insert into [tblOrderNumber] with default values

rollback ' done so no records in this table

select @OrderNumber = scope_identity()

I call this from another proc that inserts values into my order table:


procedure usp_Insert @OrderNumber int
as

SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRANSACTION


EXEC usp_GetNewOrderNumber @OrderNumber = @OrderNumber output
INSERT INTO [dbo].[tblOrder] ([OrderNumber]) values (@orderNumber) ' inserts value from other stored proc


COMMIT TRANSACTION

END TRY

BEGIN CATCH

if (XACT_STATE() = -1)

ROLLBACK TRANSACTION

else

if (XACT_STATE() = 1)

COMMIT TRANSACTION
END CATCH

Here is the problem. When I run usp_Insert I get the following: Error 266 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

This refers to the usp_GetNewOrderNumber that is called inside the other proc as shown above.

The problem does not happen if I put each statement in usp_Insert in its own try/catch. transaction statements.

Maybe it has something to do with the rollback call in the usp_getneworder.

What do I need to do to get rid of this. problem and still run these within one try/catch trans statement set.

Thanks

View 9 Replies View Related

Error 209 Ambiguous Column Name ...Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK ....

Aug 28, 2006

i'm getting following exception when i try to execute stored procedure.{"Ambiguous column name 'MemberID'.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting"Transaction count after EXECUTE indicates that......" In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN/* Its failing at    MemberID = tTempResult.MemberID   bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID    */   here is my stored procCREATE PROCEDURE dbo.ExportFile   @intMonth INT,   @intYear INT,   @dtFirstDayOfMonth DATETIMEAS  BEGINBEGIN TRANSACTION  /*   I have some logic here that will select rows into temporary table   #TEMPRESULT  */    UPDATE    dbo.RebateInstanceItem   SET     ResubmitCreated = @dtmNewCreated  FROM #TEMPRESULT tTempResult  WHERE     MemberID = tTempResult.MemberID       AND RebateInstanceItem.IsResubmit = 'Y'    AND (RebateInstanceItem.ResubmitCreated = @dtmLastCreated   OR RebateInstanceItem.ResubmitCreated IS NULL)      IF @@ERROR<>0    GOTO ERR_HANDLER // when error it will goto error_handler that will rollback  DROP TABLE #TEMPRESULT  IF @@ERROR<>0   GOTO ERR_HANDLERCOMMIT TRANSACTIONRETURN 0ENDERR_HANDLER: ROLLBACK TRANSACTION RETURN 1GO

View 5 Replies View Related

How To Create A Transaction Commit/Rollback For Oracle DB In SSIS

Oct 8, 2007

I'm able to connect to the Oracle database to insert the data into multiple tables using OLEDB connection via Oracle Provider for OLEDB. However, i wish to create a transaction so that i'm able to rollback all the data in the case where the insertion fails in one of the table. May i know where should i start from?

View 4 Replies View Related

Transact SQL :: Commit And Rollback Transaction Based Upon Results Of A Calculation

Oct 5, 2015

In t-sql 2012, I have the following sql that I would like the following to occur:

1. commit or rollback a transaction based upon the results of a calculation listed below,
2. I would like to have a message appear if the commit was successful or the rollback needed to occur. I basically want a way to be able to tell from messages if a rollback occurred or a commit happened.

DECLARE @TransactionName varchar(20) = 'Transaction1';
        @STARTLOCKERCNT INT = 0, @LOCKDIFCNT INT = 0, @ENDLOCKERCNT INT = 0
DECLARE @lockmap TABLE (lockID  int NOT NULL PRIMARY KEY,
                       schoolID  int NOT NULL,                    
                       UNIQUE(schoolID,lockID)
 )

[Code] ....

Thus can you modify the sql I just listed above so that I meet the goals that I just listed above?

View 5 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

Error On Commit/Rollback

May 12, 2008

Hi

Need a help for the below req.

I have a SP which calls another SP with in a Cursor. The main SP updated two of the DB tables. Based on the data passed from main SP the nested SP updates another two table.

What I want at the end of loop the transaction will be committed. In between if any point it fails means the whole transaction will be rollback.

I am using TRY & CATCH to handle transaction commit & Rollback . But I am getting below error

Error coming in SQL end is:
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

Please advise.

View 1 Replies View Related

TRANSACTIONS In SSIS (error: The ROLLBACK TRANSACTION Request Has No Corresponding BEGIN TRANSACTION.

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

Rollback Transaction .. Error

Nov 20, 1998

DECLARE @A INT, @B INT,@C INT
SELECT @A=2,@B=3

SELECT @C=@A*@B

SELECT @C

BEGIN TRANSACTION A
SELECT @A
SELECT @B
SELECT @C
END TRANSACTION A

THIS CODE PROCDUE the following errors
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'END'.

Can anyone help me telling me what I am doing wrong ?

thanks

Ali

View 1 Replies View Related

Commit And Rollback

Dec 15, 2007

I have a cursor loop through a set of records that looks something like this.

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @iID

WHILE @@FETCH_STATUS = 0

BEGIN

update table 1

update table
......

FETCH NEXT FROM database_cursor
INTO @iID

END

CLOSE database_cursor
DEALLOCATE database_cursor

Is there a way i could put all the UPDATE statements within a transaction. either everything goes or nothing.THnaks

Thanks,

View 3 Replies View Related

Commit/rollback

Dec 21, 2007

when doing an 'update table set field1 = 'N',using sql query analyzer, is the update committed immediately? If it isn't commited can a 'rollback' be executed, and what is the format of the rollback command?

View 4 Replies View Related

Commit And Rollback?

Apr 4, 2007

Hi,



I'm using an SQL Express database over a network, using a C# Express program. So I had to use pure SQL connections and commands instead of using Data Sources (couldn't find a way for it to work). In the program / DB I've got a couple of Master - Detail situations. Something like:



Product:

-----------

productID

(...)



Acessories:

----------------

acessID

(...)



ProductAcess:

--------------------

productID

acessID



So when inserting a new Product, I'll have to first insert the product (with product name, price, and so on) and once I get the product ID from the insert command, I'll insert the ProductAcess rows. I've found a problem in this though. If for some reason the insert of the product is successful, but the insert of ProductAcess fails, I've got a big mess in hands because I'll have a row in Product with no rows in ProductAcess (which shouldn't happen in my program scenario). I could solve this by deleting all rows from the DB which connected in someway to the product that failed to insert, but would be far better and correct if I used a commit command at the end of the insert commands to make sure only the right data would be inserted (saving time and resources). I use this all the time in Oracle databases, but don't know if it is possible in SQL Express... Is it? How? Thanks

View 1 Replies View Related

Does A Transaction Automatically Rollback On Error?

Feb 17, 2006

When I write code for a multiple statements transaction do I need to check 'if @@ERROR > 0 ' after each SELECT, INSERT, DELETE or UPDATE statement so that the 'rollback tran' statement can be given, or SQL server will automatically rollback the transaction and we don't need to check for @ERROR > 0 ?

View 2 Replies View Related

Commit && Rollback Logic In VB.NET

Oct 27, 2006

I have several sets of code that need to delete rows from more than one database at a time. The rows are basically linked without being identified as having a foreign key. This means I issue two deletes. If one fails, especially the second one, there is no way to roll the first delete back.Can someone either point me to some code that enables me to link the deletions, allowing me to insure that both are successful or both do not occur. I cannot identify any fields on the secondary database table as specifically linked to the primary, as the secondary database is a storage medium for images, that may be linked to more than one different table.TIA for any opinions, options, etc.  Tom

View 2 Replies View Related

Help WTrans Rollback Commit

Jun 11, 2002

I first must delete any existing log for my current record.

Then verify that the "exec @res = gmw_updatesynclog..." has not failed and a delete log entry has been successfully written as verified by the gmw_updatesynclog's return of int 16.

If all is well then I incrment my counter and delete the record, here in testing I'm just updateing the activity code to del so I can find them, in live I'll just be deleting them.

--My error
Server: Msg 266, Level 16, State 2, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
-- end my error

...excerpt from my proc and nested cursor....
------------------------------ begin calls to be deleted
if @rectype = 'C' and (len(rtrim(@notes))<20 or @notes is null)
begin transaction

delete gmtlog -- delete any older del log for this recid
where frecid = @recid
and fieldname = 'zzzDel'
and tableid ='"'
if @@error = 0 -- 0 or 1 rows affected...

exec @res = gmw_updatesynclog 'cal',@recid,'MASTER','D'

if @res <> 16 rollback transaction
goto endd --cleanup and fetch next record

if @res = 16 -- increment count_call_dels
set @count_call_dels = @count_call_dels +1
update cal -- update in test delete in live
set actvcode = 'DEL'
where recid = @recid
commit --transaction
------------------------------ end calls to be
... end excerpt...

TIA

JeffP...

View 2 Replies View Related

Need Some Help On Savepoint,Commit And Rollback

Oct 9, 2000

Hi,
I have a procedure of 6500 lines in which i have given a save point at the beginning of the procedure. And am storing the error number in a variable through out the procedure using select of @@error. And at the end if my @error_number is not zero then am rolling back the tranasction else commit the transaction.

Its giving me the error
Msg 266, Level 16, State 2, Line 5437
Transaction count after execute indicates that a commit or rollback
transaction statement is missing. Previous count = 0, Current count = 1.

View 1 Replies View Related

Use Of Begin+Commit/Rollback - Or Not?

Sep 26, 2004

Hi

I have an overnight process that takes transactions from an external system & applies updates to a single db table. Other processes may be active on the db but none touch the tables I'm using. I cannot guarantee the volume of source transactions (may vary from 100s to 100,000s).

My question is should I protect the update within a begin+commit/rollback or should I have a recovery procedure to run in the event of failure (that would delete any rows added to my db table)? (My preference is to do the latter - so I'm really looking for any reasons why I shouldn't take this approach).

Thanks.

View 1 Replies View Related

Triggers And COMMIT/ROLLBACK

Apr 29, 2004

I need a trigger to be fired or a stored procedure to be called on an event of COMMIT or ROLLBACK.

Non of ORACLE, SQL-SERVER, DB2 have this feature.

Please let me know any alternative's

View 2 Replies View Related

To Implement Commit / Rollback

Apr 14, 2008

hi friends,

Iam Executing the sp logic.suppose incase if any problem occurs inbetween execution(NO SPACE,communication failure,log full)
data is getting commited partially insteady of rollbacking entire transaction.

CREATE procedure RBI_Control_sp
as
begin

set nocount on
--Checking the count before truncating
exec fin_ods..count_sp

--Truncating the Table
exec fin_ods..trun_sp

--Data Transfer
exec fin_ods..RBI_Data_Transfer_sp

--Checking the count after Data transfer
exec fin_ods..count_sp

--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp

set nocount off
end

View 3 Replies View Related

Commit And Rollback Problem

Nov 6, 2005

Hi,I still haven't got a decent book on relational databases :-)My stored procedure insert_wire inserts values into two tables (wire andcablewire). The wire_ref (primary key) will be the same for both inserts.However, if for any reason the first insert fails then I would like arollback system to take place. I have tried testing for an error (@@error<> 0) after the 1st transaction but I just get a syntax error. Am I goingdown the right lines here? Any tips appreciated.Thanks, Mary.CREATE procedure insert_wire(in wire_ref VARCHAR(22), in standardVARCHAR(16), in a_color VARCHAR(16), in material VARCHAR(22),in metres INTEGER, in amps FLOAT(3), in volts FLOAT(3), in ni SMALLINT, insome_comment VARCHAR(32))BEGINinsert into cablewirevalues(wire_ref, standard, a_color, material, metres, some_comment);insert into wirevalues(wire_ref, amps, volts, ni);commit;END!

View 1 Replies View Related

When Do Transactions Commit/Rollback?

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

How To Use Commit,begin,rollback Transactions In Asp.net With C#

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

What The Equivalent Command IN SQL SERVER EXPRESS To POSTGRESQL BEGIN ROLLBACK And COMMIT

Jun 27, 2007

Hello,

First of all, this is my first time using SQL SERVER 2005 express, before that i'm using POSTGRESQL database.

I would like to know how what's the equivalent command for "BEGIN","ROLLBACK","COMMIT", these are the POSTGRESQL COMMAND use to start transaction, rollback transaction and commit transaction.

Example when i use this kind of command is . I need to insert data into 3 table. before insert into table1, i issue "begin", start to insert data into table1, if table1 no error, then i proceed to table 2 and table3. if table2 and table3 no error. then issue "commit" to commit the changes. but if any error happen between table1 and table 2 or table 2 and table3, i will issue "rollback" to roll any changes that i make to table1, table2 and table3.

Maybe some one can teach me how to achieve using SQL SERVER 2005 EXPRESS.

Thanks and Regards.

Beh Chun Yit

View 1 Replies View Related

How Do I Make Use Of Begin Transaction And Commit Transaction In SSIS.

Jun 1, 2007

Hi



How do I make use of begin transaction and commit transaction in SSIS.

As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:

Hresult:0x80004005

descriptionyntax error or access violation.



its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.



Thanks,

Prashant

View 3 Replies View Related

Confused By Behavior Of Transaction Log

May 12, 2008

OK, I'm stumped. The 37 GB log file associated with my 22 GB database continues to grow--even though the database file is backed up each week and the database is not experiencing heavy use. I've reviewed numerous log-reated threads on the SQL Server forums, but I'm still confused as to why my log file keeps growing.


The recovery model for this database is set to Full. The database is backed up every Thursday night. Just the transaction log is backed up on all other nights. (I realize that I could simply change to the Simple recovery model and just backup the entire database each night; however, I was hoping that backing up the database once a week and backing up just the log six nights a week would result in much smaller usage of my hosting company's off-site backup device.)


sys.databases reveals the following for this database: log_reuse_wait=0; log_reuse_wait_desc=NOTHING. DBCC SQLPERF (LOGSPACE) returns a 'Log Space Used' of 0.1946281% and a Status of 0.


My hosting center uses Ahsay Online Backup to perform the nightly backups. The backup log indicates that the log file is being backed up each night--actually, the backup log indicates that the night after the weekly database backup, one log file is backed up, the night after that two log files are backed up, etc., right up until the sixth night when six log files are backed up. I'm not sure if this is the way backups are supposed to work or not.


It's my understanding that the log file won't truncate until SQL Server believes that the transactions in the log file are committed and (from what I've been reading) a checkpoint occurs. Is that correct or do I have a wrong understanding? In any case, do I need to truncate the log myself, perhaps as part of regular maintenance (as some of the threads indicate)? If so, how can I be sure that my truncating the file won't cause a loss of any data? Lastly, is it conceivable that the hosting center's online backup tool is somehow interfering with log process?

As I inferred above, I'm not currently doing any regular maintenance on this database, but I need to start; first, however, I'd like to find out why the log file continues to grow. I'm concerned that if I don't resolve this, the drive will fill up.

View 11 Replies View Related

BEGIN TRANSACTION And COMMIT TRANSACTION

Oct 11, 2000

I am executing a stored procedure something like this

Create Procedure TEST
@test1
@test2
AS
BeGIN TRANSACTION ONE
SELECT...
UPDATE..
....
....

....
TRUNCATE
etc and lot of Select,update and delete statements like this
.....
COMMIT TRANSACTION ONE


The Block of code which I have b/w BEGIN TRANSACTION AND COMMIT TRANSACTION ..Will it be rolled back
if it encounters any errors in the SELECT,UPPDATE,DELETE ..statements which I have with the transaction one.
IF not How do I roll back if it encounters any erros b/w the BEGIN TRANSACTION and END TRANSACTION.

Thanks
micky

View 1 Replies View Related

BEGIN TRANSACTION COMMIT TRANSACTION Help

Sep 24, 2007

I had thought that if any statement failed within a BEING TRANS .. COMMIT TRANS block, then all the statements would be rolled back. But I am seeing different behavior (SQL Server 2000 8.00.2039)

For instance, run these statements to set up a test:
--DROP TABLE testTable1
--DROP TABLE testTable2
CREATE TABLE testTable1 (f1 varchar(1))
CREATE TABLE testTable2 (f1 varchar(1))
CREATE UNIQUE INDEX idx_tmptmp ON testTable1 (f1)
insert into testTable1(f1) values ('a')

So table testTable1 has a unique index on it..

Now try to run these statements:

--DELETE FROM testTable2
BEGIN TRANSACTION
insert into testTable1(f1) values ('a')
insert into testTable2(f1) values ('a')
COMMIT TRANSACTION

SELECT * FROM testTable2


..the first insert fails on the unique index.. but the second insert succeeds. Shouldn't the second insert roll back? How can I make two operations atomic?

View 8 Replies View Related

How To Commit The Transaction

Apr 14, 2008

hi friends,

Iam Executing the sp logic.suppose incase if any problem occurs inbetween execution(NO SPACE,communication failure,log full)
data is getting commited partially insteady of rollbacking entire transaction.

CREATE procedure RBI_Control_sp
as
begin

set nocount on
--Checking the count before truncating
exec fin_ods..count_sp

--Truncating the Table
exec fin_ods..trun_sp

--Data Transfer
exec fin_ods..RBI_Data_Transfer_sp

--Checking the count after Data transfer
exec fin_ods..count_sp

--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp

set nocount off
end

View 1 Replies View Related

Commit Transaction Logic

Oct 28, 2007

Hai, in Sql server 2000 database, i want to do the following..
from my UI, i will be updating one table(only 3 columns among the table columns), But the number of records will be around 2000 to 2500.
So every 200th record, i want to commit the transaction, so that i cannot lose the data..
Using query can i achieve this? or do i need to use the simple while loop logic.
Pls advise me

View 6 Replies View Related

Transaction In Packages; When Do They Commit?

Oct 31, 2007

Hello,

I am a SSIS developer from the Netherlands. I have a question about the use of a transaction in a package.
I have a Sequence container with some data flow tasks and some SQL tasks. The TransactionOption for this container is set to Required. The transactionOption for the tasks in the container are set to Supported. After this container I have put some other tasks (like a script task and an send mail task). This last 2 tasks have a TransactionOption set to Not Supported.
I have done this this way because if something goes wrong in the Sequence Container everthing in this container will be rollbacked. This work fine! But if one of the last 2 task goes wrong (the tasks after the container) everthing in the container is also rollbacked. I don't know why this is happening. Can you help me with this?

Thanks! Greetings, Pieter.

View 4 Replies View Related

BEGIN And COMMIT Transaction

Feb 28, 2008

Can help me?
I have to insert BEGIN and COMMIT Transaction in my stored procedure (call to a trigger)





Code Snippet

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE [dbo].[DETTAGLIO_TURNI_DIFENSORI]

(
@tipo_albo VARCHAR(50),
@data_inizio DATETIME,
@data_fine DATETIME,
@descrizione VARCHAR(50),
@idturno INT,
@n_dif INT
)

AS


-- DICHIARAZIONE VARIABILI...
DECLARE @dett_idturno as INT
DECLARE @totale_giorni as INT
DECLARE @tipo_albo_A as VARCHAR(9)
DECLARE @tipo_albo_B as VARCHAR(9)
DECLARE @data_odierna as DATETIME
DECLARE @tot_avvocati as INT
DECLARE @avv_giorno as INT
DECLARE @avv_giornoA as INT
DECLARE @avv_giornoB as INT
DECLARE @conta_giorni as INT
DECLARE @incremento_giorni as INT
DECLARE @incr_dif_giorno as INT
DECLARE @nuovo_idturno as INT
DECLARE @idalboturno as INT
DECLARE @old_turni as INT
DECLARE @tot_giorniA as INT
DECLARE @tot_giorniB as INT
DECLARE @conta_giorni_incremento as INT
DECLARE @conta_avvocati as INT
DECLARE @avv as INT
DECLARE @altri_turni AS INT
DECLARE @neg_giorni_incremento as int
-------------------------------------------------------------------------------------------------------

-- SET VARIABILI PER EVITARE PROBLEMI CON NULL o 0
SET @totale_giorni = 0
SET @tot_avvocati = 0
SET @avv_giorno = 0
SET @conta_giorni = 0
SET @incremento_giorni = 0
SET @incr_dif_giorno = 0
SET @idalboturno = 0
SET @old_turni = 0
--------------------------------------------------------------------------------------------------------

SET @nuovo_idturno = @idturno

BEGIN TRAN

-- conta i giorni
SELECT @totale_giorni = (DATEDIFF(dd, @data_inizio, @data_fine))+1

-- select da vista avvocati per tipo difensori selezionato.. (controllare anche data_fine_iscrizione?)

create table #Tmp
(
[ID_anagrafica] varchar(50)
)

IF @tipo_albo = 'DIFO'
BEGIN
SET @tipo_albo_A = 'DIFM'
SET @tipo_albo_B = 'CPT'

-- select per contare..

SELECT @tot_avvocati = COUNT(*)
FROM VALBO_ISCRIZIONE_DIF_ORDINARI

SELECT @tot_avvocati As tot_avvocati

-- creo tabella temporanea..
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_ORDINARI ORDER BY ID_ANAGRAFICA

END

IF @tipo_albo = 'DIFM'
BEGIN
SET @tipo_albo_A = 'DIFO'
SET @tipo_albo_B = 'CPT'

-- select per contare..
SELECT @tot_avvocati = COUNT(*)
FROM VALBO_ISCRIZIONE_DIF_MINORI

SELECT @tot_avvocati as tot_avvocati

-- creo tabella temporanea..
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_MINORI ORDER BY ID_ANAGRAFICA
END

IF @tipo_albo = 'CPT'
BEGIN
SET @tipo_albo_A = 'DIFM'
SET @tipo_albo_B = 'DIFO'

-- select per contare..
SELECT @tot_avvocati = COUNT(*)
FROM VALBO_ISCRIZIONE_DIF_CPT

SELECT @tot_avvocati as tot_avvocati

INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_CPT ORDER BY ID_ANAGRAFICA
END

DECLARE @idanagrafica varchar(50)

-- GIORNI PER AVVOCATO
-- tot_giorni * n_dif_minimo / tot_avvocati = tot_giorni_avvocato (con intero successivo)
SET @incremento_giorni = ((@totale_giorni * @n_dif)/@tot_avvocati)
-- controllo se c'è resto..
IF ((@totale_giorni * @n_dif)%@tot_avvocati) <> 0
BEGIN
SET @incremento_giorni = @incremento_giorni + 1
END

-- ogni avvocato deve essere difensore per almeno 2 giorni di seguito..
-- quindi se l'incremento è minore di 2 deve essere uguale a 2
IF @incremento_giorni < '2'
BEGIN
SET @incremento_giorni = '2'
END

-- AVVOCATI AL GIORNO
-- numero variabile.. prendere in considerazione il primo intero e l'intero successivo..
SET @avv_giorno = (@incremento_giorni * @tot_avvocati)/@totale_giorni
SET @avv_giornoB = @avv_giorno

-- controllo il resto della divisione.. se <> 0 @avv_giornoB = @avv_giorno + 1..
-- altrimenti i due valori sono uguali..

IF ((@incremento_giorni * @tot_avvocati)%@totale_giorni) <> 0
BEGIN
SET @avv_giornoA = @avv_giorno + 1
END
ELSE
BEGIN
set @avv_giornoA = @avv_giorno
END

-- conteggi giorni totali difensori...
-- giorni con N difensori
SET @tot_giorniB = ((@avv_giornoB * @incremento_giorni)/@totale_giorni)
-- giorni con M difensori
SET @tot_giorniA = @totale_giorni - @tot_giorniB

declare @totale as int
declare @conta_inseriti as int
set @conta_inseriti = 0
set @totale = (@avv_giornoA * @tot_giorniA) + (@avv_giornoB * @tot_giorniB)

-- ciclo per totale dei giorni
SET @conta_giorni = 1
SET @conta_giorni_incremento = 0
SET @conta_avvocati = 0
WHILE @conta_giorni <= @totale_giorni BEGIN

-- ogni giorno @avv deve essere ZERO
SET @avv = 0

IF @conta_giorni <= @tot_giorniA
BEGIN
SET @avv_giorno = @avv_giornoA
END
ELSE
BEGIN
SET @avv_giorno = @avv_giornoB
END

-- ciclo per ogni giorno per totale di avvocati/giorno
SET @incr_dif_giorno = 0

WHILE (@incr_dif_giorno < @avv_giorno) AND EXISTS(SELECT TOP 1 ID_anagrafica FROM #Tmp) BEGIN

SET @conta_avvocati = @conta_avvocati + 1

SET @data_odierna = DATEADD(dd, (@conta_giorni-1), @data_inizio)

SET @neg_giorni_incremento = -1 * @conta_giorni_incremento

SET @old_turni = 0
SET @altri_turni = 0

SELECT TOP 1 @idanagrafica = ID_anagrafica from #Tmp
DELETE #Tmp WHERE ID_anagrafica = @idanagrafica

-- query che controlla i turni già assegnati per altre liste...

SELECT @old_turni = COUNT(*)
FROM Albo_Turno_Dettaglio CROSS JOIN
ALBO_TURNO
WHERE
Albo_Turno_Dettaglio.idalbo = @idanagrafica
AND
(
ALBO_TURNO.Tipo = @tipo_albo_A --- probabile problema con trigger
OR
ALBO_TURNO.Tipo = @tipo_albo_B
)
AND
(
Albo_Turno_Dettaglio.Data
BETWEEN
DATEADD(dd, (@neg_giorni_incremento + 1), @data_odierna)
AND
DATEADD(dd, (@incremento_giorni-@conta_giorni_incremento), @data_odierna)
)

SELECT @old_turni AS old_turni

-- (nel caso in cui il ciclo ricominci..) controllare che questo avvocato non abbia
-- già un set di giorni in questo turno...

-- passato il primo controllo.. deve passare anche questo..

SELECT @altri_turni = COUNT(*)
FROM Albo_Turno_Dettaglio
WHERE
Albo_Turno_Dettaglio.idalbo = @idanagrafica
AND
Albo_Turno_Dettaglio.idturno = @nuovo_idturno

SELECT @altri_turni AS altri_turni

IF @old_turni = NULL
BEGIN
SET @old_turni = 0
END

IF @ALTRI_TURNI = NULL
BEGIN
SET @ALTRI_TURNI = 0
END

IF (@old_turni = 0 AND (@altri_turni = 0 OR @altri_turni < (@incremento_giorni)))
BEGIN
-- se non ci sono turni sovraposti assegna il turno all'avvocato x N giorni (incremento_giorni)
-- seleziono il dettaglio con idturno max per aumentare di uno...
SET @idalboturno = 1
SET @dett_idturno = 1
SELECT @dett_idturno = MAX(idalboturno)
FROM Albo_Turno_Dettaglio

if (@dett_idturno) = null
begin
set @dett_idturno = 0
end

SET @idalboturno = @dett_idturno + 1

BEGIN TRAN

INSERT Albo_Turno_Dettaglio
(
idalboturno,
idalbo,
idturno,
data
)
VALUES
(
@idalboturno,
@idanagrafica, -- da cursore
@nuovo_idturno,
@data_odierna -- problemi inserimento data??
)

COMMIT
-- valorizza il numero degli avvocati del giorno + 1
SET @incr_dif_giorno = @incr_dif_giorno + 1
END
ELSE
BEGIN
-- non incrementare la variabile...
SET @incr_dif_giorno = @incr_dif_giorno
END

END -- END WHILE AVVOCATI PER GIORNI

SET @conta_giorni_incremento = (@conta_giorni_incremento + 1)
IF @conta_giorni_incremento < @incremento_giorni
BEGIN
--DEVO RIPOPOLARE LA TABELLA PERCHE' OGNI AVVOCATO DEVE AVERE X GIORNI..
-- cancello tutti i record e poi inserisco di nuovo..
DELETE FROM #Tmp
IF @tipo_albo = 'DIFO'
BEGIN

INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_ORDINARI ORDER BY ID_ANAGRAFICA
END

IF @tipo_albo = 'DIFM'
BEGIN

INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_MINORI ORDER BY ID_ANAGRAFICA
END

IF @tipo_albo = 'CPT'
BEGIN

INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_CPT ORDER BY ID_ANAGRAFICA
END

END
ELSE
BEGIN
set @conta_giorni_incremento = 0
END

SET @conta_giorni = @conta_giorni + 1
END

DROP TABLE #TMP

COMMIT TRAN


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

View 9 Replies View Related

T-SQL (SS2K8) :: Add Record And Commit Transaction

Oct 6, 2014

I am simply inserting records in a table, But if record already present then i dont want add that record and display resultset as -1.If record is not present then i want to add that record and commit transaction. I am doing like below : below is sample, I am considering only two columns.

CREATE PROCEDURE [dbo].[Sproc__Save_Teacher_Details]
-- Add the parameters for the stored procedure here
@FacilitiDetailID int
,@SpecialityType varchar(50)
AS
BEGIN

[code]....

above code is working fine for new recordset but if record exists then it is giving two resultsets -1 and 1, but i want to display only -1.

View 4 Replies View Related

Transaction Commit Doesn't Work

Sep 27, 2007

Could somebody also help me on the issue: I am using OLE DB to setup database with SQL CE 3.1, what i need is to flush the buffer to database file before program exit, I am using DBPROPVAL_SSCE_TCM_FLUSH and Transaction commit, but they don't work for me, My code is here:

1. the code to do transaction commit


// Access Transaction Interface

hr = pIDBCreateCommand->QueryInterface(IID_ITransactionLocal, (void **) &pTransLocal);

ULONG lTransLevel;

// Start the transaction

hr = pTransLocal->StartTransaction( ISOLATIONLEVEL_READCOMMITTED, 0, NULL, &lTransLevel );

// Execute the command with paramters.

hr = pICommandText->Execute(NULL, IID_NULL, &params, &cRowsAffected, NULL);

if( FAILED( hr ) )

{

goto Exit;

}

// commit

hr = pTransLocal->Commit( FALSE, XACTTC_SYNC, 0 );

pTransLocal->Release();



2. the code to set up property:


void LogDatabase::SetSessionProperty( IDBCreateSession *pISession )

{

DBPROPSET dbpropset[1]; // Property Set used to initialize provider

DBPROP sessionProps[1];

VariantInit(&sessionProps[0].vValue);

ISessionProperties *pISessionProperties = NULL;

//************** We Initial a session properties here *************

// Initialize a session object.

HRESULT hr = pISession->CreateSession(NULL, IID_ISessionProperties,

(IUnknown**) &pISessionProperties);

// Initialize the property to change commit mode.

sessionProps[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE;

sessionProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;

sessionProps[0].vValue.vt = VT_I4;

sessionProps[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH;



// Initialize the session property set.

dbpropset[0].guidPropertySet = DBPROPSET_SSCE_SESSION;

dbpropset[0].rgProperties = sessionProps;

dbpropset[0].cProperties = sizeof(sessionProps)/sizeof(sessionProps[0]);

// Set the session property.

hr = pISessionProperties->SetProperties(sizeof(dbpropset)/sizeof(dbpropset[0]),

dbpropset);

VariantClear(&sessionProps[0].vValue);

}

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved