Using sys.sp_refreshsqlmodule with objects that have the string 'OBJECT' embedded in their name

Hi,

It looks like you were right. It looks like SQL Server didn't properly change its internal tables when the view name was changed.

In the database itself, there are a few references to the same object_id (referring to the view):

• sys.views has the new name correctly spelled
• in sql_modules, there is a CREATE that has the name of the table incorrectly spelled
• same thing with all_sql_modules - it has the same bad CREATE
• in sys.objects, the new name is spelled correctly

Hmm, can I assume that SQL Server does not know how to properly rename an object in that it only changes the name but does not change the "CERATE" that it stores for when it might need to re-create the table? Or, is the lesson that I should never trust that CREATE? (I assume that when I right-click on an object and ask it to "script a CREATE to a new window", it copies this string to the window...)

Thanks... Rob

OK...

PRINT N'Refreshing [dbo].[viewObjectPublishAdds]...';
GO
EXECUTE sp_refreshview N'dbo.viewObjectPublishAdds';
GO
PRINT N'Refreshing [dbo].[viewObjectPublishChanges]...';
GO
EXECUTE sp_refreshview N'dbo.viewObjectPublishChanges';
GO
PRINT N'Refreshing [dbo].[viewObjectPublishDeletes]...';
GO
EXECUTE sp_refreshview N'dbo.viewObjectPublishDeletes';

This script was generated by Visual Studio 2010. Thanks.

Can you post the script that was generated, as well as the error message? Because the 'refreshing' bit is generated by 'PRINT' statements in the script. So it may well be an error in the generation of the script, and not with the system proc. Have you tried running sp_refreshsqlmodule manually? OBJECT isn't one of it's internal variables, merely the default for the scope variable.

Edit -> OK, after looking at your comment, I believe what has happened is that the object was originally called dbo.viewPublishAdds, and then at some point, somebody used sp_rename to rename the object to dbo.viewObjectPublishAdds. Certainly I can re-create the behaviour you see by using the following code:

DROP VIEW testObjectView
GO
CREATE VIEW testView AS SELECT * FROM [sys].[objects]
GO
[sp_rename] 'testView', 'testObjectView'
GO
[sp_refreshview] N'dbo.testObjectView'

Your best bet is to script out the object as an ALTER, and re-create it that way. You will find that this is a known issue, and Microsoft issued that advice as a work arround in this connect item.