set implicit_transactions off - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

set implicit_transactions off

Author

Topic

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-08-18 : 13:32:16

Hello,

I have a stored procedure which can not finish the entire transaction. To monitor the process, I open a trace with SQL Server Profiler.

I found that there is

set implicit_transactions off

Should I add a statement somewhere in the stored procedure such as

set implicit_transactions on

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-18 : 13:39:33

no. see here: http://msdn.microsoft.com/en-us/library/aa259220(SQL.80).aspx (yes, it's for 2000, but includes info not covered in 2005 BOL)

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-08-18 : 13:44:31

The error message is like,


System.InvalidOperationException: ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.
   at System.Data.Odbc.OdbcConnection.SetStateExecuting(String method, OdbcTransaction transaction)
   at System.Data.Odbc.OdbcCommand.ValidateConnectionAndTransaction(String method)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()

Help needed.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-18 : 14:55:44

can we see the code in the SP?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources