TOAD for SQL Server cache issues

Toad for SQL Server
1

Hi,

It’s been a while since posts, so please bear with me on this one.

This has started recently, and I am not sure if it’s a setting or some other reason that’s causing this particular issue. And I have been able to replicate it at will.

The problem is when referencing one database, running a query and running another query on another database.

Take the following query as an example …

select *
from tblStudentProgramReg as spr
where spr.EnrolmentDate IS NULL or spr.TerminationDate IS NULL

Now if you open a database, start a new Query Editor, paste the code and run it (using your own tables of course) you would expect the query to return data based on the database connection open at the time, right? Not so if you’ve opened a another database prior to running this same query.

Steps to replicate…

  1. Have all your database connections closed (or start a new Toad session)
  2. Open ONE database
  3. Press CTRL-N to open an editor window
  4. Type (or copy/paste) some SQL code similar to the above or a simple SELECT query
  5. Run the query
  6. Double-click another database connection to say a copy of the database you opened in step 2, but make sure it has different data (old data?) so you see the difference in the next steps.
  7. Again CTRL-N to open an editor window
  8. Paste the exact same SELECT query and run
  9. Does the data from the 2nd query match what you expected from the SECOND database?
  10. If yes, good (if not read below!)
  11. Now re-open the database you opened in Step 2
  12. Open a new Editor window
  13. Paste the same code in again and run it
  14. Do you see the same data you ran in Step 5? Most likely you’re seeing the data from the SECOND database!
    Even if you close the 2nd database and run the query again on the first database, you get the same data that was in the 2nd database!

This happens 100% all the time for me.

The only way around it is to reference the database directly in the SQL

select *
from **MYDB1.dbo.**tblStudentProgramReg as spr
where spr.EnrolmentDate IS NULL or spr.TerminationDate IS NULL

Even if you use the USE command at the beginning, it makes no difference. You HAVE to directly reference the database as per the above code in red.

This is definitely a bug in TOAD that I can replicate all the time. The only other way to “clear the cache” is to close and re-start TOAD again.

Anyone else experience this recently? It was not happening like a month ago, but suddenly it’s playing up like this.

Thank you

ps. We’re using TOAD SQL Server 6.1.0.1759, Professional

2

Bump. Anyone please?

3

Can someone PLEASE attend to this issue? It’s a serious flaw in the program which needs to be addressed. We have paid good money for this product and for it to suddenly play up like this is a serious problem and needs to be addressed urgently. Who can I go to please?

4

Hello TecNQ, thanks your feedback. i can reproduced this issue, and created a task in our jira,we will fixed it at next release

5

Thank you for your response. However, this is going to have to be more urgent than your next release. We’re experiencing data corruption issues, and it could be related to this. To be frank, I have no confidence in TOAD anymore. If it can allow such a large “bug” as this to exist all of a sudden, then what’s to say it does something similar with UPDATE commands? How can you guarantee your product is not corrupting data, when I link an old database and run queries against it on my production database?! I have evidence of data in our database that has reverted back to our backups back in May 2014. And coincidentally it seems to happen only to one table when I run an SQL select on a backup database and later run the same query in production for comparison purposes.

6

The editor will execute using the database you selected in the editor database drop down not depending on what you have selected in the object explorer. I don’t understand the issue.

7

Hi Henrik. The issue is quite simply, as I explained above, running a query in one window under one database and then running it again on another, you get the same results you did from the 1st database. Did you follow my steps exactly as described above? And what version of Toad do you have and SQL Server? Mine is 2005.

8

And you are sure that the dropdown for selecting the database INSIDE the editor window isn’t the same in both windows?

9

Ok. This is different!

Geesus I can’t believe it was that little drop-down!

Thank you so much Henrik.

Yes, I re-ran the steps above and was able to replicate it but noticed the dropdown was referring to my test database, but the highlight on the Connection Manager was highlighting my production database! No wonder I got confused.

Thank you

ps. Why then did “wiki” say it was a problem?

10

Not entirely sure :slight_smile: . The issue came over my desk and I thought I understood the problem but figured I would make sure you got this working too and that I didn’t misunderstand anything before closing it.

11

Awesome mate! Cheers

12

** STOP PRESS ***

No there is a bug.

Steps to replicate …

  1. Close all connections

  2. Open your test or development database

  3. CTRL-N

  4. Paste this code (change table name and WHERE to something that you know returns data but you know should NOT return the same or no data in production)

select *

from tblStudentProgramReg as spr

where spr.EnrolmentDate > spr.TerminationDate

  1. Run query. Hopefully you get results from your test database

  2. Open your production database

  3. CTRL-N

  4. Paste the exact same SQL you used in step 4.

  5. Run it.

  6. It works. No data right? Good

BUT

  1. Now add a USE on the first line. It should look similar to this …

USE

select *

from tblStudentProgramReg as spr

where spr.EnrolmentDate > spr.TerminationDate

  1. Run it.

  2. It should give you the same result as step 10. But it does not. It actually still returns the data from the TEST database.

Sorry guys, but that is definitely a bug.

13

I should add that the problem with this bug is that TOAD is ignoring the USE statement. No matter what the database dropdown is (other than the database in the USE command), it will still refer to what the dropdown says. The USE statement should override that dropdown choice.

14

I agree and that is absolutely a bug. In fact the use statement should actually change the drop down.

15

Ok, I just tested this and it works as I expected. When you execute a use statement in the editor the dropdown switches to the new database selected by the executed use statement (And it executes subsequent statements in the same batch on the correct DB as well).

16

[mention:c926dd60f3124a0e9a591905fc07653f:e9ed411860ed4f2ba0265705b8793d05] Sorry for the late response. Yes it’s working for me now too. I don’t know why it wasn’t before. Does Dell/Quest push out patch fixes into our systems silently, because now I cannot replicate the problem. Weird.