most bizarre problem ever

[jhcorey]

I have a stored procedure that takes 14 parameters, including two for
start date and end date. The procedure then creates a sql string and
does an 'exec sql'.

The stored procedure always works correctly when I run it stand alone.

When I run it in my app, I sometimes get duplicate records, and
sometimes get no records. This is based on date range. I set the
range to 8/1/04 to 9/1/04, and I get a double set of records in the
dataset. If the range is 10/1/04 to 11/1/04, the dataset is correct.
If the range is later in 2005, I don't get anything.

I re-did the data access. I was using sqlhelper, so I just wrote
totally new code, but it didn't change anything.

The date parameters are datetime variables in sql, and the parameters
are declared this way in the vb.net code.

The procedure does a select against a view, but it's really not that
complicated. There's no unions or sub-selects or anything.
And as I said, it always works correctly in query analyzer.

I similar procedure in a classic asp app also works correctly.

Jim

[Hans Kesting]

> I have a stored procedure that takes 14 parameters, including two for

start date and end date. The procedure then creates a sql string and
does an 'exec sql'.

The stored procedure always works correctly when I run it stand alone.

When I run it in my app, I sometimes get duplicate records, and
sometimes get no records. This is based on date range. I set the
range to 8/1/04 to 9/1/04, and I get a double set of records in the
dataset. If the range is 10/1/04 to 11/1/04, the dataset is correct.
If the range is later in 2005, I don't get anything.
Let's ask the obvious first:
You are sure that the data itself is correct? First range contains
no double data, ranges later in 2005 do contain data?

I re-did the data access. I was using sqlhelper, so I just wrote
totally new code, but it didn't change anything.

The date parameters are datetime variables in sql, and the parameters
are declared this way in the vb.net code.

The procedure does a select against a view, but it's really not that
complicated. There's no unions or sub-selects or anything.
And as I said, it always works correctly in query analyzer.

I similar procedure in a classic asp app also works correctly.

Jim

[jhcorey]

To be honest I haven't looked at the raw data. I'm relying on the
results of the stored procedure when I run it in query analyzer.

But I imagine the answer will be obvious and that double-checking
everything (again) will show me something.

[UJ]

Try using SQLProfiler - then you can see the exact call that is made to SQL.
You can then run it in Query Analyzer and make sure your getting the correct
results.

J.
<jh*****@yahoo. com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

To be honest I haven't looked at the raw data. I'm relying on the
results of the stored procedure when I run it in query analyzer.

But I imagine the answer will be obvious and that double-checking
everything (again) will show me something.