747

sp_refreshView replaces view implementation when sp_rename has occured

I have a very weird issue with a view which I can reproduce. I'm quite sure that some view metadata is not up to date and causes this issue...

In short:

    <li>I have a view (MyTestView).</li> <li>This view is renamed (MyTestViewOld).</li> <li>A new view is created with the original name of the renamed view AND a different select query (MyTestView). </li> <li>An sp_refreshview is executed on the renamed view (MyTestViewOld). </li> <li>Now the implementation of MyTestView is that of MyTestViewOld!</li> </ul>

    I tried looking into the SP sp_refreshview, but he just executed another SP called sp_refreshsqlmodule_internal. I don't see this view in the system stored procedures of the master database :(

    My problem is actually fixed by removing the "MyTestViewOld" <strong>but I would like to know what exactly is going on!</strong> Tnx

    Here the script to reproduce the issue! (execute in steps please)

    --create a test table create table dbo.MyTestContacts( [Title] [varchar](20) NULL, [Name] [varchar](255) NULL, [FirstName] [varchar](255) NULL, [Telephone] [varchar](50) NULL, [Email] [varchar](255) NULL ) --insert data in the temp table insert dbo.MyTestContacts values ('Mr', 'Holly', 'Buddy', '0123456798', 'buddy@holly.co') insert dbo.MyTestContacts values ('Mr', 'Valens', 'Ritchie', '987654312', 'ritchie@valens.co') insert dbo.MyTestContacts values ('Mr', 'Richardson', 'Jiles Perry', '987654312', 'jp@richardson.co') --create a view SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view dbo.MyTestView as select Title, Name, FirstName from dbo.MyTestContacts GO --do a SCRIPT VIEW AS / ALTER TO in sql management studio to verify the view implementation --rename the view exec sp_rename 'dbo.MyTestView', 'MyTestViewOld' --BIS1 (explained below; used in a 2nd test run) --create a view with the same name as the first view, but different implementation! SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view dbo.MyTestView as select Title, Name, FirstName, Telephone from dbo.MyTestContacts GO --do a SCRIPT VIEW AS / ALTER TO in sql management studio to verify the view implementation of MyTestView (telephone is added) --perform a refreshView action on the MyTestViewOld view exec sp_refreshview 'dbo.MyTestViewOld' --do a SCRIPT VIEW AS / ALTER TO in sql management studio to verify the view implementation of MyTestView --> THIS SHOWS THE MyTestViewOld IMPLEMENTATION!!!! --(BIS1) Note that this sequence gets broken when I do an ALTER VIEW on MyTestViewOld right after the rename! --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --alter view dbo.MyTestViewOld as --select FirstName, Name, Email, Telephone from dbo.MyTestContacts --GO --drop everything drop view dbo.MyTestViewOld drop view dbo.MyTestView drop table dbo.MyTestContacts

    Answer1:

    sp_rename is rather imperfect, and there are lots of warnings against using it on most object types:

    Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

    Whilst there isn't a specific warning about the scenario you've found, there are some clues about the potential path that is leading to the problem arising:

    Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view

    And, as you've discovered, the internal method that refreshes a view is sp_refreshsqlmodule_internal - so I'd suggest it's some form of bug down at the "module" level meta-data internals.

    You might want to consider raising the issue on SQL Server Connect, if for no other reason than for the bug to be documented elsewhere and (maybe) for them to add a further warning to the product documentation.

    Answer2:

    Encountered this problem as well. I used the following query to identify any views in the database that could cause a problem:

    select * from ( SELECT o.object_id, o.name, Replace(Replace(Replace(SUBSTRING(m.definition,13,(CHARINDEX(CHAR(13),m.definition + CHAR(13)))-13),'[',''),']',''),'dbo.','') as definitionName FROM sys.objects AS o left join sys.sql_modules AS m on o.object_id = m.object_id where o.type='v' )x where name != definitionName

Recommend

  • NUnit Integration Tests on Web API - how to create/destroy integration test db
  • How can I append a JTextArea from a method that doesn't contain the JTextArea?
  • XPCOM writeString error
  • average and sum from array in php [closed]
  • Removing a json object from a json file in Java
  • When do I need to cast the result of malloc in C language? [duplicate]
  • how to locally develop windows azure apps
  • How to decode name of month into an integer
  • VBA/Macro to copy random rows based on multiple conditions
  • How to install mbstring extension
  • How to edit a JupyterLab theme?
  • Android Twitter Received authentication challenge is null [duplicate]
  • How to explain high AUC-ROC with mediocre precision and recall in unbalanced data?
  • inspect attributes of object python
  • How to update new tab title?
  • Doing some fairly basic sums with different tables
  • Aspect does not capture method from Scheduled
  • Where to change the form of json response in django rest framework?
  • What should I do if I want to send message by MPI and receive messages at the same time?
  • Facebook Ads API - Batch Requesting Targeting Search
  • Automatic way to obtain the floating-point operation count for some piece of code
  • Jackson custom deserialiser delegate back to default one
  • Java: Moving second rectangle using different keys with KeyListener
  • Bootstrap 4: Stop collapse from pushing content down
  • decompile a .class file programmatically
  • Trying to generate url slugs with PHP regex, Japanese characters not going through
  • Variable Length Arguments without using stdarg.h
  • Restrict the initial queryset to objects in pagination in django-restframework
  • JasperFillManager.fillReport and mongo?
  • gbz80 - IF statements
  • sp_refreshView replaces view implementation when sp_rename has occured
  • Error “Connection reset” in JMeter (SOAP XML web-service)
  • what does the colon do in c?
  • Base64 decoding failed for Google Speech API
  • adMob | Extend Banner size?
  • Escape double quotes within double quotes
  • How do I narrow down scope when running an ansible playbook?
  • Aggregating two data frame columns without any existing pattern logic