x

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

Hi,

We have a database that keeps track of lots and lots of objects. We have three views, all of which have "Object" in their name (these three are the only objects in our DB that have "Object" in their name). I created an update script through VS 2010 and get the following errors:

Refreshing [dbo].[viewObjectPublishAdds]...
Msg 208, Level 16, State 6, Procedure sp_refreshsqlmodule_internal, Line 75
Invalid object name 'dbo.viewPublishAdds'.

(1 row(s) affected)
Refreshing [dbo].[viewObjectPublishChanges]...
Msg 208, Level 16, State 6, Procedure sp_refreshsqlmodule_internal, Line 75
Invalid object name 'dbo.viewPublishChanges'.

(1 row(s) affected)
Refreshing [dbo].[viewObjectPublishDeletes]...
Msg 208, Level 16, State 6, Procedure sp_refreshsqlmodule_internal, Line 75
Invalid object name 'dbo.viewPublishDeletes'.

(1 row(s) affected)

Now, notice how the word "Object" is not in the error when it complains about the view name and is when it tells us what the view name is. Those were the only three errors.

When I looked at sp_refreshsqlmodule, I notices that "OBJECT" is the value of one of its internal variables, but I couldn't track down how else it is used. At this point, it would be quite difficult to change the names of these objects.

Is this a strange coincidence, something that everyone else knows about that I should have or something that can be easily worked around?

Thanks, Rob
more ▼

asked May 02, 2011 at 12:01 PM in Default

visualsi gravatar image

visualsi
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

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

----
more ▼

answered May 03, 2011 at 06:00 AM

visualsi gravatar image

visualsi
11 1 1 1

Renaming an object doesn't change it's stored creation DDL. When you script it, the client has to detect old names and correct them. That's certainly what I had to do in the IDE that I wrote...
May 03, 2011 at 08:02 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered May 03, 2011 at 04:56 AM

visualsi gravatar image

visualsi
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

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][1].

[1]: http://connect.microsoft.com/SQLServer/feedback/details/656863/sp-refreshsqlmodule-corrupts-renamed-objects-definitions
more ▼

answered May 03, 2011 at 12:05 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

@Matt Whitfield Very good answer! The lesson learned should be that sp_rename should be used with caution :)
May 03, 2011 at 08:15 AM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x415

asked: May 02, 2011 at 12:01 PM

Seen: 2689 times

Last Updated: May 02, 2011 at 12:01 PM