Jennifer Gross
jengross at gte.net
Wed Jun 27 15:48:48 CDT 2007
Stepping outside my comfort zone certainly has some growing pains. But
I need to grow . . . so here we go . . .
Jennifer
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, June 27, 2007 1:36 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Viewing and Modifying stored procedures
I have been watching this thread with amusement. I still have a very
poor handle on this stuff and likely will for quite some time. SQL
Server is for REAL MEN (and WOMEN). Wooses need not apply. We edit our
SQL statements with notepad, and that only because they took away our
copy of Edit (go to the command prompt and type Edit). We don't need no
steenkin mouse!
John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Eric
Barro
Sent: Wednesday, June 27, 2007 4:24 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Viewing and Modifying stored procedures
Jennifer,
What you are seeing is the script that will allow you to make
modifications and when you run it, it will save your mods to the
existing sproc. When you create a new sproc it will say CREATE instead
of ALTER.
The general format is..
CREATE objectype objectname objectparameters AS
<everything in between is your SQL code>
END
If you simply want to look at the code that's how you do it. If you
don't click the execute icon it will simply stay as it is. SQL server's
user interface takes a while getting used to as it was the case when
Microsoft Access first came out. A paradigm shift into object-oriented,
event-driven, point and click from the usual UI was necessary. In
essence, don't count too much on point and click operations in SQL
server because you will be expected to know and learn SQL syntax.
Just ask John Colby...he knows what's involved...LOL.
Eric
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Jennifer Gross
Sent: Wednesday, June 27, 2007 1:13 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Viewing and Modifying stored procedures
Hi Ron,
Thanks for this. In SQL Server 2005 Management Studio right click
Properties on a SP brings up a different kind of window - similar to the
Properties window of the database itself, showing permissions,
encryption info, etc. It is the right click > Modify option that brings
up the SP with an ALTER key word - an example is a right click > Modify
on the SP dbo.addUserQueue will bring up a query editor window
containing the
following:
ALTER PROC [dbo].[addUserQueue] @userid varchar(12),
@Queueuserid varchar(12)
as
if (select count(UserID) from UserQueues where userID
= @UserID and QueueUserID = @QueueUserID) = 0
Begin
Insert into UserQueues (UserID, QueueUserID)
values (@UserID, @QueueUserID)
End
I may be wrong, but I don't think that ALTER is a part of this SP, but
is there so that you can make any modifications to the SP and then
Execute the script (if that is the right word for it), in order to make
the changes necessary to the SP.
It is a conceptual shift that I need to make. I was not looking for a
QBE grid, but more of how a module is edited in Access - in an editing
window with a Save command. I now think I've got it that for SQL Server
SPs I need to Execute an ALTER command to modify the stored procedure,
and if I just want to take a look at the SP's functionality I have to
right click > Modify which brings up the SP along with this ALTER key
word, ready to be modified and executed. When I am done looking I just
close the window without saving.
Thanks for the help,
Jennifer
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Ron
Allen
Sent: Wednesday, June 27, 2007 12:49 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Viewing and Modifying stored procedures
Hi Jennifer,
I am still on SQL Server 2000, using Enterprise Manager and Query
Analyzer, but understand that all of that functionality is built into
2005 just named differently.
You are correct that the way to edit stored procedures in SQL server is
through T-SQL using the CREATE or ALTER syntax. There is no Access-like
query builder that lets you build a query visually. Whether this is good
or bad or right or wrong can be debated endlessly, and there are what I
consider valid arguments on both sides.
That said, in SQL Server 2000 there are at least two ways to simply see
the SP code. You can edit the SP in Query Analyzer or you can select
"properties" for the SP in Enterprise Manager. In either case, you will
also see the "ALTER"
or "CREATE" portion of the syntax because that is a part of the syntax
of the SP. It isn't a "template", which I'm thinking may be one of the
places you need to make a conceptual shift.
Don't get discouraged, because a vast number of things that you learned
in Access will be useful when working with SQL Server, but usually only
in that it makes things familiar not because there is a direct
correlation on the nitty gritty details.
Ron
> That makes complete sense, but it doesn't work :( I read somewhere
> that the Query Analyzer in previous versions of SQL Server is no
> longer a part of SQL Server 2005. I am not sure, but have a feeling,
> that this missing Query Analyzer is the reason I can't do what I want
> to do. Apparently, unless you use T-SQL to edit stored procedures, the
> only other way they can be edited is through Visual Studio. It can't
> be done directly in what is now called SQL Server Management Studio.
> It appears I need to learn how to navigate Visual Studio 2005 as well
> as SQL Server 2005.
>
> >
> >Thanks Robert. It is sinking in that this is the way it works in SQL
> >Server. Even if I just want to see the stored procedure so that I
can
> >understand what it does (since I inherited this database), I have to
> >generate this ALTER PROC template and then close the window without
> >saving. Seems strange that I just can't take a look at the stored
> >procedure code, but I guess that's the way it is.
_______________________________________________
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com