SQL Server 2008 - sp_refreshview bombing out on some views

I've inherited a fairly substantial project, which makes extensive use of SQL Server (2005 and 2008) views.

One step in the build process is the call the sp_refreshviews system stored procedure, to make sure, no changes on any tables have broken our views. This works fine .... except for about three or four (out of 200+) views....

With those, it just bombs out - gives odd error messages like

Msg 15165, Level 16, State 1, Procedure sp_refreshsqlmodule_internal, Line 55

Could not find object 'vYourViewNameHere' or you do not have permission.

which is dead wrong - that view does exist, and I definitely can select from it.

I cannot seem to find any good concise information about why this happens, what triggers it... any ideas? Is there anything I could do to detect such problematic views? Can I change their definitino s开发者_运维技巧o that they'd be refreshable again?

Update: I logged a bug report on Microsoft Connect for this - if you agree this seems odd and needs to be fixed, please vote for it!

https://connect.microsoft.com/SQLServer/feedback/details/676728/sp-refreshview-crashes-with-misleading-error-on-views-with-schemabinding

I noticed in the comments you mention it has SCHEMABINDING. I can almost guarantee that is the issue. Books online specifically says this is for use on non-schema bound views.

A scheme-bound view wouldn't allow a breaking change to occur so updating the meta-data is un-necessary. You can safely skip it.

You can identify all the schemabound views like this:

SELECT * FROM sys.views WHERE OBJECTPROPERTY(object_id, 'IsSchemaBound')=1

I ran into the same error when using sp_helptext. In my case the cause was using sp_rename to rename the view. The following code reproduces this error.

create view demo as select dummy = 1
go

exec sp_rename 'demo', 'new_demo'
go

exec sp_refreshview 'new_demo'
go

The only solution is to manually alter the view. Apply this fix to the above solution and you get:

create view demo as select dummy = 1
go

exec sp_rename 'demo', 'new_demo'
go

-- This statement fixes the problem
alter view new_demo as select dummy = 1
go

exec sp_refreshview 'new_demo'
go

My incarnation of this error was:

Msg 8116, Level 16, State 1, Procedure sp_refreshsqlmodule_internal, Line 75 Argument data type int is invalid for argument 1 of substring function.

This error message was being reported at various places in the db script. I would say wrong places. If I commented out the SQL this error was reported at, the same error would be reported elsewhere.

I commented out the following call in my script as a workaround, and the script would complete successfully.

-- EXECUTE sp_refreshview @viewName;

Note: My database didn't report having an schemabound views when running the query suggested in RThomas' adjacent answer https://stackoverflow.com/a/6460532/179972

UPDATE - SOLUTION:

After our database script ran successfully with the sp_refreshview command commented out (shown above), we then ran the view refresh code on its own, and it was successful too.

--
This answer doesn't make sense to me as to how it was able to work successfully, however I've documenting it here in case it proves helpful to somebody else.

To find which view is your problem add a print to the normal sppRefreshViews. Nothing earth shattering here, but i thought I would share.

CREATE procedure sppRefreshViews2

    as
    declare @t varchar (1024)

    declare tbl_cur cursor for
    select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW' and table_name like 'sp%'
    OPEN tbl_cur

    FETCH NEXT from tbl_cur INTO @t
    WHILE @@FETCH_STATUS = 0
    BEGIN
    print      @t 
    exec ('sp_refreshview ''' + @t + '''')
    FETCH NEXT from tbl_cur INTO @t
    END

    CLOSE tbl_cur
    DEALLOCATE tbl_Cur