|
|
Posted by raylopez99 on 03/31/07 22:26
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
[Back to original message]
|