Error in SQL Prompt 3.81

jules140

Hi,

I'm trying to run SQL Prompt 3.81 in SSMS 2005 SP2 (running on XP Pro SP2) and keep getting the following error whilst trying to Lay Out SQL:

Exception while processing command Redgate.SQLPrompt.SSSUI.LayOutSQL

System.ApplicationException: Exception within progress task ---> System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.VisualStudio.TextManager.Interop.IVsTextView'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{BB23A14B-7C61-469A-9890-A95648CED5E6}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).
at Microsoft.VisualStudio.TextManager.Interop.IVsTextView.GetSelection(Int32& piAnchorLine, Int32& piAnchorCol, Int32& piEndLine, Int32& piEndCol)
at RedGate.SQLPrompt.CommonVS.Editor.VSEditorWindow.get_Selection()
at RedGate.SQLPrompt.CommonUI.Refactor.Refactor.a(EditorWindowBase , b , c& )
at RedGate.SQLPrompt.CommonUI.Refactor.Refactor.a(EditorWindowBase , c& , b[] )
at RedGate.SQLPrompt.CommonUI.Refactor.Refactor.a.Run()
at RedGate.SQLPrompt.CommonUI.Progress.TaskAggregator.Run()
at RedGate.SQLPrompt.CommonUI.Forms.ProgressDialogue.a()
--- End of inner exception stack trace ---
at RedGate.SQLPrompt.CommonUI.Forms.ProgressDialogue.ShowProgress(ITaskRunner task)
at RedGate.SQLPrompt.CommonUI.Refactor.Refactor.LayOutSql(EditorWindowBase editor, SqlPromptOptions options)
at b.Execute()
at RedGate.SQLPrompt.CommonVS.Commands.VSCommandControler.Exec(String CmdName, vsCommandExecOption ExecuteOption, Object& VariantIn, Object& VariantOut, Boolean& Handled)

If I cancel this and try running SQL Refactor, SSMS freezes and has to be closed from Task Manager.

Please find the Stored Procedure below:

ALTER PROCEDURE [dbo].[CMS_Pages_Select_Superseded]

AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;

WITH AllUsers
AS ( SELECT UserID,
UserName
FROM [vw_aspnet_Users]
)
SELECT CMS_PageHistory.PageHistoryID,
CMS_PageHistory.PageID,
CMS_PageHistory.Title,
CMS_PageHistory.Body,
CMS_PageHistory.Locale,
CMS_PageHistory.ParentID,
CMS_PageHistory.PageGuid,
CMS_PageHistory.MenuTitle,
CMS_PageHistory.Roles,
CMS_PageHistory.Summary,
CMS_PageHistory.PageUrl,
CMS_PageHistory.FilePath,
CMS_PageHistory.Keywords,
CMS_PageHistory.CreatedBy,
CMS_PageHistory.CreatedOn,
( SELECT Username
FROM AllUsers
WHERE UserID = [CMS_PageHistory].CreatedBy
) AS CreatedByString,
CMS_PageHistory.ModifiedBy,
CMS_PageHistory.ModifiedOn,
( SELECT Username
FROM AllUsers
WHERE UserID = [CMS_PageHistory].ModifiedBy
) AS ModifiedByString,
CMS_PageHistory.Deleted,
CMS_PageHistory.ChangeReason,
CMS_PageHistory.PageVersion,
CMS_PageHistory.ContentType
FROM CMS_PageHistory
INNER JOIN AllUsers ON CMS_PageHistory.CreatedBy = AllUsers.UserId
ORDER BY dbo.CMS_PageHistory.PageID, dbo.CMS_PageHistory.PageVersion

Any ideas?

Regards,

Jules

jules140

Hi,

Don't worry about this - I think the problem was in VS2008.
I had to do a complete re-install, lost most of the weekend - thanks Microsoft.

Regards

Jules

tanya

Hi Jules,

Sorry for the late response but I am glad that you managed to resolve the issue.
It is bizarre but probably your .NET installation could have been corrupted and so the reinstall fixed the issue! Although I have no proof to convince anyone with my reasoning

Thanks,
Tanya
Project Manager
Red Gate Software Ltd