2

we have a Tuesday/Thursday Maint window for one application we support. On Tuesdays we modify one facility and thursday if there are no errors we roll all the changes out to the other facilities. The application is 3ed party, and at times makes changes to the databases which render views invalid on our "reporting" server for the application.

Is there a way I can roll through all the views on my report server (There are about 70 of them) with TSQL to find the Binding Errors after Tuesdays Depolyment?

Errors I would like to find are:

Executed as user: <>. Statement(s) could not be prepared. [SQLSTATE 42000] (Error 8180) Could not use view or function 'PAT.dbo.Reorders' because of binding errors. [SQLSTATE 42000] (Error 4413) All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. [SQLSTATE 42000] (Error 205). The step failed.

Is there a way to do this programatically, or do I just have to keep right clicking each view and trying to see if it comes back with data?

Thanks,

6

Something like the following should be good enough to get you started. It should be quite lightweight as well.

DECLARE @Name       NVARCHAR( MAX ),
        @SQL        NVARCHAR( MAX );

DECLARE @t_BindingErrors TABLE
(
    ViewName        NVARCHAR( MAX ),
    ErrorMessage    NVARCHAR( MAX )
);

DECLARE c CURSOR 
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT  '[' + ss.name + '].[' + so.NAME + ']'
    FROM    sys.schemas ss
    INNER JOIN sys.objects so
        ON  ss.schema_id = so.schema_id
    WHERE   so.type = 'V';
OPEN    c;
FETCH NEXT FROM c 
INTO    @Name;

WHILE ( @@FETCH_STATUS = 0 )
BEGIN
    SET @SQL = '
        DECLARE @i  INTEGER;
        SELECT  TOP ( 0 ) @i = 1
        FROM    ' + @Name + ';';
    BEGIN TRY
        EXECUTE dbo.sp_executesql @statement = @SQL;
    END TRY BEGIN CATCH
        INSERT INTO @t_BindingErrors
        VALUES ( @Name, ERROR_MESSAGE() );
    END CATCH;  

    FETCH NEXT FROM c 
    INTO    @Name;
END;

CLOSE   c;
DEALLOCATE c;

SELECT  *
FROM    @t_BindingErrors;
0

You can generate SELECT statements for all the views in a database and just run the results:

SELECT 'SELECT * FROM dbo.' + name from sys.sysobjects WHERE type = 'V'

Which should return

SELECT * FROM dbo.xxxx
SELECT * FROM dbo.yyyy
etc...

For all the views in the current database. Customize as needed for a different schema or columns to SELECT to avoid returning huge data sets.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.