The old inability to toggle/change/switch between ALTER PROCEDURE <---> CREATE PROCEDURE bug (or is it a feature?)

[raylopez99]

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/micro...454182ae77d409

(2) http://tinyurl.com/2ovybv or,

http://groups.google.com/group/micro...e5428bf0525889

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

[Tibor Karaszi]

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" <ra********@yahoo.comwrote in message
news:11**********************@p15g2000hsd.googlegr oups.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/micro...454182ae77d409

(2) http://tinyurl.com/2ovybv or,

http://groups.google.com/group/micro...e5428bf0525889

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

[Erland Sommarskog]

raylopez99 (ra********@yahoo.com) writes:

// 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'*/

No! That has nothing to do with it! In the above you are try to add a
foreign key from the Content table to the Authors table, but there is
data that prevents this from happening. Maybe because you had already
entered data in Contents before you tried to apply the foreign key. This
is entirely unrelated to your procedure.

Here is a script that demontstrates:

CREATE TABLE Authors (
authorid int NOT NULL,
name nvarchar(50) NOT NULL,
CONSTRAINT pk_authors PRIMARY KEY (authorid))
go
-- Very simple book table. I ignore ISBN and the fact that
-- a book can have several authors.
CREATE TABLE Books (
bookid int NOT NULL,
title nvarchar(1000) NOT NULL,
authorid int NOT NULL,
CONSTRAINT pk_books PRIMARY KEY (bookid))
go
-- Being eager, we insert a book, before we add any authors.
INSERT Books (bookid, title, authorid)
VALUES(1, 'The Mouse Trap', 1)
go
-- Oops, someone told us that foreign keys is a good idea.
ALTER TABLE Books ADD
CONSTRAINT fk_books_authors FOREIGN KEY (authorid)
REFERENCES Authors(authorid)
go
-- It failed. We must add the author first:
INSERT Authors (authorid, name)
VALUES (1, 'Agatha Christie')
go
-- It should work now.
ALTER TABLE Books ADD
CONSTRAINT fk_books_authors FOREIGN KEY(authorid)
REFERENCES Authors(authorid)
go
-- Clean up.
DROP TABLE Books, Authors

Note by the way that this is all SQL commands. While point-and-click
GUIs for table design can make you work faster if you are unexperienced,
learning the SQL commands is absolutely essential if you plan for a
professional career in SQL programming. Once you know the commands well,
you can use GUI tools if you find that more convenient. But all the
tools do is to generate the commands like the above, and without knowledge
and understanding of that, you are pretty clueless when things go wrong.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[raylopez99]

On Apr 1, 3:22 am, Erland Sommarskog <[e-mail]:

>
Note by the way that this is all SQL commands. While point-and-click
GUIs for table design can make you work faster if you are unexperienced,
learning the SQL commands is absolutely essential if you plan for a
professional career in SQL programming. Once you know the commands well,
you can use GUI tools if you find that more convenient. But all the
tools do is to generate the commands like the above, and without knowledge
and understanding of that, you are pretty clueless when things go wrong.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks Erland--I have bookmarked your example for future reference,
after I read some SQL books I ordered. Right now I'm trying to just
learn how the SQL editor and IDE work. For some strange reason I was
able to "save" the same example while as Administrator rather than
PowerUser, but I think it's just a coincidence--I must have inserted a
wrong word or some wrong semantics in the PowerUser version, which is
kept in its own seperate database. Permissions seems to be a big deal
with modern SQL databases BTW--eventually I hope to figure out how to
manipulate permissions on the fly with commands, as you seemed to show
in another thread.

Seems like SQL is like a macro language that is "top-down" procedural
and definitely not object oriented of course.

Now onto the next topic in my SQL GUI paint-by-numbers book:
connected vs disconnected ADO.NET (I program for fun, not
professionally).

RL

[Erland Sommarskog]

raylopez99 (ra********@yahoo.com) writes:

For some strange reason I was able to "save" the same example while as
Administrator rather than PowerUser, but I think it's just a
coincidence

Probably because the first time the procedure wasn't there, so CREATE was
the right thing. The second time round, ALTER is the right thing.

Seems like SQL is like a macro language that is "top-down" procedural
and definitely not object oriented of course.

SQL is hardly a macro language, but it's true that neither is it object-
oriented. And it is different from traditional programming. In a traditional
programming when you want to operate on many objects of the same time you
run a loop. In SQL you operate on all rows at once, and the optimizer out
which is the best way to do this.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[raylopez99]

On Apr 1, 8:16 am, Erland Sommarskog <[e-mail]:

>--

Thanks Erland--right now another problem that's sprung up is
connecting to a simple database (the same one, this time the database
'works' fine, but accessing it from outside the SQL language via a
console C++ program is the problem). See thread here: http://tinyurl.com/3e2nz8

If you have any insight please let me know.

Cheers,

Ray

[Erland Sommarskog]

raylopez99 (ra********@yahoo.com) writes:

Thanks Erland--right now another problem that's sprung up is
connecting to a simple database (the same one, this time the database
'works' fine, but accessing it from outside the SQL language via a
console C++ program is the problem). See thread here:
http://tinyurl.com/3e2nz8

You have a connection string that goes:

connection->ConnectionString = "User ID=sa; Password=;"
"Data Source=(local); Initial Catalog=DCV_DB;";

But didn't you say you were using SQL Express? By default, SQL Express is
installed as a named instance with the name SQLEXPRESS, in which case the
Data Source should be: (local)\SQLEXPRESS.

Did the User ID and Password come from that book? If you want your app
to run with Integrated Security, you should use

Integrated Security=SSPI;

instead. Having sa with a blank password is extremely bad practice, and
outright dangerous if the server is exposed on the Internet.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[raylopez99]

On Apr 1, 11:45 am, Erland Sommarskog <[e-mail]:

raylopez99 ([e-mail]) writes:

> See thread here:
http://tinyurl.com/3e2nz8

You have a connection string that goes:

connection->ConnectionString = "User ID=sa; Password=;"
"Data Source=(local); Initial Catalog=DCV_DB;";

But didn't you say you were using SQL Express? By default, SQL Express is
installed as a named instance with the name SQLEXPRESS, in which case the
Data Source should be: (local)\SQLEXPRESS.

Yes, but for some reason \SQLEXPRESS doesn't compile in VS2005 "S not
recognized" error

>
Did the User ID and Password come from that book?

Yes, it was the author's example.

If you want your app
to run with Integrated Security, you should use

Integrated Security=SSPI;

instead. Having sa with a blank password is extremely bad practice, and
outright dangerous if the server is exposed on the Internet.

I'm not sure this database is exposed to the Internet or not (I
believe I checked the \CLR option in SQL Server Management Express,
and intended only local connections but I don't know.

Also of interest: "testing" the database connection (from within
Server Explorer in VS2005) yields "OK" (connected), then clicking on
"Modify Connection" then "Advanced" gives: "Use Windows
Authentication", "Integrated Security = True", "initial catalog =
NAMEofDB", "Data Source = myPCname\SQLEXPRESS, and a bunch of other
parameters such as packet size, etc.

Rather than waste any more of your time (and I thank you for replying--
very helpful), I am going to order a specialized book on programming
for SQL *within* VS2005 (I've already ordered a bunch of SQL generic
books, including this one: Programming Microsoft SQL Server 2005
(Paperback) by Andrew J. Brust (Author)
http://www.amazon.com/Programming-Mi...5461390&sr=1-1
).

The online help for VS2005 was too specific and not comprehensive
enough--they were talking about setting connection strings, which is
fine once you get more experienced, but all I want to do at the moment
is connect to a existing dB from within a simple console C++
application. I wish they had an example showing how to get a
connection string from all databases 'open' in your local path, which
would have helped, or how to connect to any database that's open in
your 'environment'.

Anyway I think the solution lies in security permissions etc. The
example in the textbook made it look so easy but it's not. As in lots
of programming you can spend days with various permutations or you can
see an exemplar that works, and solve your problem much quicker.
Right now I'm looking for that exemplar and am confident I can find it
eventually.

BTW I recently have picked up the Trojan virus "pushu-A", which is
unusual, and I hope it's not related to this database programming. I
can't imagine how, since the above dB is "using Windows
Authentication" and I have a firewall, so I should not be any less
safe than before, but I don't know.

RL

[Erland Sommarskog]

raylopez99 (ra********@yahoo.com) writes:

Yes, but for some reason \SQLEXPRESS doesn't compile in VS2005 "S not
recognized" error

If you program in C++ (and I assume C#) you need to double the \, as
\ has a special meaning in string literals in C++.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[raylopez99]

On Apr 1, 3:41 pm, Erland Sommarskog <[e-mail]:

raylopez99 ([e-mail]) writes:

Yes, but for some reason \SQLEXPRESS doesn't compile in VS2005 "S not
recognized" error

If you program in C++ (and I assume C#) you need to double the \, as
\ has a special meaning in string literals in C++.

--

Yes it worked! You are correct Erland. The double slash did work,
and the program compiled and worked perfectly. (For some reason the
forward slash, which usually also works for paths in lieu of the
double slash, did not compile however).

For the record here is what worked for the connection string:

connection->ConnectionString = "Persist Security Info = False;
Integrated Security=SSPI;" "Data Source=(local)\\SQLEXPRESS; Initial
Catalog=MyDATABASEnameHERE_DB;";

//note the double slash: \\

No need to supply a password in the connection string (using Windows
Authentication).

Thanks again! Now I can pickup this book on SQL again, which I set
aside.

RL

[Erland Sommarskog]

raylopez99 (ra********@yahoo.com) writes:

Yes it worked! You are correct Erland. The double slash did work,
and the program compiled and worked perfectly. (For some reason the
forward slash, which usually also works for paths in lieu of the
double slash, did not compile however).

I would exepct "(local)/SQLEXPRESS" to compile, but certainly not to be
functional.

I don't know for sure, but I suspect that if / works just like \ in
file specifications in C++, this is something that is implemented in
the C++ run-time library to achieve Unix compatibility.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[raylopez99]

On Apr 2, 2:44 pm, Erland Sommarskog <[e-mail]:

raylopez99 ([e-mail]) writes:

Yes it worked! You are correct Erland. The double slash did work,
and the program compiled and worked perfectly. (For some reason the
forward slash, which usually also works for paths in lieu of the
double slash, did not compile however).

I would exepct "(local)/SQLEXPRESS" to compile, but certainly not to be
functional.

No, that's the amazing part--it was functional. As shown in
http://tinyurl.com/3e2nz8, I got the correct message of "We got a
connection!", which showed indeed the db DCV_DB was connected to.

Thanks again,

RL