Error: "There is already an open DataReader ..."

First - I think this product kicks RedGate's butt.

I have actually two reproducible issues that are related, I believe. I have query made up of a bunch of derived tables. Normally, SQL Assistant handles it - but it seems to choke on this one. SQL Assistant also cannot see the fields contained in the sub-query when typing the alias. The full error is:

An error occurred while executing batch. Error message is: There is already an open DataReader associated with this Command which must be closed first.

I am using SQL Server Management Studio connected to a SQL Server 2000 DB. I have version 3.0.20 Beta. I have commented what it takes to reproduce the two problems below.

The Query:

	Code:
	DECLARE @dStart DATETIME SET @dStart = '20071122' SELECT -- type "ty." in the select list get no fields. Type "ISNULL(ty.Type, '')" and try to execute, get the error. FROM ( SELECT a1.Type, a1.Revenue FROM AggregateDateTypeDCI a1 WHERE a1.ValidDate = @dStart UNION ALL SELECT a2.type, a2.Revenue FROM AggregateDateTypeDCI a2 WHERE a2.ValidDate = @dStart ) ty

Also - as a bonus piece of information, if you take out the @dStart variables and replace them with literals, it seems to remedy the problem, and I can no longer reproduce either bug.

Good luck, and keep up the good work with this awesome tool.

- Sean

Thanks for nice comments and for trying SQL Assistant!

Support for handling of variables in derived tables is already in the queue and should be available in one of the following maintenance releases for version 3.x. I cannot provide exact timing now or disclose the complete list of planned enhancements for future maintanence releases. The list is still open. If you have any suggestions, please let us know. We appreciate all feedback and happy to implement whatever possible to make SQL coding as easy and efficient as possible.

In regard to "There is already an open DataReader " error, this error occurs when your editor is using the connection running some background queries while you are editing the code. Please keep in mind that SQL Assistant in most cases shares the connection with the target editor and cannot use it simultaneously y to run its own queries required for the SQL Intelli-sense. In beta build 3.0.21 we implemented a change that makes SQL Assistant not to display such errors if the connection is busy and cannot be used. It assumes that the database execution context is the same and relies on the current cache contents only. Please note that the last beta build is 3.0.23 and it is already available. For release notes please see http://softtreetech.com/support/phpBB2/viewtopic.php?t=22026

For me, SQL Assistant seems to handle most derived tables already reasonably well - just not in this example.

I just downloaded 3.0.24, and I can still reproduce the "Open DataReader" issue. From what I can tell, there isn't a problem with the sharing of the connection except in this example. After typing the ISNULL(ty.Type, ''), I cannot execute any more SQL commands until I reset the database (in the database selection window), or close and re-connect. The issue isn't occasional - is stays in an error state.

So - what I think is that SQL Assistant is trying to fetch valid columns from the dervied tables, and gets stuck - failing to execute a Close(), which then ties up the rest of the session.

FYI - I'm not interested in the derived table column names with this posting - I'm more worried about the "open DataReader" thing. I'm still in love with the product, and this kind of thing isn't a normal occurrence, probably - but it is reproducible, and therefore preventable.

- Sean

We are looking into this issue. It has been sitting in the support system already for a while. Just for the reference issue #342.