SQL Server - QA - edit procedure shows "create"

Asked By Keith G Hicks

20-Nov-09 10:09 AM

SQL 2000 Query Analyzer. Well this seems very odd to me. Every stored
procedure I can ever remember doing this with has never done this. When you
have the object browser open and right click a stored procedure, one of the
options is "edit". When you click that option, the procedure opens up and
the definition starts with "ALTER PROCEDURE..."

But last night, I had one start opening with "CREATE PROCEDURE..." instead.
Every time I open it up to edit it, it says "CREATE", not "ALTER".

So I droppted the proc and recreated it just to see what would happen. This
solved the problem. It opens up with the "edit" button showing "ALTER" now
instead of "CREATE". But I am very curious what was wrong here.Any idea why
this would happen?

Thanks,

Keith

21-Nov-09 05:50 AM

Wild guess:

The proc had been renamed so the name of the proc did not match the stored
source code for the proc and this confused the tool (QA)...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

21-Nov-09 07:33 AM

Tibor Karaszi (tibor_please.no.email_karaszi@hotmail.nomail.com) writes:

Sounds plausible. However, I tested that and the problem did not appear.

If I am to make a guess on my own, I would suspect that there is a comment
header, and there is a problem when scripting the procedure because CREATE
appears just around the end of a 4000-character chunk.

Overall, using things like Edit in Query Analyzer is like you would
edit your C# programs by disassembling the object files. Source code
should be kept under version control, and what it is in SQL Server is a
binary repository.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx