Re: recordset not opening even though query analyzer returns resul

Yes, I'm convinced that VB can execute this complex SP. I expect you'll have
to depend on using NextResultset to step through the set of resultsets
returned by the SP. That said, I also know that the QA will have trouble
building an efficient plan for the SP as it generates the plan based on the
first execution of the SP. Subsequent calls (that use different parameter
settings) would/might generate a (very) different plan but are forced to use
the first. It's tough to make any bold suggestions here, but I would
consider breaking the SP down into smaller component parts that are called
by one or more "driver" SPs. Each of the smaller SPs gets its own QP and
would be easier to tune.

I assume you're in the UK. I know a great SQL Server consultant that can
help you if you're interested.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


"steven scaife" <stevenscaife@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F59C0D8E-474B-4592-99F0-F9797E874E11@xxxxxxxxxxxxxxxx
>I forgot to mention that I have added set nocount on in my sp, this was the
> only way i could get an earlier version of it to return in a recordset.
>
> My sp takes around 50 inputs but only requires 5 for the sp to run.
> The sp then creates a temporary table which is an exact replica of one of
> the tables, then i do an insert select statement to populate the temporary
> table with all the records from the table. Then based on the 5 required
> parameters (these are bit datatype) deletes from the temporary table if
> its
> true. Its worth mentioning that if this was all the sp did it works and I
> get a recordset in my VB code.
>
> However because there are 45 other parameters that could be entered the sp
> does something else.
>
> It is for a financial program and based on the parameters removes certain
> mortgages from the list based on the adverse data supplied to the sp. So
> a
> maximum of 15 ccjs can be passed to the sp, but each ccj has 3 types of
> information associated with it a value, a date registered, a date it was
> satisfied.
>
> So the next stage in my sp is to fetch the remaining records in my temp
> table into a cursor and add up th evalues of the ccjs based on values in
> the
> table. If ccj1 is £120 but the row in the database which corresponds to a
> mortgage ignores ccjs up to £150 and any ccjs older than 24 months or
> satisifed over 12 months ago then its not counted. so if this was the
> case
> for all 15 ccjs the mortgage is valid to the client and stays in the temp
> table, the next row is fetched and the same is done on that row, if the
> criteria entered doesn't fit for the row then it is deleted from the temp
> table, once all the rows have been matched the cursor is destroyed and a
> select statement is issued returning the results.
>
> so the above is performed in a loop with a series of nested ifs
>
> Then after the select is issued the temp table is dropped
>
> finally
>
> When i test the sp in query analyzer I get the results I expect, when I
> execute the sp in my vb code for some reason the recordset refuses to open
> and its down to the loop and series of nested ifs I perform.
>
> The only thing I can think that is causing problems is the while loop and
> nested ifs because if i remove this section form my sp it works in vb.
> But
> if its returning a resultset in QA why wont it in vb, is there another
> setting like nocount that i need to use. Or am I using the wrong
> parameters
> to open the recordset, I just assume I can use a firehose cursor as I only
> want to put the results into a grid
>
> thanks for the help
>
>
> "William (Bill) Vaughn" wrote:
>
>> This is by design. As I discuss (in great detail) in my books, when you
>> execute a query the SQL engine returns a resultset. You use the Recordset
>> object to handle these. If the resultset has a rowset, the Recordset's
>> State
>> is adStateOpen. If no rows were returned, you still get an "open"
>> Recordset.
>> However, if you execute an SQL command that does not return a rowset
>> (like
>> an UPDATE command), your Recordset tells you there is no rowset by
>> setting
>> the State property to adStateClosed.
>>
>> To remedy this, the easiest way is to turn off these rowsetless
>> resultsets
>> by adding SET NOCOUNT ON in your SP. I don't suggest this as it discards
>> the
>> "rows affected" value returned by the action command. Instead, you can
>> simply step to the next resultset returned by your query-- Set RS =
>> Rs.NextResult (or somesuch).
>>
>> hth
>>
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> www.betav.com/blog/billva
>> www.betav.com
>> www.sqlreportingservices.net
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> __________________________________
>>
>>
>> "steven scaife" <stevenscaife@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:691F66FD-18C1-421A-BAF0-A1303A1EDDB0@xxxxxxxxxxxxxxxx
>> >I have a query that runs a stored procedure which returns a resultset in
>> > query analyzer but when run in vb doesn't open a recordset
>> >
>> > I have tried set rs = conn.execute(strSQL)
>> > and
>> > rs.open strSQL, conn, adOpenForwardOnly , adLockReadOnly
>> >
>> > i get an error saying operation not allowed when object is closed
>> >
>> > I dont understand why it isn't opening when I get results from the same
>> > sp
>> > in query analyzer
>> >
>> > Does anyone have any ideas if you need more information just ask
>>
>>
>>


.

Relevant Pages

  • Re: recordset not opening even though query analyzer returns resul
    ... "William Vaughn" wrote: ... I'm convinced that VB can execute this complex SP. ... >> only way i could get an earlier version of it to return in a recordset. ... >>> execute a query the SQL engine returns a resultset. ...
    (microsoft.public.vb.database.ado)
  • Re: recordset not opening even though query analyzer returns resul
    ... only way i could get an earlier version of it to return in a recordset. ... table into a cursor and add up th evalues of the ccjs based on values in the ... When i test the sp in query analyzer I get the results I expect, ... > execute a query the SQL engine returns a resultset. ...
    (microsoft.public.vb.database.ado)
  • Re: Return value from SQL Stored Procedure
    ... There is no need to create a recordset with this procedure. ... > below, your procedure actually is returning a resultset, but it's not one ... use the adExecuteNoRecords option setting in your Execute ... > quicker response by posting to the newsgroup. ...
    (microsoft.public.scripting.vbscript)
  • Re: UPDATE query in Access 2003 raising error
    ... The only reason I questioned the recordset is that you could conceivably ... the SQL looks fine to me. ... Set qdfTemp = db.CreateQueryDef ... Elsewhere in the code I use the same technique to execute an SQL statement ...
    (microsoft.public.access.formscoding)
  • Re: Fastest db methods?
    ... side-effects, such as scalability issues (for example, a SqlDataReader is ... > Reading a recordset using SqlDataAdapter.Fill/ reading a ... recordset using SqlDataReader ?? ... and then send the actual resultset. ...
    (microsoft.public.dotnet.framework.adonet)