I open a new window in SSMS and run this:
SET ANSI_DEFAULTS ON
GO
CREATE PROCEDURE [dbo].[zzz_test2]
(
@a int
)
AS
SET NOCOUNT ON
SET @a=1
RETURN 0
GO
and then close the window, which results in this warning:
There are uncommitted transactions. Do you wish to commit these before closing the window?
what is going on??
when I open a new SSMS window and run this:
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[zzz_test2]
(
@a int
)
AS
SET NOCOUNT ON
SET @a=1
RETURN 0
GO
and close the window, I get no warning.
An Uncommitted transaction simply means it will not be remitted for you, if we file your returns, and it will not reflect in your reports. Typically uncommitted transactions are used for unfinished transactions, such as quotes or sales orders.
Two things to check. One is that you might have the "implicit transaction" setting turned on, which means EVERYTHING it wrapped in a transaction. Check the properties of the server/database. Otherwise, you can use the DBCC OPENTRAN function to find any uncommitted transactions....
Uncommitted transactions are not remitted for you (if we file your returns) and are not reflected in your reports.
Uncommitted data is written into the table(s) in question, it is simply not marked as committed until the transaction commits. If the transaction is rolled back the data will be overwritten the next time a write occurs that needs the affected page.
As described in the documentation for ANSI_DEFAULTS
When enabled (ON), this option enables the following ISO settings:
SET ANSI_NULLS
SET CURSOR_CLOSE_ON_COMMIT
SET ANSI_NULL_DFLT_ON
SET IMPLICIT_TRANSACTIONS
SET ANSI_PADDING
SET QUOTED_IDENTIFIER
SET ANSI_WARNINGS
As documented here when IMPLICIT_TRANSACTIONS
is on.
Transactions that are automatically opened as the result of this setting being ON must be explicitly committed or rolled back by the user at the end of the transaction
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With