Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2002-10-24 : 13:36:49
|
| Hiya!This is a weird one: When changing the table schema of the Payments table (that one only!) by enabling/disabling triggers, subsequently trying to open a cursor based on that table gives the ADO error: "Cursor operation cannot be completed because table schema has changed", or s/t like that. Only after restarting the server does the problem go away. It would seem that the old cursor might still be in memory and the new cursor tries to get that one, but DBCC DropCleanBuffers doesn't help. It's very annoying to have to keep on restarting, and if anyone has a solution, Thanks.Sarah Berger MCSD |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-24 : 13:42:40
|
| you mean dbcc freeproccache, right?Jay White{0} |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-10-24 : 13:47:58
|
| No. I thought dbcc dropcleanbuffers will clear all rows previously selected that are still in memory. Is that right?dbcc freeproccache will free procedures in memory, and the cursors are not opened by using SPs, but rather by saying rs.open ... using ADO in VB.Sarah Berger MCSD |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-24 : 13:54:14
|
From Books Online, "DBCC FREEPROCCACHE":quote:
Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.
FREEPROCCACHE: it's not just for stored procedures!  |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-10-24 : 14:07:45
|
| Thanks, that did it!!So what exactly does dbcc dropcleanbuffers do?Oddly, DBCC FreeProcCache is not mentioned in my BOL, and neither is DBCC DropCleanBuffers. They run though, on SQL 7. Maybe the help is just outdated.Sarah Berger MCSD |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-24 : 14:47:33
|
| No, they were undocumented until SQL Server 2000, unless you boughtThe Guru's Guide To Transact-SQLby Ken Henderson.DBCC DROPCLEANBUFFERS will flush the data cache, and any SELECT statements immediately afterwards will have to access the disk. FREEPROCCACHE works similarly, but on cached and/or previously compiled code.In your situtation, freeing the data cache would probably not matter. However, trigger code is basically appended to the commands that are executed and cached; since you disabled the trigger the cached plan subsequently became invalid, and ADO probably did not recognize that...since the cursor definition(s) were the same...and therefore did not force a recompile. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-10-24 : 14:55:03
|
| That was a very good explanation. Thumbs Up!And yeah, I get it... I'll get the book. (And I probably won't regret it, huh?)Sarah Berger MCSD |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-10-27 : 21:29:10
|
| $volk ++ %commission * $listprice----Nancy Davolio: Best looking chick at Northwind 1992-2000Edited by - tim on 10/27/2002 21:29:44 |
|
|
|
|
|
|
|
|
Weird problem when changing table schema