zuloohn.blogg.se

1. SQL STUDIO SETTING PERMITTING FIELD TYPES FROM CHANGING HOW TO
2. SQL STUDIO SETTING PERMITTING FIELD TYPES FROM CHANGING CODE

SQL STUDIO SETTING PERMITTING FIELD TYPES FROM CHANGING CODE

No code changes necessary, but there might be some issues with rights due to dropping of procedures.īut today I saw a post by Michael J. OBJECT_DEFINITION( OBJECT_ID (referencing_schema_name + '.' + referencing_entity_name))įROM sys.dm_sql_referencing_entities('', 'TYPE') Referencing_schema_name + '.' + referencing_entity_name, SELECT /* get the procedures and the code */ INSERT INTO (referencing_entity_name, object_code) Then drop the procedures, drop the type, recreate the type, and recreate the procedures.ĭECLARE TABLE (id INT IDENTITY, referencing_entity_name SYSNAME, object_code NVARCHAR(MAX)) My own solution was to collect the procedures and their code in which the type was referenced. Sometimes a change won't require a phased-in approach, but sometimes it will. Say you're adding or dropping a column or changing the type of a column, but you don't have the luxury of changing all the application code to start/stop referencing that column or change the variables feeding into it. Gserdijn, yes there are elegant ways to change everything all at once.īut that's not always what you want to do - sometimes there is a benefit to phase in two different versions. Wednesday, Octo3:13:52 PM - Aaron Bertrand It goes without saying that if you have dynamic sql code referencing you type it won't work.Įspecially if you build your referenced type at runtime so you are unable to find it even with a text search in procedures scripts. No article can solve every peripheral problem without becoming a book.

SQL STUDIO SETTING PERMITTING FIELD TYPES FROM CHANGING HOW TO

This article also doesn't explain how to update all of your application code, either, which surely has mentions of the type name and data types that might be equally hard to find depending on how the source code creates those references, whether/how you're using source control, etc.

And maybe that's a risk you take using dynamic SQL - some things you're just not going to be able to discover until they break. On the plus side, if the code that references the old version of the type doesn't get updated to use the changes to the type, maybe it's ok that it continues to use the old version. While I don't imagine there are a whole lot of cases where you're using a table type in dynamic SQL, it's certainly possible. Wednesday, Octo11:52:17 AM - Aaron Bertrand