Changed Table Variable definition not re-scanned (3.6.0.109)

PDinCA

If I declare a table-variable and then write some code that consumes it, SQL Prompt picks up the TV and auto-completes OK. If I then make significant changes to the structure of the TV, how do I provoke SQL Prompt to use the NEW definition? (It insists on using the old one.) Closing the script window, losing bookmarks and split screen and the useful change markers, etc., is definitely possible, though undesirable, so could either a "force schema refresh" include a re-scan for variables, or give us another CTRL+ hotkey to do the job if it's too complex a thing to have SQL Prompt do automatically...

Eddie D

Can you please confirm that refreshing the cache does not resolve this issue. To refresh the cache file for the database you are using in your query editor, on the SQL Prompt menu, click Refresh Cache, or press CTRL+SHIFT+D.

Many Thanks
Eddie

Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-Mail: Support@red-gate.com

Lionel

Hi PDinCA

When I try to duplicate your issue my SQL Prompt seems to be picking up the changes to the table variable. In the Performance options tab on the options dialog under Listed Candidates there are some options that control the rescanning of the text. I suspect that your select statement is lower the the scanning range in that tab so the table variable is not being rescanned. If you write a select statement just below the Table variable does it pick up the changes ?

Lionel

PDinCA

Ths falls into my common experience of "sometimes it works, sometimes it doesn't". I have several other posts that cite things that "stop working" and this appears to be another of them. I just changed a TV and it was picked up this time. I'll monitor the situation as I'm sure it will recur... When it does, what information is there that I can collect that may assist you?

Lionel

If you could check the log file and see if there are any exceptions in there that would be useful. It sounds like something is breaking the parser and that is causing your problems. I would be interested if you could disable SQL Prompt do one small change (like typing a letter) and then enable SQL Prompt. This should cause a full reparse. If it starts working again then it might be a bug in our change tracking. If it doesn't start working then there must be something in your script that is throwing it off.

Lionel

PDinCA

I'm fairly sure that the incident occurred during the timespan of the log entries that follow - several of which detail exceptions...

14 Nov 2007 21:31:01,321 [1] INFO  RedGate.SqlPrompt.Engine.LogService - Starting new SQL Prompt editor session
15 Nov 2007 00:28:46,658 [ConfigurableThreadPool thread] INFO  _181 - Hibernating cached database [pd-dev-02].[PurposeDrivenBOS]
15 Nov 2007 17:42:05,259 [ConfigurableThreadPool thread] INFO  _181 - Hibernating cached database [PD-SQLDB].[PurposeDrivenBOS]
15 Nov 2007 17:42:47,991 [ConfigurableThreadPool thread] INFO  _181 - Hibernating cached database [10.40.7.130].[master]
15 Nov 2007 17:46:53,484 [ConfigurableThreadPool thread] INFO  _181 - Hibernating cached database [10.40.7.130].[PurposeDrivenBOS]
15 Nov 2007 17:47:43,576 [ConfigurableThreadPool thread] INFO  _181 - Hibernating cached database [PD-SBC-SQLDEV01].[PurposeDrivenBOS]
15 Nov 2007 18:23:42,658 [54] ERROR RedGate.SqlPrompt.Engine.LogService - LogService caught unhandled exception in AppDomain: 'Thread was being aborted.'
System.Threading.ThreadAbortException: Thread was being aborted.
   at System.Threading.Thread.SleepInternal(Int32 millisecondsTimeout)
   at RedGate.Refactoring.AddIn.Dialogs.SpinnyThing.SpinnyWidget._1()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()
15 Nov 2007 23:08:38,868 [ConfigurableThreadPool thread] INFO  _181 - Hibernating cached database [pd-dev-01].[PurposeDrivenBOS]
15 Nov 2007 23:08:47,119 [ConfigurableThreadPool thread] INFO  _181 - Hibernating cached database [pd-dev-02].[PurposeDrivenBOS]
15 Nov 2007 23:23:23,470 [ConfigurableThreadPool thread] INFO  _181 - Hibernating cached database [PD-SQLDB].[PurposeDrivenBOS]
16 Nov 2007 00:15:53,769 [ConfigurableThreadPool thread] INFO  _181 - Hibernating cached database [10.40.7.130].[master]
16 Nov 2007 23:03:19,248 [ConfigurableThreadPool thread] INFO  _181 - Hibernating cached database [10.40.7.130].[PurposeDrivenBOS]
16 Nov 2007 23:03:22,583 [ConfigurableThreadPool thread] INFO  _181 - Hibernating cached database [PD-SBC-SQLDEV01].[PurposeDrivenBOS]
16 Nov 2007 23:08:20,471 [45] ERROR RedGate.SqlPrompt.Engine.LogService - LogService caught unhandled exception in AppDomain: 'Thread was being aborted.'
System.Threading.ThreadAbortException: Thread was being aborted.
   at System.Threading.Monitor.Enter(Object obj)
   at RedGate.Refactoring.AddIn.Dialogs.SpinnyThing.SpinnyWidget._1()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()
16 Nov 2007 23:08:32,328 [115] ERROR RedGate.SqlPrompt.Engine.LogService - LogService caught unhandled exception in AppDomain: 'Thread was being aborted.'
System.Threading.ThreadAbortException: Thread was being aborted.
   at System.Windows.Forms.SafeNativeMethods.GetWindowThreadProcessId(HandleRef hWnd, Int32& lpdwProcessId)
   at System.Windows.Forms.Control.get_InvokeRequired()
   at RedGate.Refactoring.AddIn.Dialogs.SpinnyThing.SpinnyWidget._1()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()
16 Nov 2007 23:08:55,442 [45] ERROR RedGate.SqlPrompt.Engine.LogService - LogService caught unhandled exception in AppDomain: 'Thread was being aborted.'
System.Threading.ThreadAbortException: Thread was being aborted.
   at System.Threading.Monitor.Enter(Object obj)
   at RedGate.Refactoring.AddIn.Dialogs.SpinnyThing.SpinnyWidget._1()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()
16 Nov 2007 23:51:50,965 [1] ERROR RedGate.SqlPrompt.Engine.SqlPromptEngine - Invalid second child for alias table. Was expecting an identifier (578) but found element of type 22.
16 Nov 2007 23:51:50,965 [1] ERROR RedGate.SqlPrompt.Engine.SqlPromptEngine - _44
16 Nov 2007 23:51:51,035 [1] ERROR RedGate.SqlPrompt.Engine.SqlPromptEngine -    at RedGate.SqlPrompt.Engine.Candidates.DerivedTableCandidate._1(SqlPromptEngine , _189 , Element )
   at RedGate.SqlPrompt.Engine.Candidates.DerivedTableCandidate..ctor(SqlPromptEngine engine, ISqlInfo parent, _189 astMap, Element tableSubquery)
   at _161._5(SqlPromptEngine , _189 , Element )
16 Nov 2007 23:51:51,105 [1] ERROR RedGate.SqlPrompt.Engine.SqlPromptEngine - Invalid second child for alias table. Was expecting an identifier (578) but found element of type 22.
16 Nov 2007 23:51:51,105 [1] ERROR RedGate.SqlPrompt.Engine.SqlPromptEngine - _44
16 Nov 2007 23:51:51,105 [1] ERROR RedGate.SqlPrompt.Engine.SqlPromptEngine -    at RedGate.SqlPrompt.Engine.Candidates.DerivedTableCandidate._1(SqlPromptEngine , _189 , Element )
   at RedGate.SqlPrompt.Engine.Candidates.DerivedTableCandidate..ctor(SqlPromptEngine engine, ISqlInfo parent, _189 astMap, Element tableSubquery)
   at _161._5(SqlPromptEngine , _189 , Element )
21 Nov 2007 18:56:17,111 [ConfigurableThreadPool thread] INFO  _181 - Hibernating cached database [pd-dev-01].[PurposeDrivenBOS]
21 Nov 2007 22:50:17,460 [115] ERROR RedGate.SqlPrompt.Engine.LogService - LogService caught unhandled exception in AppDomain: 'Thread was being aborted.'
System.Threading.ThreadAbortException: Thread was being aborted.
   at System.Threading.WaitHandle.WaitOneNative(SafeWaitHandle waitHandle, UInt32 millisecondsTimeout, Boolean hasThreadAffinity, Boolean exitContext)
   at System.Threading.WaitHandle.WaitOne(Int64 timeout, Boolean exitContext)
   at System.Threading.WaitHandle.WaitOne(Int32 millisecondsTimeout, Boolean exitContext)
   at System.Windows.Forms.Control.WaitForWaitHandle(WaitHandle waitHandle)
   at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
   at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
   at System.Windows.Forms.Control.Invoke(Delegate method)
   at RedGate.Refactoring.AddIn.Dialogs.SpinnyThing.SpinnyWidget._1()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

Lionel

Thanks a lot! I will forward that along to the developer who is responsible for the engine to see if he has any insight as to what is going on.

Lionel