|
|
Posted by Tibor Karaszi on 04/01/07 07:20
It seems you are confusing creating the proc with executing the proc. MV apparently displays (when
you select Edit or something similar), the proc code with an ALTER PROC in the beginning, assuming
that you want to modify the source code for the procedure.
Compare to developing a C app from an IDE. The IDE let you work with the source code. This is the
ALTER PROC part. When you open/edit or whatever the proc in VS, it reads the source code for the
proc from the system tables and replace the initial CREATE to ALTER assuming that you want to modify
the source code.
Executing the C code is different from compiling and linking it.
For the first URL you referred to, we never got a repro of what actually was happening. For the
second, we found a bug where the tool incorrectly not only changed CREATE PROC to ALTER PROC, but
also code *inside the proc* was changed such as CREATE TABLE changed to ALTER TABLE.
Also, I suggest you keep your source code in files, which allow you to version them (Source safe
etc). Or even look at Visual Studio for Database Professionals.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"raylopez99" <raylopez99@yahoo.com> wrote in message
news:1175379997.470001.106510@p15g2000hsd.googlegroups.com...
> Keep in mind this is my first compiled SQL program Stored Procedure
> (SP), copied from a book by Frasier Visual C++.NET in Visual Studio
> 2005 (Chap12). So far, so theory, except for one bug (feature?)
> below. At some point I'm sure I'll be able to laugh about this, akin
> to forgeting a semi-colon in C/C++, but right now it's frustrating
> (time to sleep on it for a while).
>
> Problem--
> For some reason I get the error when trying to save files where two
> tables (called Author and Content), linked by a single key, form a
> relationship.
>
> By simple comparison of the source code in the textbook and my program
> (below) I found the difference: instead of, like in the textbook, the
> Stored Procedure (SP) starting with "CREATE PROCEDURE", it
> *automatically* is (was somehow) given the name of 'ALTER PROCEDURE'
> and I cannot change this to "CREATE PROCEDURE" (you get an error in MS
> Visual Studio 2005 Pro edition of "There is already an object named
> XXX in the database", see *|* below). No matter what I do, the SP is
> always changed by Visual Studio 2005 to 'ALTER PROCEDURE'!!!
> (otherwise it simply will not save)
>
> Anybody else have this happen? (See below, others have had this happen
> over the years but it's not clear what the workaround is)
>
> Keep in mind this is my first attempt and I have ordered some
> specialized books on SQL, but if this is a common problem (and I
> suspect it's some sort of bug or quirk in VS2005), please let me know.
>
> Frankly I think SQL as done by VS2005 is messed up.
>
> Here are two Usenet threads on this problem:
>
> (1) http://tinyurl.com/2o956m or,
>
> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/8a289c6cb9c698be/61454182ae77d409?lnk=st&q=CREATE+PROCEDURE+CHANGED+TO+ALTER+PROCEDURE+&rnum=4#61454182ae77d409
>
> (2) http://tinyurl.com/2ovybv or,
>
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/6286b2ed77c03105/99e5428bf0525889?lnk=st&q=CREATE+PROCEDURE+CHANGED+TO+ALTER+PROCEDURE+&rnum=6#99e5428bf0525889
>
> The second thread implies this is a bug--any fix?
>
> Also this bug might be relate to the fact I've switched (and not
> rebooted) from Administrator to PowerUser after successfully changing
> the permissions in the SQL Server Management Studio Express (see this
> thread: http://tinyurl.com/2o5yqa )
>
> Regarding this problem I might try again tommorrow to see if rebooting
> helps.
>
> BTW, in the event I can't get this to work, what other SQL editor/
> compiler should I use besides MS Visual Studio 2005 for ADO.NET and
> SQL dB development?
>
> RL
>
> // source files
>
>
> // error message:
>
> 'Authors' table saved successfully
> 'Content' table
> - Unable to create relationship 'FK_Content_Authors'.
> The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
> "FK_Content_Authors". The conflict occurred in database "DCV_DB",
> table "dbo.Authors", column 'AuthorID'.
>
> // due to the below no doubt!
>
> --
> CREATE PROCEDURE dbo.InsertAuthor /* THIS IS CORRECT (what I want)
> 'CREATE PROCEDURE' not 'ALTER PROCEDURE'*/
>
> (
> @LastName NVARCHAR(32) = NULL,
> @FirstName NVARCHAR(32) = NULL
> )
> AS
> /* SET NOCOUNT ON */
> INSERT INTO Authors (LastName, FirstName)
> VALUES (@LastName, @FirstName)
> RETURN
> --
> ALTER PROCEDURE dbo.InsertAuthor /* WRONG! I want 'CREATE PROCEDURE'
> not 'ALTER PROCEDURE' but VS2005 won't save it as such!!!*/
>
> (
> @LastName NVARCHAR(32) = NULL,
> @FirstName NVARCHAR(32) = NULL
> )
> AS
> /* SET NOCOUNT ON */
> INSERT INTO Authors (LastName, FirstName)
> VALUES (@LastName, @FirstName)
> RETURN
> --
>
> *|* Error message given: when trying to save CREATE PROCEDURE Stored
> Procedure: "There is already an object named 'InsertAuthor' in the dB
>
Navigation:
[Reply to this message]
|