January 30, 2017 at 6:07 am
I have created a program that refreshes databases automatically, making sure fresh production data is inside an acceptance database.
Part of this is manually scripting out any custom objects in the database into a file, then re-applying them into the newly created database.
The problem I am facing is that a definition inside SQL_MODULES table shows me (completely) different code than within the view itself.
I will post the query and output below.
SELECT 'DROP '+
CASE WHEN [type] = 'V' THEN 'View'
ELSE CASE WHEN [type] = 'P' THEN 'PROC' ELSE 'FUNCTION' END
END [dr]
, ss.name + '.[' + SO.[name] + ']' [Name]
, CASE WHEN SM.[uses_ansi_nulls] = 1 THEN 'SET ANSI_NULLS ON' + CHAR(10) + ' GO' ELSE '' END [ANSI]
, CASE WHEN SM.[uses_quoted_identifier] = 1 THEN 'SET QUOTED_IDENTIFIER ON' + CHAR(10) + 'GO' ELSE '' END [QUOTED]
, SM.[definition] + CHAR(10) + 'GO' [Definition]
FROM sys.objects SO
INNER JOIN sys.sql_modules SM ON SO.object_id = SM.object_id
INNER JOIN sys.schemas SS ON SS.schema_id = SO.schema_id
WHERE (LEFT(SO.name,2) = '_S' AND [type] = 'P') OR (LEFT(SO.name,2) = 'fn' AND ([type] = 'FN' OR [type] = 'TF')) OR (LEFT(SO.name,1) = '_' AND [type] = 'V')
ORDER BY [Type]
The output shows me this:
Object name: dbo.[_vuVP_Admins]
Definition:
CREATE VIEW dbo._vu_Admins.... (etc)..
As you can see the object name in the definition is missing the 'VP' part of the code.
I don't want to recompile the view (as I suspect this would fix it) and I need to be able to reproduce it.
When I right click on the view and say 'script to clipboard' it all works fine and the definition is proper!
Questions:
- How could this have happened?
- Am I querying the right objects? (Same results in both SQL_Modules and ALL_SQL_Modules.
- How can it be that the definition inside the system table is different from the actual object name?
All object_id's are correct, I'm not looking at a duplicate or something weird like that.
EDIT:
Other findings:
- OBJECT_DEFINITION function also returns the wrong code for the view
- After re-compiling the view the error is gone and the definition is correct.
- I feel that it could have to do with old SSIS packages and predecessors using that to migratie objects on a system level table or someone literally updating SYS.OBJECTS (even though that shouldn't be possible).
- I *feel* that this is a discrepancy that should be thrown during DBCC CHECKDB but it's not.
<solved using SMO>
January 30, 2017 at 11:14 am
bas de zwart - Monday, January 30, 2017 6:07 AMQuestions:
- How could this have happened?
- Am I querying the right objects? (Same results in both SQL_Modules and ALL_SQL_Modules.
- How can it be that the definition inside the system table is different from the actual object name?All object_id's are correct, I'm not looking at a duplicate or something weird like that.
EDIT:
Other findings:
- OBJECT_DEFINITION function also returns the wrong code for the view
- After re-compiling the view the error is gone and the definition is correct.
- I feel that it could have to do with old SSIS packages and predecessors using that to migratie objects on a system level table or someone literally updating SYS.OBJECTS (even though that shouldn't be possible).
- I *feel* that this is a discrepancy that should be thrown during DBCC CHECKDB but it's not.
This all looks to be normal to me. The most likely reason for the difference in view definition is that the underlying base tables changed. And the view wasn't created with schema binding.
You need to execute sp_refreshview against views that references tables that have changed. Otherwise the definition will be out of date
If views are created using with schemabinding then among other things, it won't underlying tables to be modified in a way that would affect the view definition. The view definition needs to be dropped or modified first.
You would have the same results for sys.sql_modules as you would for sys.all_sql_modules as sys.all_sql_modules is the union of sys.sql_modules and sys.system_sql_modules.
So it's including system modules.
It's really not something that is database corruption so it wouldn't throw an error on dbcc checkdb. If you want an error to be thrown, the view needs to be created using with schemabinding. Then you would get the errors when changing the underlying tables.
Sue
February 1, 2017 at 1:56 am
Sue_H - Monday, January 30, 2017 11:14 AMbas de zwart - Monday, January 30, 2017 6:07 AMQuestions:
- How could this have happened?
- Am I querying the right objects? (Same results in both SQL_Modules and ALL_SQL_Modules.
- How can it be that the definition inside the system table is different from the actual object name?All object_id's are correct, I'm not looking at a duplicate or something weird like that.
EDIT:
Other findings:
- OBJECT_DEFINITION function also returns the wrong code for the view
- After re-compiling the view the error is gone and the definition is correct.
- I feel that it could have to do with old SSIS packages and predecessors using that to migratie objects on a system level table or someone literally updating SYS.OBJECTS (even though that shouldn't be possible).
- I *feel* that this is a discrepancy that should be thrown during DBCC CHECKDB but it's not.This all looks to be normal to me. The most likely reason for the difference in view definition is that the underlying base tables changed. And the view wasn't created with schema binding.
You need to execute sp_refreshview against views that references tables that have changed. Otherwise the definition will be out of date
If views are created using with schemabinding then among other things, it won't underlying tables to be modified in a way that would affect the view definition. The view definition needs to be dropped or modified first.
You would have the same results for sys.sql_modules as you would for sys.all_sql_modules as sys.all_sql_modules is the union of sys.sql_modules and sys.system_sql_modules.
So it's including system modules.
It's really not something that is database corruption so it wouldn't throw an error on dbcc checkdb. If you want an error to be thrown, the view needs to be created using with schemabinding. Then you would get the errors when changing the underlying tables.Sue
Dear Sue,
You misunderstand the question. The underlying tables are not the issue.
The issue is that the OBJECT_NAME differs in SYS.OBJECTS from the OBJECT_NAME definition inside the OBJECT definition.
So OBJECT_NAME returns: vu_VPADmins
But when you script that objects it starts with:
CREATE VIEW vu_Admins... etc.
So the VP part is missing from the name.
It's a huge discrepancy in sys.sql_modueles.
The view itself (all code besides the name of the object) is good and doesn't break.
February 1, 2017 at 2:25 am
If you rename the view (e.g. from SSMS), sql_modules doesn't get updated, resulting in exactly what you are seeing.
February 1, 2017 at 4:20 am
Ian Scarlett - Wednesday, February 1, 2017 2:25 AMIf you rename the view (e.g. from SSMS), sql_modules doesn't get updated, resulting in exactly what you are seeing.
So the definition of the view in sql_modules isn't actually the 'actual' definition of the object.
So where do you then receive the 'actual' definition of the object itsellf?
OBJECT_DEFINITION function gives the same discrepancy.
February 1, 2017 at 5:55 am
as others have identified, the object was renamed with sp_rename.
OBJECT_DEFINITION and the definition in sql_modules are what is actually saved/stored.
when you go through the GUI, the results of the SMO being called by SSMS is additionally modifying the definition to have the current name of the object. so the gui does an additional helpful modification to what is actually being stored in sql_modules.
Lowell
February 1, 2017 at 7:19 am
Am I the only thinking this is a serious design flaw?
February 1, 2017 at 7:46 am
bas de zwart - Wednesday, February 1, 2017 7:19 AMAm I the only thinking this is a serious design flaw?
Probably. You really didn't find a bug.
There are similar types of things on most database platforms in regards to metadata when changing things with database objects. That's why things like sp_refreshview exist in SQL Server. Or why Oracle has UTL_RECOMP. I have seen some development groups refreshing views of all views on releases as part of their release routine. This really isn't a new or surprising thing.
Dropping and recreating a view or using sp_refreshview is not that difficult or time consuming. It's just automatic for a lot of people doing database development.
Sue
February 2, 2017 at 5:34 am
Sue_H - Wednesday, February 1, 2017 7:46 AMbas de zwart - Wednesday, February 1, 2017 7:19 AMAm I the only thinking this is a serious design flaw?Probably. You really didn't find a bug.
There are similar types of things on most database platforms in regards to metadata when changing things with database objects. That's why things like sp_refreshview exist in SQL Server. Or why Oracle has UTL_RECOMP. I have seen some development groups refreshing views of all views on releases as part of their release routine. This really isn't a new or surprising thing.
Dropping and recreating a view or using sp_refreshview is not that difficult or time consuming. It's just automatic for a lot of people doing database development.Sue
I just did a full sp_refreshview on this view and the object definition remains wrong, see the attached screenshot.
I did not call it a bug, but a design flaw, which is a difference.
Now you would say 'just drop and recreate all views'. But for that I need the proper definition, i mean that was the point of this whole exercise. 🙂
(Please note this is just one view of hundreds as this Database's source is propagated across many jurisdictions. So think that for any exercise I need to be able to automate it across 26+ SQL server instances and DB's.
Running on: Microsoft SQL Server 2012 - 11.0.5343.0 (X64) May 4 2015 19:11:32 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
February 2, 2017 at 12:29 pm
bas de zwart - Thursday, February 2, 2017 5:34 AMI just did a full sp_refreshview on this view and the object definition remains wrong, see the attached screenshot.
I did not call it a bug, but a design flaw, which is a difference.Now you would say 'just drop and recreate all views'. But for that I need the proper definition, i mean that was the point of this whole exercise. 🙂
(Please note this is just one view of hundreds as this Database's source is propagated across many jurisdictions. So think that for any exercise I need to be able to automate it across 26+ SQL server instances and DB's.Running on: Microsoft SQL Server 2012 - 11.0.5343.0 (X64) May 4 2015 19:11:32 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
If you already know the answer why post the question? I'm sorry but the issues around this are all over the internet, for various platforms, dating back to over 10 years. I would venture to say that any piece of software or any RDBMS is going to have nuances or things some will perceive as design flaws. The only exercise I really engage in in that regard is learning those things and moving on. Hashing it out over and over and coming up with all the ways I can find something that doesn't work how I would think it should just isn't going to provide any value unless I plan on designing a better replacement for that software or RDBMS.
If you search on this you can come up with a large number of articles to understand this better:
A reason to avoid sp_rename
sp_rename (Transact-SQL)
Renaming of stored procs, triggers, UDFs, and views
sp_rename a table and keys and constraints
SQL SERVER – DO’s & Don’ts of sp_rename
And its all summed up in post up here years ago:
Renaming a view
Sue
February 2, 2017 at 11:59 pm
Sue_H - Thursday, February 2, 2017 12:29 PMbas de zwart - Thursday, February 2, 2017 5:34 AMI just did a full sp_refreshview on this view and the object definition remains wrong, see the attached screenshot.
I did not call it a bug, but a design flaw, which is a difference.Now you would say 'just drop and recreate all views'. But for that I need the proper definition, i mean that was the point of this whole exercise. 🙂
(Please note this is just one view of hundreds as this Database's source is propagated across many jurisdictions. So think that for any exercise I need to be able to automate it across 26+ SQL server instances and DB's.Running on: Microsoft SQL Server 2012 - 11.0.5343.0 (X64) May 4 2015 19:11:32 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
If you already know the answer why post the question? I'm sorry but the issues around this are all over the internet, for various platforms, dating back to over 10 years. I would venture to say that any piece of software or any RDBMS is going to have nuances or things some will perceive as design flaws. The only exercise I really engage in in that regard is learning those things and moving on. Hashing it out over and over and coming up with all the ways I can find something that doesn't work how I would think it should just isn't going to provide any value unless I plan on designing a better replacement for that software or RDBMS.
If you search on this you can come up with a large number of articles to understand this better:
A reason to avoid sp_rename
sp_rename (Transact-SQL)
Renaming of stored procs, triggers, UDFs, and views
sp_rename a table and keys and constraints
SQL SERVER – DO’s & Don’ts of sp_renameAnd its all summed up in post up here years ago:
Renaming a viewSue
I think you misunderstand.. where did I say 'I already know the answer?'.
The point is definitely not to call SQL a bad product. The reason I post here is to find a solution and ask for help!
sp_refreshview was offered as a solution but it doesn't seem to work. 🙁
I understand all the intricacies of sp_rename but we are 3 DBA's on 126 SQL Servers and 45 developers that we cannot control, I can ask them 'don't use it', but I can't enforce them not to. Stuff happens, basically, and it's up to us to control this stuff.
The thing is... I am no further to a solution to dynamically script out all custom objects from a vendor delivered database as I was at the first post. 🙂
I am now diving into stepping away from T-SQL and will implement SMO into the .NET application to proceed. This goes against one of the requirements that no extra libraries should be needed (makes the installation more difficult) but I hope it will solve this issue so we can get the 'actual' definition of objects as they run, not some virtual representation of them.
Again I am not here to criticise, I am here to ask for help.
<solved> Using SMO objects I've managed to retreive the proper definition for all custom objects in our target databases. Just did some thorough testing and it works.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply