errors executing stored procedures through ADO??

[dev X]

I am executing a stored procedure on a connection object in order to pass
back a adodb.recordset object. This works fine for simple select statements
but when a stored procedure does an insert and then a select the stored procedure
exits after the insert statement. I can check this as the insert affects
21 records and the select only retrieves 2 records. I used the connection.execute(
"exec sp_XX", iRows) to retrieve the rows affected, which was 21 and not
2. The recordset set returned is a closed recordset which would be consistant
with an insert statement but not a select. However, the stored procedure
works as it should if I call the stored procedure from query analyser..??
any clues what is wrong?? ( I am using vb6 -> sp3, sql server 7 -> sp1 and
ADO 2.1 )

[Daniel Reber]

"dev X" <stephen.hawkins@northern-electric.co.uk> wrote:
>
>I am executing a stored procedure on a connection object in order to pass
>back a adodb.recordset object. This works fine for simple select statements
>but when a stored procedure does an insert and then a select the stored
procedure
>exits after the insert statement. I can check this as the insert affects
>21 records and the select only retrieves 2 records. I used the connection.execute(
>"exec sp_XX", iRows) to retrieve the rows affected, which was 21 and not
>2. The recordset set returned is a closed recordset which would be consistant
>with an insert statement but not a select. However, the stored procedure
>works as it should if I call the stored procedure from query analyser..??
>any clues what is wrong?? ( I am using vb6 -> sp3, sql server 7 -> sp1 and
>ADO 2.1 )

If you are doing the INSERT and SELECT in the same sp, then insert 'SET NOCOUNT
ON' before you do the insert then 'SET NOCOUNT OFF' before you do the select
statement. Hope this helps

Daniel Reber, MCP