| Invert |
James Sturdy
11/25/2009 03:05
Stored Procedures missing / scripted with the wrong name (including reason why and solution)
I'm posting this both to hopefully offer some help to Perpetuum Software, and equally help any users of the SQL Schema Sync API that may have encountered this apparent problem.
First, I'll explain what was happening.
I am using the SQL Schema Sync API to synchronise the structure of multiple databases. I produced an XML file from the master database, and then applied this to an existing database.
At first glance, it appeared that some stored procedures were missing.
However, it then became apparent that in fact the stored procedure was there, however it had been created with the wrong name.
The name given was in fact the original name for the procedure. It had at some point been renamed through the SQL Server Management Studio by right clicking on the SP and going to 'Rename'.
Having seemingly found a 'bug', I investigated what was actually in the XML to see if I could find out more information about where the problem arose.
Below are snippets from the XML (I've trimmed out most of it...)
Notice that the stored procedure name doesn't match the 'CREATE PROCEDURE' line.
<Item type="PerpetuumSoft.DataModel.MsSql.StoredProcedure" id="2268" ExecuteAsClauseString="CALLER" Name="sp_SLGetStreamingSites"
Text="CREATE PROCEDURE [dbo].[sp_SLGetSiteList]
Having investigated this a bit on the internet, it seems to be a known 'issue' with SQL and renaming things and certain sys tables not being updated to reflect the new name.
I don't have a conclusion as to exactly which tables get out of sync, as there appears to be conflicting reports. Some people say it's the SYSCOMMENTS table, others the sys.sql_modules table. Several related links are at the bottom of this post.
Now, to resolve this, there are a few things that can be done.
The first is never rename anything. That's not very helpful.
The next is to DROP and then CREATE the procedure with the new name if you want to rename it.
Again, that's nice in theory, but it's going to be a bit of a pain if things are already renamed, and at some point, somebody will forget to do it and just rename it through the SSMS...
It does appear that you can fix the problem through the SSMS fortunately.
Go to the Stored Procedure, right click and choose 'SCRIPT AS > ALTER TO > New Query Editor Window'. Execute this procedure, and the sys tables that are out of sync are updated with the correct name.
The now correct XML is below. As we can see, the CREATE PROCEDURE line is now correct.
<Item type="PerpetuumSoft.DataModel.MsSql.StoredProcedure" id="2268" ExecuteAsClauseString="CALLER" Name="sp_SLGetStreamingSites"
Text="CREATE PROCEDURE [dbo].[sp_SLGetStreamingSites]
While this is all well and good now that there's a solution, I fear that it may cause users to distrust the SQL Schema Sync API, and if there is anything that PerpetuumSoft can do to work around this, it would be a worthwhile investment of your time.
As mentioned earlier, at first glance, it appears that the SQL Schema Sync has simply not synchronised all of the stored procedures. For the purposes of database development and developing software that uses these databases, if it doesn't do all of the procedures, then it might as well not do any, as it's just too much of a risk for things to be missing or inconsistent...
It is of course not the fault of the SQL Schema Sync API, it's just unfortunate that the issue will manifest itself as a result of the API, and thus the API will probably get the blame.
There is an article below that describes what's going on very well, I'd advise any developers to read it. There is also a MS bug report about it, although SQL Server 2008 isn't listed, so it's possible that they've fixed this in SSMS 2008...
http://blog.beyondrelational.com/2008/09/be-careful-when-renaming.html
http://support.microsoft.com/kb/243198
|
Michael Riman
12/30/2009 02:43
Stored Procedures missing / scripted with the wrong name (including reason why and solution)
Dear James,
Thank you for analysis of this situation. We’ll take your ideas into consideration in the nearest release of the product.
Best regards
Vald Nikrasov
|