none
Stored Procedure com erro RRS feed

  • Pergunta

  • Bom dia, Pessoal !

        Estou executando uma stored procedure, no banco de dados AdventureWorks2008R2, e esta dando o seguinte erro:

        msg error level 16 ddl_error

        O que pode ser este erro, segue script abaixo:

        

    use AdventureWorks2008R2
    GO

    CREATE PROCEDURE dbo.asp_reindex @database SYSNAME, @fragpercent INT
    AS
    DECLARE @cmd NVARCHAR(max),
    @table SYSNAME,
    @schema SYSNAME

    --Using a cursor for demonstration purposes.
    --Could also do this with a table variable and a WHILE loop
    DECLARE curtable CURSOR FOR
    SELECT DISTINCT OBJECT_SCHEMA_NAME(object_id, database_id) SchemaName,
    OBJECT_NAME(object_id,database_id) TableName
    FROM sys.dm_db_index_physical_stats (DB_ID(@database),NULL,NULL,NULL,'SAMPLED')
    WHERE avg_fragmentation_in_percent >= @fragpercent
    FOR READ ONLY
    OPEN curtable
    FETCH curtable INTO @schema, @table
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @cmd = 'ALTER INDEX ALL ON ' + @database + '.' + @schema + '.' + @table
    + ' REBUILD WITH (ONLINE = ON)'
    --Try ONLINE build first, if failure, change to OFFLINE build.
    BEGIN TRY
    EXEC sp_executesql @cmd
    END TRY
    BEGIN CATCH
    BEGIN
    SET @cmd = 'ALTER INDEX ALL ON ' + @database + '.' + @schema + '.'
    + @table + ' REBUILD WITH (ONLINE = OFF)'
    EXEC sp_executesql @cmd
    END
    END CATCH
    FETCH curtable INTO @schema, @table
    END
    CLOSE curtable
    DEALLOCATE curtable
    GO

    Obrigado.

    sexta-feira, 6 de setembro de 2013 13:17

Respostas