Commit Transaction Gets Deleted - Unable To Save SP
Jan 24, 2006
I've re-written a stored procedure and when I post the following code
into the existing SP in EM, is saves OK. However, when I re-edit the
SP, the last line 'Commit Transaction' has been removed.
I cannot save the remainder of the SP as it throws error 208 (Invalid
Object name #Max) about two of the temp tables I use when I post the
entire script. It shows in a message box with the header : 'Microsoft
SQL-DMO(ODBC SQLState:42S02)
I haven't posted the full SP nor the structure as it's quite large
(2000 lines), so hopefully I have given enough detail, but my questions
are :
Why does it now have problems with (temp) #Tables ? The use of these
has not changed. All I have done is wrap the script into various
transactions as this helps a lot for performance and tweaked a few
parts later in the SP again for performance.
Also, why does the line get removed once I save the SP ?
If I run this in QA, I get the same errors, so I suspect it's my
script, but don't know where I'm going wrong.
SQL2000 (Need to upgrade the service pack as recently installed on my
PC, so this may help)
Thanks in advance
Ryan
CREATE PROCEDURE [dbo].[JAG_Extract] (@ExtractYear INTEGER,
@ExtractMonth INTEGER) AS
BEGIN TRANSACTION
SELECT 0 AS MaxYear, 0 AS MaxMonth INTO #Max
UPDATE #Max SET MaxYear = @ExtractYear
UPDATE #Max SET MaxMonth = @ExtractMonth
FROM
DEALER_NAW DN WITH (NOLOCK)
INNER JOIN DEALER_SOURCE_DATA SD WITH (NOLOCK)
ON DN.DEALER_CODE = SD.DEALER_CODE
INNER JOIN DEALER_SOURCE_DATA_VALUES_Current DV WITH (NOLOCK)
ON SD.DEALER_SOURCE_DATA_ID = DV.DEALER_SOURCE_DATA_ID
AND SD.STATUS < 4096
INNER JOIN DEALER_FIXED_GROUP_RELATION GR WITH (NOLOCK)
ON DN.DEALER_CODE = GR.DEALER_CODE
AND GR.FIXED_GROUP_ID IN
(11,12,13,14,15,16,17,18,23,42,43,44,45,46,47,48,4 9,50,
51,52,53,54,55,56,57,58,59,60,61,106,109,110,111,1 12,
113,114,115,130,131,132,133,134,135,136,137)
GO
COMMIT TRANSACTION
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.
Hi all I would like to know if its possible to "Save" records when they get deleted. For example: I have a table, tblUsers, with coulmns, UserID, Name, Surname, etc... In VWD I've created a GridView which shows everything on a webpage. I've also added a confirm return('Are you sure you want to delete the user?') option in OnClientClick field. What i want to achieve is, have some sort of log file, or log table if you want to call it that, of which users has been deleted by the end user. So, in later stages, i can see who deleted who, when, where, etc... - by building a report or view. All this should go to a seperate database or seperate table, it doesnt really matter. My delete query:DELETE FROM [tblUsers] WHERE [UserID] = @UserID
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.
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?
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
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
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?
-- 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
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
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.
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:
Our testing server experienced a timeout exception when execute System.Data.SqlClient.SqlTransaction.Commit() in SQL Server 2000 thru .Net Framework 1.1. And this happened at 2007-02-13 18:07:05,954. It was strange to us that all the insert statement can be executed without error within the transaction but the commit operation fails. Moreover, after about 40 minutes, we found that the transaction log of this database is full.
Here is the exception and SQL Server 2000 Error Log: Exception Stack Trace: DateTime: 2007-02-13 18:07:05,954 Req Id: bccdae08-cc47-4f85-8f48-5f0b9dbbbf88 Exception: MyDatabaseException Detail: MyDatabaseException: Index #0 Server: MySQLServer Source: .Net SqlClient Data Provider Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. LineNumber: 0 Procedure: ConnectionRead (recv()). State: 0 Error Number: -2 ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, TdsParserState state) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected) at System.Data.SqlClient.TdsParser.ReadBuffer() at System.Data.SqlClient.TdsParser.ReadByte() at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler, SqlDataReader dataStream) at System.Data.SqlClient.SqlInternalConnection.ExecuteTransaction(String sqlBatch, String method) at System.Data.SqlClient.SqlConnection.ExecuteTransaction(String sqlBatch, String method) at System.Data.SqlClient.SqlTransaction.Commit()
SQL Server Error Log:
2007-02-13 13:02:23.72 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944769, first LSN: 9434:22326:1, last LSN: 9434:22360:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:MSSQLBackupMyDatabaseName.bak'}). 2007-02-13 15:50:52.40 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944970, first LSN: 9442:38096:1, last LSN: 9443:748:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'Legato#4f96edfd-7fdb-4cd1-a740-3fe9a54d66c6'}). 2007-02-13 18:48:51.42 spid66 Error: 9002, Severity: 17, State: 62007-02-13 18:48:51.42 spid66 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space.. 2007-02-13 18:52:40.61 spid58 Error: 9002, Severity: 17, State: 6 2007-02-13 18:52:40.61 spid58 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space.. 2007-02-13 18:53:22.69 spid61 Error: 9002, Severity: 17, State: 6 2007-02-13 18:53:22.69 spid61 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space.. 2007-02-13 18:54:01.11 spid57 Error: 9002, Severity: 17, State: 6 2007-02-13 18:54:01.11 spid57 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
Are these 2 things related? Would a full transaction log cause a commit operation fail?
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 ON2 set QUOTED_IDENTIFIER ON 3 go4 5 ALTER PROCEDURE [dbo].[AddUserHaveTag] 6 (7 @UserHaveID int,8 @Tag varchar(24),9 @UserHaveTagExists bit OUTPUT 10 )11 AS12 SET NOCOUNT OFF13 DECLARE @ErrorCode int14 DECLARE @TagID int15 DECLARE @TagExists bit16 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 ERROR24 25 -- Check for existing record, otherwise insert 26 IF EXISTS (SELECT 1 FROM UserHaveTags WHERE UserHaveID = @UserHaveID AND TagID = @TagID)27 BEGIN28 SET @UserHaveTagExists = 129 RETURN 030 END31 ELSE32 BEGIN33 INSERT INTO UserHaveTags (UserHaveID, TagID) VALUES (@UserHaveID, @TagID)34 SET @UserHaveTagExists = 035 END 36 37 -- Check for errors 38 IF @ErrorCode <> 0 GOTO ERROR39 40 COMMIT TRAN 41 42 ERROR:43 IF (@ErrorCode <> 0)44 BEGIN45 PRINT 'Unexpected error occurred!' 46 ROLLBACK TRAN47 END Here is the AddTag sproc:1 set ANSI_NULLS ON2 set QUOTED_IDENTIFIER ON 3 go4 5 ALTER PROCEDURE [dbo].[AddTag] 6 (7 @Tag varchar(24),8 @TagID int OUTPUT,9 @TagExists bit OUTPUT 10 )11 AS12 SET NOCOUNT OFF13 14 IF EXISTS (SELECT 1 FROM Tags WHERE Tag = @Tag)15 BEGIN16 SELECT @TagID = TagID FROM Tags WHERE Tag = @Tag17 SET @TagExists = 118 RETURN 019 END20 ELSE21 BEGIN22 INSERT INTO Tags (Tag) VALUES (@Tag)23 SET @TagID = SCOPE_IDENTITY()24 SET @TagExists = 025 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
SQL Server 2000, C#, ASP.NET and ADO.NET. I have searched for 3 days trying to figure out why my ADO.NET Transaction executes my stored procedures and runs the .COMMIT() on the server, figured this out by using the SQL Profiler, but the data doesn't show up in the database tables and I recieve no error from SQL or ASP.NET. What gives???? Anyone else ever heard of such a thing. Please help, before I loose my sanity.
Are there any tips/techniques/issues when doing a begin tran and commit between a sql 2000 server/db and a sql 2005 server/db. Should you still use "set xact_abort on"? This will be a recordset of about 1-2000
I have a update trigger. In this trigger I need to insert few records in 3 tables. If error comes in any of these inserts then previous inserts to get committed. This trigger was written in Sybase and it was possible to create transaction and commit the transactions.
I have a page that runs a transaction correctly after a button click. I want to allow someone to click a button that rolls back the transaction, after the transaction runs on the first button click. I can also successfully roll back within the first button click. I'm getting a NullReference error when trying to access SqlTransaction.Rollback() outside the button click. If SqlTransaction.Commit() completes without error can SqlTransaction.Rollback() be called after? I tried making 'trans' a more global variable and it still gave me the error. Button Click 1: Dim trans As SqlTransaction trans = connection.BeginTransaction() try 'run SQL Statement trans.Commit() Catch e As Exception trans.Rollback() throw e end try
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.
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?
I have this sql stored procedure in SQL Server 2012:
ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes] @newBatches as dbo.CreateBatchList READONLY , @productId int , @cLevelRatio int , @nLevelRatio int AS set nocount on;
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?
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
I am getting an exception. And i don't know whether this exception is from the C# coding or SQL SERVER."Cannot roll back InsertRxs. No transaction or savepoint of that name was found.Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back."
I'm all of a sudden getting this error on a Stored Procedure that has not been touched since it was created.
Msg 266, Level 16, State 2, Procedure usp_ArchivexactControlPoint, Line 0 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
CREATE PROCEDURE [dbo].[usp_ArchivexactControlPoint] AS DECLARE @TableName VARCHAR (50)
I have about 100 DTS packages and they were created by a user who has left the company. Now I( the new user)am trying to open the packages from another client machine and trying to save them back. I am connecting to SQL server where the packages are stored using NT authentication(domain account). When I try to save the package, I get the following error
Ole DB, Only the owner of the DTS package "packagename", or a member of the sysadmin role can create new versions of it".
What I have tried is that I can save the same package with a new name without being a member of the sysadmin role but I cannot save the package with the same name. ANy help will be greatly appreciated.
A mistake was made and "old" transaction logs (and old backups) weredeleted. It appears that a long living transaction was living in one ofthose logs. Now the EP shows (no items). The DBs themselves are stillfunctioning OK, just the EP shell that's not working.What's the proper resolution?Thank you in advance,FBCK
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tbl_position_tbl_departments". The conflict occurred in database "MyProjectDB", table "dbo.tbl_departments", column 'ID'. All of my tables have an ID field that is a primary key with Allow Null unchecked.
Hai, Im using Sql server 2000. Im writing appliction to do Batch Update, meaning updating more than one row in single database call.. In my Requirment i may get around 2000 record to update... so i want to us Savepoint after every 200 records are updated to the database.. Can i use save point for my requirment...can someone clarify
I've got log shipping set up, and everything seems to be working fine, but the log files are not being deleted from the primary server despite configuring log shipping to retain them for 3 days. Â I see no errors concerning the log shipping, but did not configure a monitor. What process is responsible for deleting the older log backups, and how can I look for errors. Â I could simply set up a jog to delete the older files, but that will only mask the issue.
