SSMS Hangs with certain INSERT statement

Hi,

I'm encountering a hanging SSMS when SQL Prompt tries to analyze this query in a query window:

INSERT INTO [ABC_ALL] SELECT * FROM [ABC_1] UNION ALL SELECT * FROM [ABC_2]

If I turn off Code Analysis (from the SQL Prompt menu) the problem doesn't happen.

If I turn Code Analysis on, and then click on the text, it freezes for a few seconds and then it's okay and I can click elsewhere. When I click again on the query, it hangs indefinitely (while chiding me for "INSERT without column list") (guilty).

It's a problem on SQL Prompt 9.4.4.7199 (also on 9.4.3.7121).

SSMS version:

Microsoft SQL Server Management Studio                        14.0.17285.0
Microsoft Analysis Services Client Tools                        14.0.1016.283
Microsoft Data Access Components (MDAC)                        10.0.17134.1
Microsoft MSXML                        3.0 6.0
Microsoft Internet Explorer                        9.11.17134.0
Microsoft .NET Framework                        4.0.30319.42000
Operating System                        6.3.17134

Thanks,

Marek

marekprz November 21, 2018 18:46

Comments

12 comments

Hi marekprz

Thank you for your forum post.

I have known the Code Analysis to cause performance problems for some users.

Using SSMS V17.7 (14.0.172540.0) with SQL Prompt V9.3.1.6690 and V9.4.4.7199 I cannot replicate the problem you have described in your post.

Also using SSMS V17.9.1 (14.0.17289.0) with SQL Prompt V9.4.4.7199, I am unable to reproduce your error.

If you enable the verbose logging - SQL Prompt menu ->Help ->Enable Verbose Logging. Are there any entries in the SQL Prompt log file SQL Prompt menu ->Help ->Show Log that indicate where the problem might be occurring for you?

Many Thanks
Eddie

Eddie D November 23, 2018 14:17

0
Thanks for looking into this.

I'm having a difficult time posting a response with log file rows. It says it accepts my post, but my post returns as a draft.

It looks like this might be similar to https://forum.red-gate.com/discussion/84356, so I'll follow that one for info.

marekprz November 26, 2018 17:13

0

Looks like some dependencies aren't loading, right?

This should be from several clicks on the code above.

Looks like it doesn't actually get hung up indefinitely.

<div>26 Nov 2018 15:10:39,776 [1] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6117] Started task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))>'.</div>26 Nov 2018 15:10:40,088 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:10:40,104 [33] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.AsyncWork<AlwaysSkipWorkInput, IList<AnalysisIssue>> - [6117] Run 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))>' completion handler!<br>26 Nov 2018 15:10:40,104 [33] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6117] Successfully finishing current task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))>'.<br>26 Nov 2018 15:10:42,026 [49] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6186] Started task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.<br>26 Nov 2018 15:10:42,151 [53] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.AsyncWork<AlwaysSkipWorkInput, IList<AnalysisIssue>> - [6186] Run 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>' completion handler!<br>26 Nov 2018 15:10:42,151 [53] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6186] Successfully finishing current task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.<br>26 Nov 2018 15:10:42,698 [58] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6231] Started task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.<br>26 Nov 2018 15:10:42,698 [37] DEBUG RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.TfsExtendedClientDllLoader - looking in Dependencies\TFS\ExtendedClient folder: installPath: C:\Program Files (x86)\Red Gate\SQL Source Control 6, relativeAssemblyPath: Dependencies\TFS\ExtendedClient\Microsoft.SqlServer.TransactSql.ScriptDom.resources.dll, workingDir: C:\WINDOWS\system32, assemblyPath: C:\Program Files (x86)\Red Gate\SQL Source Control 6\Dependencies\TFS\ExtendedClient\Microsoft.SqlServer.TransactSql.ScriptDom.resources.dll, exists: False<br>26 Nov 2018 15:10:42,698 [37] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Runtime failed to load assembly Microsoft.SqlServer.TransactSql.ScriptDom.resources, Version=14.0.0.0, Culture=en-US, PublicKeyToken=89845dcd8080cc91, let's have a go<br>26 Nov 2018 15:10:42,698 [37] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Failed to load assembly Microsoft.SqlServer.TransactSql.ScriptDom.resources, Version=14.0.0.0, Culture=en-US, PublicKeyToken=89845dcd8080cc91 from C:\Program Files (x86)\Red Gate\SQL Source Control 6\Microsoft.SqlServer.TransactSql.ScriptDom.resources.dll<br>26 Nov 2018 15:10:42,714 [37] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Failed to load assembly Microsoft.SqlServer.TransactSql.ScriptDom.resources, Version=14.0.0.0, Culture=en-US, PublicKeyToken=89845dcd8080cc91<br>26 Nov 2018 15:10:42,714 [37] DEBUG RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.TfsExtendedClientDllLoader - looking in Dependencies\TFS\ExtendedClient folder: installPath: C:\Program Files (x86)\Red Gate\SQL Source Control 6, relativeAssemblyPath: Dependencies\TFS\ExtendedClient\Microsoft.SqlServer.TransactSql.ScriptDom.resources.dll, workingDir: C:\WINDOWS\system32, assemblyPath: C:\Program Files (x86)\Red Gate\SQL Source Control 6\Dependencies\TFS\ExtendedClient\Microsoft.SqlServer.TransactSql.ScriptDom.resources.dll, exists: False<br>26 Nov 2018 15:10:42,714 [37] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Runtime failed to load assembly Microsoft.SqlServer.TransactSql.ScriptDom.resources, Version=14.0.0.0, Culture=en-US, PublicKeyToken=89845dcd8080cc91, let's have a go<br>26 Nov 2018 15:10:42,714 [37] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Failed to load assembly Microsoft.SqlServer.TransactSql.ScriptDom.resources, Version=14.0.0.0, Culture=en-US, PublicKeyToken=89845dcd8080cc91 from C:\Program Files (x86)\Red Gate\SQL Source Control 6\Microsoft.SqlServer.TransactSql.ScriptDom.resources.dll<br>26 Nov 2018 15:10:42,714 [37] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Failed to load assembly Microsoft.SqlServer.TransactSql.ScriptDom.resources, Version=14.0.0.0, Culture=en-US, PublicKeyToken=89845dcd8080cc91<br>26 Nov 2018 15:10:43,011 [37] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.AsyncWork<AlwaysSkipWorkInput, IList<AnalysisIssue>> - [6231] Run 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>' completion handler!<br>26 Nov 2018 15:10:43,011 [37] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6231] Successfully finishing current task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.<br>26 Nov 2018 15:10:44,026 [50] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6272] Started task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.<br>26 Nov 2018 15:10:44,151 [53] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.AsyncWork<AlwaysSkipWorkInput, IList<AnalysisIssue>> - [6272] Run 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>' completion handler!<br>26 Nov 2018 15:10:44,151 [53] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6272] Successfully finishing current task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.<br>26 Nov 2018 15:10:51,949 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:10:51,949 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 50557452<br>26 Nov 2018 15:11:07,315 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:07,331 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:07,331 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:07,331 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 50557452<br>26 Nov 2018 15:11:07,331 [1] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6541] Started task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.<br>26 Nov 2018 15:11:07,331 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:07,659 [42] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.AsyncWork<AlwaysSkipWorkInput, IList<AnalysisIssue>> - [6541] Run 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>' completion handler!<br>26 Nov 2018 15:11:07,659 [42] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6541] Successfully finishing current task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.<br>26 Nov 2018 15:11:08,784 [1] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Runtime failed to load assembly RedGate.Honeycomb.Wpf.resources, Version=3.2.1.220, Culture=en-US, PublicKeyToken=7f465a1c156d4d57, let's have a go<br>26 Nov 2018 15:11:08,800 [1] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Failed to load assembly RedGate.Honeycomb.Wpf.resources, Version=3.2.1.220, Culture=en-US, PublicKeyToken=7f465a1c156d4d57 from C:\Program Files (x86)\Red Gate\SQL Source Control 6\RedGate.Honeycomb.Wpf.resources.dll<br>26 Nov 2018 15:11:08,800 [1] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Failed to load assembly RedGate.Honeycomb.Wpf.resources, Version=3.2.1.220, Culture=en-US, PublicKeyToken=7f465a1c156d4d57<br>26 Nov 2018 15:11:08,800 [1] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Runtime failed to load assembly RedGate.Honeycomb.Wpf.resources, Version=3.2.1.220, Culture=en-US, PublicKeyToken=7f465a1c156d4d57, let's have a go<br>26 Nov 2018 15:11:08,800 [1] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Failed to load assembly RedGate.Honeycomb.Wpf.resources, Version=3.2.1.220, Culture=en-US, PublicKeyToken=7f465a1c156d4d57 from C:\Program Files (x86)\Red Gate\SQL Source Control 6\RedGate.Honeycomb.Wpf.resources.dll<br>26 Nov 2018 15:11:08,800 [1] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Failed to load assembly RedGate.Honeycomb.Wpf.resources, Version=3.2.1.220, Culture=en-US, PublicKeyToken=7f465a1c156d4d57<br>26 Nov 2018 15:11:08,800 [1] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Runtime failed to load assembly RedGate.Honeycomb.Wpf.resources, Version=3.2.1.220, Culture=en, PublicKeyToken=7f465a1c156d4d57, let's have a go<br>26 Nov 2018 15:11:08,800 [1] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Failed to load assembly RedGate.Honeycomb.Wpf.resources, Version=3.2.1.220, Culture=en, PublicKeyToken=7f465a1c156d4d57 from C:\Program Files (x86)\Red Gate\SQL Source Control 6\RedGate.Honeycomb.Wpf.resources.dll<br>26 Nov 2018 15:11:08,800 [1] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Failed to load assembly RedGate.Honeycomb.Wpf.resources, Version=3.2.1.220, Culture=en, PublicKeyToken=7f465a1c156d4d57<br>26 Nov 2018 15:11:08,800 [1] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Runtime failed to load assembly RedGate.Honeycomb.Wpf.resources, Version=3.2.1.220, Culture=en, PublicKeyToken=7f465a1c156d4d57, let's have a go<br>26 Nov 2018 15:11:08,800 [1] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Failed to load assembly RedGate.Honeycomb.Wpf.resources, Version=3.2.1.220, Culture=en, PublicKeyToken=7f465a1c156d4d57 from C:\Program Files (x86)\Red Gate\SQL Source Control 6\RedGate.Honeycomb.Wpf.resources.dll<br>26 Nov 2018 15:11:08,800 [1] INFO&nbsp; RedGate.SQLSourceControl.Engine.Utils.DependencyResolution.CustomAssemblyResolver - Failed to load assembly RedGate.Honeycomb.Wpf.resources, Version=3.2.1.220, Culture=en, PublicKeyToken=7f465a1c156d4d57<br>26 Nov 2018 15:11:11,394 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:11,394 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 50557452<br>26 Nov 2018 15:11:13,310 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:13,310 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:13,310 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:13,310 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 50557452<br>26 Nov 2018 15:11:13,310 [1] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [8910] Started task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.<br>26 Nov 2018 15:11:13,310 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:13,669 [19] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.AsyncWork<AlwaysSkipWorkInput, IList<AnalysisIssue>> - [8910] Run 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>' completion handler!<br>26 Nov 2018 15:11:13,669 [19] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [8910] Successfully finishing current task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.<br>26 Nov 2018 15:11:15,856 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:15,856 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 50557452<br>26 Nov 2018 15:11:19,091 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:19,091 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:19,091 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:19,091 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 50557452<br>26 Nov 2018 15:11:19,091 [1] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [9040] Started task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.<br>26 Nov 2018 15:11:19,247 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:19,263 [18] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.AsyncWork<AlwaysSkipWorkInput, IList<AnalysisIssue>> - [9040] Run 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>' completion handler!<br>26 Nov 2018 15:11:19,263 [18] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [9040] Successfully finishing current task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.<br>26 Nov 2018 15:11:21,966 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:21,966 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 50557452<br>26 Nov 2018 15:11:23,045 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:23,045 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:23,045 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:23,060 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 50557452<br>26 Nov 2018 15:11:23,060 [1] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [9121] Started task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.<br>26 Nov 2018 15:11:23,232 [1] DEBUG RedGate.SQLPrompt.CommonUI.Utils.UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection<br>26 Nov 2018 15:11:23,263 [19] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.AsyncWork<AlwaysSkipWorkInput, IList<AnalysisIssue>> - [9121] Run 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>' completion handler!<br>26 Nov 2018 15:11:23,263 [19] INFO&nbsp; RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [9121] Successfully finishing current task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.