trouble calling sql server stored procedure

[miguelleeuwe]

Hello,

using pb 11.5
sql server 2008

call a stored procedure from powerbuilder that does a BULK INSERT and everything goes fine. I can repeat the operation thousands of times and no problem.
The problem is when I quit the application and go back to execute exactly the same procedure, it fails, saying that the transaction can not be 'confirmed' and does not permit 'write' operations (traduced from spanish, sorry). And I should REVERT.
I've tried tons of stuff and the only diference y seem to find is - when monitoring the sql - there's a diference in the call. When it fails there seems to be a call to
"sp_procedure_params_rowset N'[name of my procedure]',1,N'dbo',NULL" which I don't see when its working fine. .....

the way to make my powerbuilder call work again, is to previously call the procedure straight from the "sql sever manager" tool.

¿any guesses?
I'm thinking it might have something to do with having to specify "BATCH-size" to my BULK INSERT ??? but why does it work 1000's of times untill I leave the application and get back in ....


Quite Desperate. this was a 16 hours workday, not counting the time I already spent at home..

Thanks in Advance
Miguel

regards,
Miguel L.

 

[mbalent]

There are many google hits on 'sp_procedure_params_rowset' which appears to be called from an API.

My guess is something needs to know the metadata your proc is using the first time it is called?

Matt


"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[miguelleeuwe]

Thank you Matt,

this first time is when everything goes well.

I solved the problem (one of the options I tried first yesterday, but didn't work because of having the transaction log full (I guess....))

I create a new transaction object, connect with the same parameters as sqlca and use 'autocommit=true', that was the whole trouble. Everything is working fine now.


Thanks again
Miguel

regards,
Miguel L.