March 1, 2013 at 11:27 am
Hi,
We are using sp_refreshsqlmodule to find out if there are any integrity issues with the database.
The process is that we generate scripts by dropping and recreating stored procedures, functions or views every time we change any of the objects.
I noticed today that when I execute sp_refreshsqlmodule procedure after running my scripts, SQL Server is reverting my changes done. For e.g. If a dropped a stored procedure and created it again using a drop and create script, SQL Server is reverting the change I have done with my new script for that procedure.
Has anyone else faced a similar issue? I am interested in knowing the underlying cause as to why SQL Server is behaving in this manner?
Thanks!
March 1, 2013 at 12:01 pm
I figured out what the issue is.
The issue is that I had renamed the procedures and functions through the Management studio as I had later planned to revert the changes.
I then ran those scripts that were to re-create some of the existing procedures and functions after dropping them.
I don't know the internals of how this procedure works but I think why SQL Server was reverting it to an older version of the procedure/functions was because the older procedures that I had renamed still carried the same object ID's in the metadata. When I ran the scripts to create the renamed procedures, it was reverting my changes to the one that I had renamed.
How I verified this is by deleting the old stored procs and functions and then running my scripts that created them and after that I ran sp_refreshsqlmodule and it did NOT overwrite my changes.
Hope this helps!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply