SQLTeam.com | Weblogs | Forums

SP with 4 select statements not returning records from one of the 4 issue -VB.NET


#1

I have a stored procedure that runs 4 separate select statements. From there, in VB.NET, I use a datareader to get each of the 4 sets of records. With each set of records I populate an EXCEL file, one worksheet for each set of records

To get the first set of records from the stored procedure, I use
While datareader.Read
Get all of the data from first Select
End While

I then use
datareader.NextResult
While datareader.Read
Get all of the data from subsequent Selects
End While
to get the 2nd, 3rd, and 4th set of records.

My code will hang when, for example, the 3rd set of records has or returns no records. It takes the VB code a very long time to get past the empty 3rd record set, if at all, and never gets to the next and final 4th record set

Any suggestions on how I can get the code to move past any of these when one, or more of the Select statements return no records?

SQL 2016

TIA
Bill


#2

Try something like:

if (datareader.HasRows)

#3

Thanks. To my understanding, the datareader will have rows since some at
least one of the four select statements will have returned some records.
So, I think that won't work unless I am missing something


#4

Sorry, I misread the question.

I have never had any problems with while (dr.Read()) but I have also never tried to populate an Excel Worksheet.


#5

I am not familiar with DotNet but we have some extra steps in our "Next Recordset" handler which will have equivalence in VB.NET I expect.

After NextRecordset (assuming no error raised) we check
CurrentResultSet.ActiveCommand.ActiveConnection.errors.count and if there are any then we loop around that array outputting the error information.

Else the Errors Collection Count = 0 then we check the CurrentResultSet for:

Is Valid object?
If CurrentResultSet.State <> 1 then "some problem"
If CurrentResultSet.BOF <> 0 AND CurrentResultSet.EOF <> 0 then Empty ResultSet
Else "Has Rows"


#6

No problem. It is not the populating of the EXCEL file that is the issue,
per se, it's the fact that the vb.net code is hanging up on trying to get
the data from the third of the 4 select statements in the stored procedure
before getting the 4th set of records, which has data. The 1st and 2nd
select statements do return records and the code progresses nicely thru
them.

And, it's no that just the 3rd one is the issue; it just happens to be the
one that does not return records in this case or specific situation.

Thanks
Bill


#7

Is it possible that the actual SQL query for that takes "longer than usual" - i.e. your VB.NET code is fine, but after reading the previous result set SQL only then starts making the next, empty, query and "that takes it some time"?

It might be, for example, that the query is poorly optimised for the situation that no rows are found.


#8

Thanks for the reply, the query is optimized, already checked that


#9

Just to double check: "already checked that" as in spied on it actually running (e.g. using SQL Profiler) from the APP to check that the query for that, empty, recordset does not take SQL "longer than reasonable" and also "does not use a daft query plan"?

Doing a synthetic test in isolation can fail to show up user-related issues. Two that spring to mind are parameter sniffing and things like, for example, absence of Schema prefix to table names causing Parser to examine if there is a user-specific table/view available instead.

If you already know all this stuff then apologies and please ignore :slight_smile: