October 5, 2018 at 11:07 am
SQL Version:
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Aug 19 2014 12:21:34
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Used sp_RefreshSQLModule to identify objects that would not compile.
In 2 cases for views this command completely changed the view definition. it was like there is some cached view definition that this command used to re-write the view. It added commented out lines that were not in the view previously and dropped a column from the select.
From what i read it's primarily used to update meta-data in case an underlying object changed.
Can anyone shed some light on why this happened and what this command ACTUALLY does?
October 5, 2018 at 12:10 pm
Tom Van Harpen - Friday, October 5, 2018 11:07 AMSQL Version:
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Aug 19 2014 12:21:34
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)Used sp_RefreshSQLModule to identify objects that would not compile.
In 2 cases for views this command completely changed the view definition. it was like there is some cached view definition that this command used to re-write the view. It added commented out lines that were not in the view previously and dropped a column from the select.From what i read it's primarily used to update meta-data in case an underlying object changed.
Can anyone shed some light on why this happened and what this command ACTUALLY does?
Changes to the underlying structures of views, stored procedures etc can change and may not be reflected in the dependent objects. You can create a view for a table with two columns that does something like Select * from tableA. Select from that view and it pulls the table data. Add a column to that table and that column will not show up in the view until the view is refreshed. Before the refresh, you will see just the original two columns.
So the meta data needs to be updated as you already read. In terms of the specifics of what it does exactly....sp_refreshsqlmodule (or sp_refreshview) call an undocumented stored procedure named sys.sp_refreshsqlmodule_internal
The code for sp_refreshsqlmodule_internal that does the actual refresh is: -- REFRESH MODULE DEFINITION
EXEC %%Module(ID = @objid).Refresh(NameSpaceClass = @ns)
So what is actually happening when it's executed is internal to Microsoft.
Sue
October 5, 2018 at 12:31 pm
Thanks Sue,
I understand the part about select * in a view (which we never use) and how changing a table won't be reflected in the view.
But what happened here is the command replaced the view definition with a completely different one. It used an old version of the view that we had not used in a long time.
I'm going to restore the db and run Object_definition and see if it gives me something different that what the view currently is.
I will say after doing an Alter View then the refresh command ran ok without changing the view.
I guess what this comes down to is whatever SQL had for the view def didn't match what the view really was. It seems the refresh command just rebuilds the view based on the saved definition.
October 5, 2018 at 2:17 pm
Tom Van Harpen - Friday, October 5, 2018 12:31 PMThanks Sue,
I understand the part about select * in a view (which we never use) and how changing a table won't be reflected in the view.
But what happened here is the command replaced the view definition with a completely different one. It used an old version of the view that we had not used in a long time.I'm going to restore the db and run Object_definition and see if it gives me something different that what the view currently is.
I will say after doing an Alter View then the refresh command ran ok without changing the view.
I guess what this comes down to is whatever SQL had for the view def didn't match what the view really was. It seems the refresh command just rebuilds the view based on the saved definition.
That would be a pretty huge bug if it's refreshed with the incorrect definition.
Any chance someone else could have been trying to address issues with the view?
Sue
October 5, 2018 at 2:32 pm
Fwiw, I usually stick to sp_refreshview for views, and only use the broader module proc when I need to.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 5, 2018 at 2:54 pm
There's only a few of us here. These views have been in use for some time.
So now i'm pretty sure that renaming views in SSMS is the culprit.
I have a view named GetSalesData and a view named GetSalesData_WORegionID
What probably happened is one existed it was modified using a create statement and given the new name and the other view was simply renamed.
When i execute this:SELECT OBJECT_DEFINITION(OBJECT_ID('[fact].[GetSalesData_WORegionID]'))
I get this result
CREATE view [fact].[GetSalesData] AS SELECT ....
When i did the RefreshSQLModule it used the definition from before the rename, thereby reverting the view to an old version.
October 5, 2018 at 2:59 pm
ScottPletcher - Friday, October 5, 2018 2:32 PMFwiw, I usually stick to sp_refreshview for views, and only use the broader module proc when I need to.
Yeah i haven't tried that, from what i read in BOL they function identically for views. RefreshSQLModule just has the added Class parm in case you are doing server or db triggers.
I get the same result with both.
October 5, 2018 at 3:39 pm
Tom Van Harpen - Friday, October 5, 2018 2:54 PMThere's only a few of us here. These views have been in use for some time.
So now i'm pretty sure that renaming views in SSMS is the culprit.I have a view named GetSalesData and a view named GetSalesData_WORegionID
What probably happened is one existed it was modified using a create statement and given the new name and the other view was simply renamed.
When i execute this:
SELECT OBJECT_DEFINITION(OBJECT_ID('[fact].[GetSalesData_WORegionID]'))I get this result
CREATE view [fact].[GetSalesData] AS SELECT ....
When i did the RefreshSQLModule it used the definition from before the rename, thereby reverting the view to an old version.
When you change the name in SSMS, it executes sp_rename. And sp_rename comes with a set of it's own issues explained in the documentation:
sp_rename (Transact-SQL)
Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object either in the definition column of the sys.sql_modules catalog view or obtained using the OBJECT_DEFINITION built-in function. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.
So you were heading down the right path on that one - it's the rename that caused the issues.
Sue
October 8, 2018 at 7:27 am
Sue_H - Friday, October 5, 2018 3:39 PMTom Van Harpen - Friday, October 5, 2018 2:54 PMThere's only a few of us here. These views have been in use for some time.
So now i'm pretty sure that renaming views in SSMS is the culprit.I have a view named GetSalesData and a view named GetSalesData_WORegionID
What probably happened is one existed it was modified using a create statement and given the new name and the other view was simply renamed.
When i execute this:
SELECT OBJECT_DEFINITION(OBJECT_ID('[fact].[GetSalesData_WORegionID]'))I get this result
CREATE view [fact].[GetSalesData] AS SELECT ....
When i did the RefreshSQLModule it used the definition from before the rename, thereby reverting the view to an old version.
When you change the name in SSMS, it executes sp_rename. And sp_rename comes with a set of it's own issues explained in the documentation:
sp_rename (Transact-SQL)
Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object either in the definition column of the sys.sql_modules catalog view or obtained using the OBJECT_DEFINITION built-in function. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.So you were heading down the right path on that one - it's the rename that caused the issues.
Sue
Well yep that explains it!
Learned something new, actually 2 things. The first is don't rename stuff, and the second is that SQL keeps 2 definitions of objects. One you get when you do a Script as Create/Alter and the other from sys.sql_modules/Object_definition.
Thanks for your help Sue!
October 8, 2018 at 8:07 am
I don't use sp_rename so I've not run into this but, good lord!!! What a land mine MS put into their code. Oh sure, it's "documented"...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2018 at 12:08 pm
EDIT: What actually happened is running refresh on a view changed a different view, different from what the post title says.
Created a script to find mismatching entries between sys.all_objects and sys.sql_module.
I adapted that to make it a little demo script...
-- drop the test views
IF EXISTS ( SELECT 1 FROM sys.all_objects WHERE object_id = OBJECT_ID('dbo.TestView1') )
DROP VIEW dbo.TestView1;
GO
IF EXISTS ( SELECT 1 FROM sys.all_objects WHERE object_id = OBJECT_ID('dbo.TestView2') )
DROP VIEW dbo.TestView2;
GO
IF EXISTS ( SELECT 1 FROM sys.all_objects WHERE object_id = OBJECT_ID('dbo.TestView1_Rename') )
DROP VIEW dbo.TestView1_Rename;
GO
-- create the test views
CREATE VIEW dbo.TestView1
AS
(SELECT
1 AS mycol);
GO
CREATE VIEW dbo.TestView2
AS
(SELECT
9999 AS MyColumnName);
GO
-- rename testview1 to TestView1_Rename
EXEC sys.sp_rename
@objname = N'dbo.TestView1'
,@newname = N'TestView1_Rename'
,@objtype = 'OBJECT';
GO
-- rename testview2 to testview1
EXEC sys.sp_rename
@objname = N'dbo.TestView2'
,@newname = N'TestView1'
,@objtype = 'OBJECT';
GO
-- find object definitions that dont have the object name within the definition according to all object names.
-- Now this is not the best approach since any occurrence of that name within the def will exclude the result.
-- but its a start.
SELECT
ao.name AS AllObjectsName
,ao.object_id AS AllObjectsID
,asm.definition AS SQLModuleDefinition
,asm.object_id AS SQLModuleID
FROM
sys.all_sql_modules asm
JOIN
sys.all_objects ao
ON ao.object_id = asm.object_id
WHERE
asm.definition NOT LIKE '%' + ao.name + '%';
-- SSMS now shows TestView1 and TestView1_Rename
-- TestView1 shows the definition of TestView2 (as it should since it was renamed from that)
-- Script TestView1 as create to new query window give this:
/*
CREATE VIEW [dbo].[TestView1]
AS
(SELECT
9999 AS MyColumnName);
*/
-- Try to run the refresh command on TestView1
/*
EXEC sys.sp_refreshsqlmodule
@name = N'dbo.TestView1'
*/
-- Result: Msg 208, Level 16, State 6, Procedure sp_refreshsqlmodule_internal, Line 75 [Batch Start Line 66]
--Invalid object name 'dbo.TestView2'.
-- now run the refresh command on the renamed TestView1
/*
EXEC sys.sp_refreshsqlmodule
@name = N'dbo.TestView1_Rename'
*/
-- now check testview1.... The refresh of TestView1_Rename actually changed TestView1 to a something completely different.
-- Script as Create to new query window:
/*
CREATE VIEW [dbo].[TestView1]
AS
(SELECT
1 AS mycol);
*/
October 9, 2018 at 1:46 pm
+ 1 informative!
Thanks for the heads up!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply