Changing an SP causes a Recordset object error.

[Keith]

I have an SQL7 database and a VB6 app, linked via ADO 2.5 - one particular
stored procedure builds a temp table then returns said table as a recordset.
I have made changes to the sp (the main one being that it now uses two cursors
to collate and append more data to the temp table).

If I call the sp from Query Analyzer it works fine, but calling it from the
VB app (which uses a command and recordset object) gives Error 3707:

"cannot change the Active Connection properties of a RecordSet object which
has a Command object as its source"

Is short, what I am asking is how can changing the way a stored procedure
works affect the active connection of a VB recordset object?

[Rune Bivrin]

My guess is ADO gets confused by the rowcounts being generated by the statements
prior to the actual SELECT that returns data. Try SET NOCOUNT ON as the first
statement of your SP.

Rune Bivrin


"Keith" <nospam@nospam.com> wrote:
>
>I have an SQL7 database and a VB6 app, linked via ADO 2.5 - one particular
>stored procedure builds a temp table then returns said table as a recordset.
>I have made changes to the sp (the main one being that it now uses two cursors
>to collate and append more data to the temp table).
>
>If I call the sp from Query Analyzer it works fine, but calling it from
the
>VB app (which uses a command and recordset object) gives Error 3707:
>
>"cannot change the Active Connection properties of a RecordSet object which
>has a Command object as its source"
>
>Is short, what I am asking is how can changing the way a stored procedure
>works affect the active connection of a VB recordset object?

[Keith]

That hasn't worked unfortunately.

I'm currently looking at rewriting it without cursors though so it may be
ok.

Thanks anyway!!

"Rune Bivrin" <rune@bivrin.com> wrote:
>
>My guess is ADO gets confused by the rowcounts being generated by the statements
>prior to the actual SELECT that returns data. Try SET NOCOUNT ON as the
first
>statement of your SP.
>
>Rune Bivrin
>
>
>"Keith" <nospam@nospam.com> wrote:
>>
>>I have an SQL7 database and a VB6 app, linked via ADO 2.5 - one particular
>>stored procedure builds a temp table then returns said table as a recordset.
>>I have made changes to the sp (the main one being that it now uses two
cursors
>>to collate and append more data to the temp table).
>>
>>If I call the sp from Query Analyzer it works fine, but calling it from
>the
>>VB app (which uses a command and recordset object) gives Error 3707:
>>
>>"cannot change the Active Connection properties of a RecordSet object which
>>has a Command object as its source"
>>
>>Is short, what I am asking is how can changing the way a stored procedure
>>works affect the active connection of a VB recordset object?
>

[Glenn Block]

This is probably happening because by adding the cursors, you have created
more recordsets that are being returned to ADO. I encountered this before,
it usually happens when if you are using disconnected recordsets. If you
are, when you set the active connection to nothing, you will get this error.
Try using .nextrecordset on the recordset object. You may have to call it
twice, one for each cursor. Eventually you will find a recordset that is
your recordset. You also need to check the recordset.state property to see
if the recordset is open or not. Anyway, once you have the recordset that
you want, then you can close the connection. The reason this happens, is
because when you use a client side recordset, the stored proc automatically
returns default recordsets for each recordset within the sp in order to
facilitate better performance. You should not get this behavior if you use a
server side recordset.

Let me know how it works for you

--Glenn


"Keith" <nospam@nospam.com> wrote in message
news:3e636408$1@tnews.web.devx.com...
>
> I have an SQL7 database and a VB6 app, linked via ADO 2.5 - one particular
> stored procedure builds a temp table then returns said table as a
recordset.
> I have made changes to the sp (the main one being that it now uses two
cursors
> to collate and append more data to the temp table).
>
> If I call the sp from Query Analyzer it works fine, but calling it from
the
> VB app (which uses a command and recordset object) gives Error 3707:
>
> "cannot change the Active Connection properties of a RecordSet object
which
> has a Command object as its source"
>
> Is short, what I am asking is how can changing the way a stored procedure
> works affect the active connection of a VB recordset object?