stored procedure and recordset

stored procedure and recordset

Post by Georg » Sat, 06 Dec 2003 19:59:09


Hi to everybody....(sorry fore the code example...i assume that it will make
understanding easier)
I have an application in Access97 wich is connected
via ODBC to a SQL-Server 7.0 Database (the tables are linked)
My problem is that if i make a call to a stored procedure (to add f.e some
data to a table)
within a WorkSpace and then trying to open a recordset,to the same
table,within the same workspace,
i get the message (after 60sec) "ODBC-Call failed".(I know that 60 sec are
the ODBC time out.)
The same 'thing' works perfect IF the database is a standard .mdb file.
My question is:
Why can't i open a recordset to a table, when few lines before,
a stored procedure is called to make some changes to the same table
(always within the same Workspace) when i am connected via ODBC to the
Database ???
Is there any way to resolve this?
Example Code:
============================================================================
===============================
On Error GoTo errZ
Dim myws As Workspace, mydb As Database, mydef As QueryDef
Dim myset1 As Recordset, myset2 As Recordset, mysql As String
Set myws = DBEngine.Workspaces(0)
myws.BeginTrans
mysql = "sp_insert_TABLE1 'George'"
Set mydb = CurrentDb()
Set mydef = mydb.CreateQueryDef("")
mydef.ReturnsRecords = True
mydef.Connect = "ODBC;DATABASE=unidata;UID=sa;PWD=;DSN=unidata"
mydef.SQL = mysql
Set myset1 = mydef.OpenRecordset() 'execute the stored
procedure

Set myset2 = mydb.OpenRecordset("table1", dbOpenDynaset,
dbSeeChanges) '**here the code fails**

myws.CommitTrans

Exit Sub
errZ_nxt:
myws.Rollback
Exit Sub
errZ:
If Err = 3325 Then 'Error handling of ...."Pass_through Query did not
return any records...."
Resume Next
Else
MsgBox Error$ & Err
Resume errZ_nxt
End If
============================================================================
===============================
I appreciate any help......
Many Thanks in advance!!!
George!
 
 
 

1. Using variables for tablename in stored procedures/getting records in a Recordset from stored procedure

2. "recordset is not open" with multiples recordsets from Stored procedures

Hi all,

I am using delphi 6 with MSSQL 2005.

I post my code... the error I am asking is on the last 5 lines... the error
is with comments section " //

I appreciate any help, thanks in advance



var
aDataSet : TADOStoredProc;
recordsAffected: integer;
begin
aDataSet := TADOStoredProc.create(nil);
aDataSet.Connection := myConnection;
aDataSet.ProcedureName := 'sincronizacion_' + mySucursal;
aDataSet.CommandTimeout := 3000;
aDataSet.Parameters.Refresh;
aDataSet.Open;

myReturningDataSet.Recordset := aDataSet.Recordset;
lblArticulos.Caption :=
myReturningDataSet.FieldByName('Articulos').asString;

//all is working so far....
//The following line raise an error : "recordset is not open"
myReturningDataSet.Recordset :=
aDataSet.NextRecordset(recordsAffected);
...
...
end

3. VB6/ADO: Using Recordset.Sort on a recordset from a stored procedure

4. Stored Procedure calls another Stored Proc which returns a recordset

5. Calling a stored procedure from a stored procedure and ignoring output from inner procedure call

6. Map Stored Procedure dependencies from ASP pages through methods to stored procedures

7. Using stored procedure result set in another stored procedure

8. all stored procedures are user stored procedures

9. Stored procedure refer to another Stored Procedure ?

10. Using a value from a stored procedure in the calling stored procedure?

11. Call Stored Procedure Within Stored Procedure!

12. System Stored Procedure for validating a stored procedure.

13. Stored procedure from stored procedure

14. Stored procedure in database X, executes stored procedure in database Y, wrapped in transaction?

15. calling a stored procedure from a stored procedure