Competition: What’s your favorite Redgate tool? Enter now.

Sql Refactor crashes SSMS on this query

SvdSinnerSvdSinner Posts: 13
edited February 8, 2008 4:12PM in SQL Refactor Previous Versions
If I attempt to use Layout SQL or Uppercase Keywords from Sql Refactor for the following query, SSMS locks up.
WITH CurrentQuery AS
(
SELECT * 
	FROM dbo.spf_GetEntireEntryPointHierarchy(1)
),
Orphans AS
(
SELECT * FROM CurrentQuery
	WHERE parentid NOT IN (SELECT id FROM CurrentQuery)
		AND parentId > 1
)
SELECT * 
	FROM orphans

FWIW, the above query parses correctly in TSQL and returns the expected results.

Is this a bug, or do I have a problem with my install?

Comments

  • Hi there,

    I have tried using the query you submitted and haven't found any problems with SQL Refactor. May I ask which version of SQL Refactor, SSMS and Windows you are using?

    Regards,

    Jonathan
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • I'm using Sql Refactor 1.1.0.97, SSMS version 9.00.3042.00, and Windows XPSP2
  • We haven't noticed any problems like this on this set up previously.

    I take it that this occurs 100% of the time on this file? I know it sounds strange can you copy the text to a new file and try to reformat it again and see if that makes a difference?

    Also are you able to email me your layout options file?

    Regards,

    Jonathan
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • I am realizing that the problem is bigger than this query. Now, anytime I try to run SQL Refactor, it locks up SSMS. I have even tried uninstalling Sql Refactor, re-downloading from your site, and reinstalling. The same behavior is continuing.

    Any suggestions? I'm just about to head into Code Review week (next week) and I don't want to even think about not being able to use my Red Gate tools.
  • Hi there,

    From they way you are talking I gather than SQL Refactor had been working fine, until you noticed this problem with the above query and now things have got worse?

    Can you be a bit more specific, are you referring to any SQL when you use the Layout SQL command or are other commands like Smart Rename, Split table, Uppercase keywords, Find unused variables, etc. also affected?

    Have you installed any applications or service packs recently?

    Regards,

    Jonathan
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • All SQL Refactor functions now lock up SSMS. The only installations that have happened this week were the basic MS patches from patch Tuesday.
  • Hi there,

    I had a chat with our developers on Friday:

    Do you use Prompt? If so is that showing the same problem?

    Also I am informed that Management studio has an issue where it will hang for 30 seconds to 1 minute if we don’t return control to the UI for 10 seconds or more: you can see this happen if you Refactor a very large file. In this case, control does eventually return. How long have you been waiting before deciding Management Studio is hung?

    Regards

    Jonathan
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Sql Prompt is working just fine. It is just SQL Refactor.

    I tried this weekend to see if SSMS would ever return control back to the user, but even after letting it be locked up for > 24 hours, it never returned control back.

    Normally, when software gets buggy like this I reinstall, but as I mentioned, I've already done that and it hasn't improved.
  • Is this problem only occurring on your machine? Are you able to install SQL Refactor on another box and see if using SQL Refactor cause the same problems?

    Do you use SQL Refactor as the same user who installed the application?

    Jonathan
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Yes, it works on my test machine, and (up until last week) it worked flawlessly on my main development machine for many months since I purchased it.

    I was the user who installed it, and it is installed under my user account.

    Please let me know what I can do to get this functioning again.
  • I have started having the same issue with Refactor locking up SSMS. Just started about 2 weeks ago and I have been using Refactor since it came out over a year ago. I am on a Vista 64 box, but have been for over 6 months so that is not the cause. Seems to be random. I do a CTRL+B CTRL+L to format the highlighted code and it works I do it another time and SSMS locks up. When this happens the Refactor progress box is not appearing. If I mange some combination of killing tasks I can get the Refactor progress box to come to the front and it shows some percentage done, but is locked. Once I kill SSMS I restart and usually can recover the script I was working on. If I then try to refactor the script it usually work.

    As I said, this has never happened until the last couple of weeks and now is happening enough times a week that I am almost afraid to use Refactor.

    Tim
    Denver, CO
  • JonathanWattsJonathanWatts Posts: 418
    edited November 21, 2007 12:31PM
    You might try uninstalling SSMS and reinstalling everything from scratch.

    You’ll need to uninstall SQL Refactor, SQL Prompt and then SSMS. Then delete the following registry keys before you attempt to reinstall:

    HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{EAD9E428-F0B0-417C-9624-BB7CB0EF2B9B}
    HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\90\Tools
    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools

    When you reinstall try installing just SQL Refactor and not SQL Prompt at first, just in case his problem is due to a clash between the two programs.

    Regards,

    Jonathan
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Ouch! That is a lot of time lost to do that. Uninstall Client tools, Reinstall Client tools, SQL service pack, Refactor, Prompt.

    As a consultant that is getting paid for productive hours this is a bigger cost than it is worth. Back to hand formatting and will consider my support renewal as a donation to Red Gate.

    Tim
    Denver, CO
  • I have the issue still ever since I started using SQL Refactor

    I have SQL Prompt installed, but no problem there at all

    see the topic I created
    http://www.red-gate.com/messageboard/vi ... php?t=6409

    Red Gate has opened a support ticket for me, let's hope it leads to somewhere

    I have had to kill SqlWb.exe 5~10 times today after Refactor stalls
    Jerry Hung
    DBA, MCITP
Sign In or Register to comment.