I've been having some problems w/ the following query in a stored procedure (see attached). Worse is that
I need to add this SET IMPLICIT_TRANSACTIONS ON before the sp to make it run, even if the session in query analyzer options already has it checked. Although it works now, I don't understand why I needed to add the SET IMPLICIT_TRANSACTIONS ON explicitly and why I needed 2 commit statements.
SET IMPLICIT_TRANSACTIONS ON
Create proc myproc
Set @sql = /* insert mytable in mssql
select some fields from some openquery stuff from mysql*/
Commit Tran -- this one will not commit entirely
Commit Tran -- this one will finish committing