Board index » delphi » Delphi ODBC API-SQL 7.0 Store Procedure Problem

Delphi ODBC API-SQL 7.0 Store Procedure Problem

I have a very weir problem with the store procedure in SQL Server 7.0.
When I'm running a large store procedure that call some other store
procedure suddenly it stop running and return without any error. I've
made some research and it looks like it stop after  x number of line
executed. Any one has an idea what could be happening? I'm a little bit
desperate with this.

I'm using delphi and API ODBCl but the same happend with ODBC Express
5.05.

Thanks in advance.

Alberto Ibarra

 

Re:Delphi ODBC API-SQL 7.0 Store Procedure Problem


Try adding "set nocount on" to the beginning of all your stored procedures.
Also, make sure that there is no possibility that you are returning multiple
recordsets, Delphi cannot handle that.

Keith

Quote
Alberto Ibarra <aeiba...@sprynet.com> wrote in message

news:37FF5970.34D28D86@sprynet.com...
Quote
> I have a very weir problem with the store procedure in SQL Server 7.0.
> When I'm running a large store procedure that call some other store
> procedure suddenly it stop running and return without any error. I've
> made some research and it looks like it stop after  x number of line
> executed. Any one has an idea what could be happening? I'm a little bit
> desperate with this.

> I'm using delphi and API ODBCl but the same happend with ODBC Express
> 5.05.

> Thanks in advance.

> Alberto Ibarra

Re:Delphi ODBC API-SQL 7.0 Store Procedure Problem


Wow, was I in the same boat...now my headache is somewhat releived :).  I can
execute my sproc in a TQuery, but according to this, there is no way to retrieve
the value(s) in the last recordset when mutiple sets are returned?
Quote
Keith Rome wrote:
> Try adding "set nocount on" to the beginning of all your stored procedures.
> Also, make sure that there is no possibility that you are returning multiple
> recordsets, Delphi cannot handle that.

> Keith

Re:Delphi ODBC API-SQL 7.0 Store Procedure Problem


It's possible to do it but you must give up with the BDE and use "pure" OBDC
procedures.

If you do so you can use SQLMoreResults which allows you to switch from a
result set to the next one (discarding the old one). That function must be
used with care as sometimes you may leave your buffers (hStmt) not
completely emptied but using it you can process multiple result sets... But,
again, that means youmust give up with BDE. I don't know if ODBCExpress can
handle multiple result sets and if it can't you then must code using
straight ODBC APIs.

These weren't available in D3, I wrote a .PAS file to encapsulate them
(nearly all of them). If you need that file drop me a mail...

--
Remove ".net" from my email address to contact me.

Quote
Tom Mitchell wrote in message <3804F619.818EA...@geocities.com>...
>Wow, was I in the same boat...now my headache is somewhat releived :).  I
can
>execute my sproc in a TQuery, but according to this, there is no way to
retrieve
>the value(s) in the last recordset when mutiple sets are returned?

>Keith Rome wrote:

>> Try adding "set nocount on" to the beginning of all your stored
procedures.
>> Also, make sure that there is no possibility that you are returning
multiple
>> recordsets, Delphi cannot handle that.

>> Keith

Other Threads