SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Error: "There is already an open DataReader ..."

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Error: "There is already an open DataReader ..."
Author Message
seannerd



Joined: 24 Nov 2007
Posts: 3
Country: United States

Post Error: "There is already an open DataReader ..." Reply with quote
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
Sat Nov 24, 2007 1:19 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
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
Sat Nov 24, 2007 12:26 pm View user's profile Send private message
seannerd



Joined: 24 Nov 2007
Posts: 3
Country: United States

Post Reply with quote
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
Thu Nov 29, 2007 12:52 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
We are looking into this issue. It has been sitting in the support system already for a while. Just for the reference issue #342.
Thu Nov 29, 2007 1:56 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.