To know if there is pending commit

SQL 2000

Consider this sp  ( please ignore syntax / coding error as this is just an example/pattern)

alter proc usp_xyz(@company_code varchar(2),      @inv_no varchar(8))
as
BEGIN TRAN mytran
.... some insert / update / delete in temp tables (@tables not #tables)

insert Mytable (My columns )
select My columns
if(@@error <> 0) Goto Failed

COMMIT TRAN mytran
RETURN 0

Failed:
  ROLLBACK TRAN mytran
  RETURN 1

I am executing this sp from a front end and I know a primary key conflict occured. The error message returned is

" Transaction count after execute indicates that a commit or rollback transaction statement is missing , previous count=1, current count =2"

I changed the proc to  --addition of a raiserror

alter proc usp_xyz(@company_code varchar(2),      @inv_no varchar(8))
as
BEGIN TRAN mytran

.... some insert / update / delete in temp tables (@tables not #tables)

declare @kount int
select @kount = (select count(vm_no) from mytable where (vm_no = @inv_no)
if @kount > 1
      begin
            RAISERROR ('Transaction already exists.', 16, 1)
            Goto Failed
      end

insert Mytable (My columns )
select My columns
if(@@error <> 0) Goto Failed

COMMIT TRAN mytran
RETURN 0

Failed:
  ROLLBACK TRAN mytran
  RETURN 1

I executed the sp from front end making sure that @kount > 1 and hoped this message to be returned by sql
            'Transaction already exists.'
as I wrote in raiserror but the error message returned again was "transaction count after....."

My question is
1) How can I proper the messages.
2) Should I write "goto failed" after raiserror statement.

Point to be noted:
When @kount > 1 then in my proc actually no real insert/delete/update statement
was executed and it went to area Failed.








MehramAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Hello Mehram,

Seems  like you have some pending transactions, do one thing, run
SELECT @@TRANCOUNT  
which returns the transcations count and ROLLBACK that many times manually
again run the sp check for the TranCount


Aneesh R
0
MehramAuthor Commented:
Hi Aneesh R
I did not understand
<<ROLLBACK that many times MANUALLY >>

I wan to do something automatically at this point of code

Failed:
  ROLLBACK TRAN mytran
  RETURN 1




0
MehramAuthor Commented:
Oh , I am committing more than once and never notice.

Please consider code written in my front end

ls_sql = 'exec accsrv.shma_console.dbo.usp_GenBCA_RLZ ' + "'" + ls_unit_code + "'"+',' + "'" +ls_inv_no+ "'"
execute immediate :ls_sql using sqlca;

IF sqlca.SqlCode <> 0  and sqlca.sqlcode <> 100 THEN
  Rollback;
      else
      commit;
end if                              
                  
U can see, it's a matter of taking decision, where to make final commit/rollback.
On commit/rollback is in my proc.
Another is in my front end.
Whether there is error or not. commit/rollback is already being executed in proc

I am confused, and thinkin of asking this question in relative area that is power builder.

I will be available tomorrow.
      
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Aneesh RetnakaranDatabase AdministratorCommented:
I dont really find any issues with you sql code, it seems to be correct
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MehramAuthor Commented:
Can I attract attention of Powerbuilder experts through this question.
0
MehramAuthor Commented:
I have raised the question in powerbuilder section to know if I am doing mistake in Powerbuilder scripting.

Thanks & regards.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.