Sql Refactor crashes SSMS on this query

SvdSinner

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?

JonathanWatts

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

SvdSinner

I'm using Sql Refactor 1.1.0.97, SSMS version 9.00.3042.00, and Windows XPSP2

JonathanWatts

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

SvdSinner

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.

JonathanWatts

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

SvdSinner

All SQL Refactor functions now lock up SSMS. The only installations that have happened this week were the basic MS patches from patch Tuesday.

JonathanWatts

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

SvdSinner

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.

JonathanWatts

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

SvdSinner

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.

tgoldstein@co.arapahoe

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

JonathanWatts

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

tgoldstein@co.arapahoe

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

jerryhung

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