missing rows in procedure output

6 Jul 2006 5:57 PM

Keith G Hicks

I'm getting a result set that seems to be missing some rows in one
situation. I have some code like this in a stored procedure:

Select * From vwCustomers

vwCustomers is a view on several different tables.

When I run Select * From vwCustomers in QA all by itself it returns 88 rows.
When I run the stored procedure in QA, that line of code returns 80 rows.

Can anyone tell me what might cause this sort of thing?

Thanks,

Keith

6 Jul 2006 6:08 PM

Tracy McKibben

Keith G Hicks wrote:
Show quoteHide quote

> I'm getting a result set that seems to be missing some rows in one
> situation. I have some code like this in a stored procedure:
>
> Select * From vwCustomers
>
> vwCustomers is a view on several different tables.
>
> When I run Select * From vwCustomers in QA all by itself it returns 88 rows.
> When I run the stored procedure in QA, that line of code returns 80 rows.
>
> Can anyone tell me what might cause this sort of thing?
>
> Thanks,
>
> Keith
>
>

Can you post the stored procedure for us to see?

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

6 Jul 2006 6:25 PM

Keith G Hicks

Regretfully I can't or I already would have happily done that. However, I
think I see what's going on. There is some code just above the select on the
view that updates some of the underlying tables. And prior to that I start a
transaction. I'm guessing that the view is not seeing the rows that have
been updated by the udpate statements above? Is there a way to work around
this so that I can use the view in the procedure and still have all the
other code in a transaction? I haven't run into this issue before.

Keith

6 Jul 2006 6:35 PM

Tracy McKibben

Keith G Hicks wrote:

> Regretfully I can't or I already would have happily done that. However, I
> think I see what's going on. There is some code just above the select on the
> view that updates some of the underlying tables. And prior to that I start a
> transaction. I'm guessing that the view is not seeing the rows that have
> been updated by the udpate statements above? Is there a way to work around
> this so that I can use the view in the procedure and still have all the
> other code in a transaction? I haven't run into this issue before.
>
> Keith
>
>

It's possible, as those changes aren't committed yet. You might resolve
this by using NOLOCK or READUNCOMMITTED hints in the query behind your
view. Or skip the view altogether, and just read directly from the tables.

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

6 Jul 2006 6:10 PM

Roy Harvey

If a view changes, any view that references it needs to be recompiled.
The same goes (I believe) for any stored procedure that references a
changed view. Just script the view or proc as an ALTER and execute
it. If there are layers of reference be sure to recompile from the
inside out, so to speak.

Roy Harvey
Beacon Falls, CT

Show quoteHide quote

On Thu, 6 Jul 2006 13:57:46 -0400, "Keith G Hicks" <k**@comcast.net>
wrote:

>I'm getting a result set that seems to be missing some rows in one
>situation. I have some code like this in a stored procedure:
>
>Select * From vwCustomers
>
>vwCustomers is a view on several different tables.
>
>When I run Select * From vwCustomers in QA all by itself it returns 88 rows.
>When I run the stored procedure in QA, that line of code returns 80 rows.
>
>Can anyone tell me what might cause this sort of thing?
>
>Thanks,
>
>Keith
>

6 Jul 2006 6:26 PM

Keith G Hicks

I just tried that and it didn't solve the problem but I think I see what's
going on. See my follow up to Tracy's post.

Keith

6 Jul 2006 6:14 PM

--CELKO--

>> SELECT * FROM vwCustomers;

vwCustomers is a view on several different tables. <<

First of all, SELECT * is a really bad practice in production code.
List the columns. Next, why are you putting that "vw-" prefix on a
data element name? According to ISO-11179, you must mean "Volkswagen
Customers" :)

>> Can anyone tell me what might cause this sort of thing? <<

Not without seeing some code. One possible cause is using
CURRENT_TIMESTAMP in a VIEW and running a query with that VIEW at two
different times.

Thread options