SSMS Hangs with certain INSERT statement
marekprz
Posts: 11 Bronze 1
in SQL Prompt
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
Tagged:
Best Answer
-
Eddie D
Posts: 1,806 Rose Gold 5
Hi Marek
That's great news that V9.4.5.7296 resolves the reported problem. Thank you for bring the problem to our attention and sorry for any inconvenience caused.
Many Thanks
Eddie
Answers
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
26 Nov 2018 15:10:40,104 [33] INFO RedGate.SqlPrompt.Engine.AsyncExecution.AsyncWork<AlwaysSkipWorkInput, IList<AnalysisIssue>> - [6117] Run 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))>' completion handler!
26 Nov 2018 15:10:40,104 [33] INFO RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6117] Successfully finishing current task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))>'.
26 Nov 2018 15:10:42,026 [49] INFO RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6186] Started task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.
26 Nov 2018 15:10:42,151 [53] INFO RedGate.SqlPrompt.Engine.AsyncExecution.AsyncWork<AlwaysSkipWorkInput, IList<AnalysisIssue>> - [6186] Run 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>' completion handler!
26 Nov 2018 15:10:42,151 [53] INFO RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6186] Successfully finishing current task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.
26 Nov 2018 15:10:42,698 [58] INFO RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6231] Started task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.
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
26 Nov 2018 15:10:42,698 [37] INFO 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
26 Nov 2018 15:10:42,698 [37] INFO 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
26 Nov 2018 15:10:42,714 [37] INFO 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
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
26 Nov 2018 15:10:42,714 [37] INFO 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
26 Nov 2018 15:10:42,714 [37] INFO 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
26 Nov 2018 15:10:42,714 [37] INFO 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
26 Nov 2018 15:10:43,011 [37] INFO RedGate.SqlPrompt.Engine.AsyncExecution.AsyncWork<AlwaysSkipWorkInput, IList<AnalysisIssue>> - [6231] Run 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>' completion handler!
26 Nov 2018 15:10:43,011 [37] INFO RedGate.SqlPrompt.Engine.AsyncExecution.WorkAsyncExecutor - [6231] Successfully finishing current task 'Code analysis task <SQLQuery1.sql - <SERVERNAME>.master (<AD USER> (59))*>'.
I suspect your problem maybe related to bug SP-7365. The good news is that this bug is now fixed in V9.4.5.7296 which can be downloaded using this link.
Please upgrade and confirm if this problem is resolved for you.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com