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

Exception in VS 2010 RC

youngcm2youngcm2 Posts: 14
edited April 28, 2010 11:07AM in SQL Prompt Previous Versions
I am receiving the following exception in VS 2010 RC when I layout sql. I do not receive it in SSMS on the same code.

System.ApplicationException: Exception within progress task ---> System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x80041003
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32 errorCode)
at Microsoft.VisualStudio.ErrorHandler.ThrowOnFailure(Int32 hr, Int32[] expectedHRFailure)
at RedGate.SQLPrompt.CommonVS.Editor.VSEditorWindow.a(CaretPosition , CaretPosition , String )
at RedGate.SQLPrompt.CommonVS.Editor.VSScriptProvider.a(IEnumerable`1 )
at RedGate.SQLPrompt.CommonVS.Editor.VSScriptProvider.ApplyChange(ICompletionFragment fragment)
at RedGate.SQLPrompt.CommonUI.Refactor.Refactor.a(EditorWindowBase , Func`2 , Errors& )
at RedGate.SQLPrompt.CommonUI.Refactor.Refactor.a(EditorWindowBase , Errors& , Func`2[] )
at RedGate.SQLPrompt.CommonUI.Refactor.Refactor.a.Run()
at RedGate.SqlPrompt.Engine.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.a(a )
at RedGate.SQLPrompt.CommonUI.Refactor.Refactor.LayOutSql(LayoutOptions layoutOptions, EditorWindowBase editor)
at b.Execute()
at a.Execute()
at RedGate.SQLPrompt.CommonVS.Commands.VSCommandControler.EnvDTE.IDTCommandTarget.Exec(String CmdName, vsCommandExecOption ExecuteOption, Object& VariantIn, Object& VariantOut, Boolean& Handled)
DECLARE
    @constraint_name SYSNAME ,
    @parent_schema SYSNAME ,
    @parent_name SYSNAME ,
    @referenced_object_schema SYSNAME ,
    @referenced_object_name SYSNAME ,
    @column_name SYSNAME ,
    @referenced_column_name SYSNAME ,
    @is_not_for_replication BIT ,
    @is_not_trusted BIT ,
    @delete_referential_action TINYINT ,
    @update_referential_action TINYINT ,
    @AddLine NVARCHAR(MAX) ,
    @DropLine NVARCHAR(MAX) ,
    @fkline NVARCHAR(MAX) ,
    @pkline NVARCHAR(MAX) ,
    @object_id INT ,
    @parent_object_id INT

DECLARE @AddScript TABLE
    (
      id INT IDENTITY(1, 1) ,
      line NVARCHAR(MAX)
    )
DECLARE @DropScript TABLE
    (
      id INT IDENTITY(1, 1) ,
      line NVARCHAR(MAX)
    )

SET NOCOUNT ON

 -- Create cursor for foreign keys system view
DECLARE cFKeys CURSOR READ_ONLY
FOR 
SELECT object_id, 
parent_object_id, 
OBJECT_SCHEMA_NAME(parent_object_id), 
OBJECT_NAME (parent_object_id), 
[name], 
is_not_trusted, 
OBJECT_SCHEMA_NAME(referenced_object_id), 
OBJECT_NAME(referenced_object_id),
delete_referential_action,
update_referential_action,
is_not_for_replication
FROM sys.foreign_keys
 
OPEN cFKeys
 
 -- Collect basic data
FETCH NEXT FROM cFKeys INTO @object_id, @parent_object_id, @parent_schema, @parent_name, @constraint_name, @is_not_trusted, @referenced_object_schema, @referenced_object_name, @delete_referential_action, @update_referential_action, @is_not_for_replication
WHILE ( @@fetch_status <> -1 ) 
    BEGIN
        IF ( @@fetch_status <> -2 ) 
            BEGIN
 -- Start creating command string. One for add and one for drop constraint
                SET @AddLine = N'ALTER TABLE ' + QUOTENAME(@parent_schema) +
                    N'.' + QUOTENAME(@parent_name)
                SET @DropLine = N'ALTER TABLE ' + QUOTENAME(@parent_schema) +
                    N'.' + QUOTENAME(@parent_name)
 -- Check if it is enabled or not
                IF @is_not_trusted = 1 
                    SET @AddLine = @AddLine + N' WITH NOCHECK'
                ELSE 
                    SET @AddLine = @AddLine + N' WITH CHECK'
 
                SET @AddLine = @AddLine + N' ADD CONSTRAINT ' +
                    QUOTENAME(@constraint_name) + N' FOREIGN KEY (' 
                SET @DropLine = @DropLine + N' DROP CONSTRAINT ' +
                    QUOTENAME(@constraint_name)
 
 -- Gather all columns for current key from foreign key columns system view
                DECLARE cColumns CURSOR READ_ONLY
                FOR 
                SELECT fc.name, pc.name
                FROM sys.foreign_key_columns fk 
                INNER JOIN sys.columns fc ON fk.parent_object_id = fc.object_id AND fk.parent_column_id = fc.column_id
                INNER JOIN sys.columns pc ON fk.referenced_object_id = pc.object_id AND fk.referenced_column_id = pc.column_id
                WHERE parent_object_id = @parent_object_id AND fk.constraint_object_id = @object_id
 
                OPEN cColumns
 
                SET @fkline = N''
                SET @pkline = N''
 
                FETCH NEXT FROM cColumns INTO @column_name, @referenced_column_name
                WHILE ( @@fetch_status <> -1 ) 
                    BEGIN
                        IF ( @@fetch_status <> -2 ) 
                            BEGIN
 -- One line for column list and one for referenced columns
                                SET @fkline = @fkline + @column_name
                                SET @pkline = @pkline +
                                    @referenced_column_name
                            END
                        FETCH NEXT FROM cColumns INTO @column_name, @referenced_column_name
                        IF ( @@fetch_status = 0 ) 
                            BEGIN
                                SET @fkline = @fkline + ', '
                                SET @pkline = @pkline + ', '
                            END
                    END
 
                CLOSE cColumns
                DEALLOCATE cColumns
 -- Add column list
                SET @AddLine = @AddLine + @fkline + N')' + CHAR(13) 
 -- Add referenced table and column list
                SET @AddLine = @AddLine + 'REFERENCES ' +
                    QUOTENAME(@referenced_object_schema) + N'.' +
                    QUOTENAME(@referenced_object_name) 
                SET @AddLine = @AddLine + N' (' + @pkline + N')'
 -- Check the referential action that was declared for this key as well as replication option
                SET @AddLine = @AddLine + ' ON DELETE' +
                    CASE @delete_referential_action
                      WHEN 1 THEN N' CASCADE'
                      WHEN 2 THEN N' SET NULL'
                      WHEN 3 THEN N' SET DEFAULT'
                      ELSE N' NO ACTION'
                    END + ' ON UPDATE' + CASE @UPDATE_REFERENTIAL_ACTION
                                           WHEN 1 THEN N' CASCADE'
                                           WHEN 2 THEN N' SET NULL'
                                           WHEN 3 THEN N' SET DEFAULT'
                                           ELSE N' NO ACTION'
                                         END +
                    CASE WHEN @IS_NOT_FOR_REPLICATION = 1
                         THEN N' NOT FOR REPLICATION'
                         ELSE N''
                    END
 -- Insert command into table for later use
                INSERT  INTO @DropScript
                        SELECT
                            @DropLine
                INSERT  INTO @AddScript
                        SELECT
                            @AddLine
 
                FETCH NEXT FROM cFKeys INTO @object_id, @parent_object_id, @parent_schema, @parent_name, @constraint_name, @is_not_trusted, @referenced_object_schema, @referenced_object_name, @delete_referential_action, @update_referential_action, @is_not_for_replication
            END
    END
 
CLOSE cFKeys
DEALLOCATE cFKeys
 
SET NOCOUNT OFF
 

BEGIN TRANSACTION
BEGIN TRY
    DECLARE @Current INT = 1 ;
    DECLARE @Max INT ;
    DECLARE @Sql VARCHAR(MAX) ;
    
    --drop statements
    SELECT
        @Max = MAX(id)
    FROM
        @DropScript ;
 
    WHILE ( @Current <= @Max ) 
        BEGIN
            SELECT
                @Sql = line
            FROM
                @DropScript
            WHERE
                id = @Current ;
            
            PRINT @Sql ;
            --EXEC (@Sql) ;
            
            SET @Current += 1 ;
        END         
        
    --add statements
    SET @Current = 1 ;
    
    SELECT
        @Max = MAX(id)
    FROM
        @AddScript ;
 
    WHILE ( @Current <= @Max ) 
        BEGIN
            SELECT
                @Sql = line
            FROM
                @AddScript
            WHERE
                id = @Current ;
            
            PRINT @Sql ;
            --EXEC (@Sql) ;
            
            SET @Current += 1 ;
        END  
    
END TRY
BEGIN CATCH
    
    SELECT
        ERROR_NUMBER() AS ErrorNumber ,
        ERROR_SEVERITY() AS ErrorSeverity ,
        ERROR_STATE() AS ErrorState ,
        ERROR_PROCEDURE() AS ErrorProcedure ,
        ERROR_LINE() AS ErrorLine ,
        ERROR_MESSAGE() AS ErrorMessage ;

    IF @@TRANCOUNT > 0 
        ROLLBACK TRANSACTION ;

END CATCH ;

IF @@TRANCOUNT > 0 
    COMMIT TRANSACTION ;

Comments

Sign In or Register to comment.