goosvanbeek
asked on
Sybase 12.5 ASE stored procedure doesn't complete when executed by ADO
Hi
I have a stored procedure that runs fine while executing it from SQL Advantage.
The procedure runs a 'select count' on a (part of a) large table and stores the result in a #tmp table.
The results from the #tmp table are row by row compared to a second table (by a cursor); the result of each cursor fetch is passed to a second stored procedure which updates the second table.
The problem occurs when this procedure is executed by an ADO procedure in Visual Basic. The first part of the procedure is executed, but the part with the cursor seems to be passed over..
No problems with other procedures and ADO
I'm using ADO 2.7, Sybase OLEDB provider version 2.7.0.0.6 and Sybase 12.5.03 ASE
--- Goos van Beek.
I have a stored procedure that runs fine while executing it from SQL Advantage.
The procedure runs a 'select count' on a (part of a) large table and stores the result in a #tmp table.
The results from the #tmp table are row by row compared to a second table (by a cursor); the result of each cursor fetch is passed to a second stored procedure which updates the second table.
The problem occurs when this procedure is executed by an ADO procedure in Visual Basic. The first part of the procedure is executed, but the part with the cursor seems to be passed over..
No problems with other procedures and ADO
I'm using ADO 2.7, Sybase OLEDB provider version 2.7.0.0.6 and Sybase 12.5.03 ASE
--- Goos van Beek.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, solved it for so far with a (VB) shell command which runs SQL Advantage as (temporary) service while executing the procedure. Works fine as long as the client has SQL Advantage installed.
Shell "sqladv.exe -Uuser -Ppassword -SserverL" & " -Ddatabase" & _
" -istoredProcedure.sql" & " -G", vbHide
But I still want to execute the procedure by ADO...
--- Goos van Beek.
Shell "sqladv.exe -Uuser -Ppassword -SserverL" & " -Ddatabase" & _
" -istoredProcedure.sql" & " -G", vbHide
But I still want to execute the procedure by ADO...
--- Goos van Beek.
Hi Goos,
Again I'm not all that familiar with ASE but if you think it might be something to do with Temp tables then I might have a suggestion. SQL Server has a Table datatype that you can use to declare Tables as variables rather than Temp tables. Not sure if ASE has this equivalant datatype..? Could be worth investigating...
Jack
Again I'm not all that familiar with ASE but if you think it might be something to do with Temp tables then I might have a suggestion. SQL Server has a Table datatype that you can use to declare Tables as variables rather than Temp tables. Not sure if ASE has this equivalant datatype..? Could be worth investigating...
Jack
ASKER
Hi JAck.
I know what you mean, but a table datatype is not available within ASE....
Thanks again,
Goos
I know what you mean, but a table datatype is not available within ASE....
Thanks again,
Goos
ASKER
Unfortunately ADO 2.8 shows the same behaviour. I downloaded the MDAC 2.8 SDK (as suggested in the pdf) and tested with the rowset viewer. The result is the same as in the VB environment. No error messages and no records added to my table.
It might have something to do with the 'select ... into #tmp' clause in my procedure. When I run the procedure from ADO I have to set the database option 'DDL in transaction' true (for the tempdb). Using a 'create table' clause instead of only a 'select into' doesn't have any effect either..
I don't know what to do anymore :-(
Of course I can create a client-side procedure which does the same, but that should be the last resource...
--- Goos van Beek.