Post reply

altering the text of a sproc thru QA

  • January 9, 2004 at 4:19 am

    #107347

    Hi,

    I am trying to use QA as opposed to EM.

    I want to change the text of the sproc.

    I can find the sproc text by doing:

     

    sp_helptext spGetProducts

     

    Now, the sproc text appears in QA, but can't be edited.

    Is there a way to to:

    1) change the text.

    2) update the sproc.

     

    many thanks,

    yogi.

     

     

  • January 9, 2004 at 4:37 am

    #489316

    Once you got the text from spGetProducts, use

    drop procedure spGetProducts

    go

    create procedure spGetProducts

    as .......

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 9, 2004 at 4:44 am

    #489320

    oh, you can also use

    alter procedure spGetProducts

    as

    ....

     

    Is even better, becuase you don't need to recreate permissions or dependent objects

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 9, 2004 at 5:33 am

    #489330

    cheers Frank,

    All these wee things save a lot of time.

    Thanks for the 2 options.

    yogiberr

  • January 9, 2004 at 1:45 pm

    #489460

    Even better yet. When in QA hit F8 to bring up the object browser. Drill down to the SP you want to "EDIT" and right click on it. Hit Edit and modify the SP to your hearts content . When you are done save the edited sp out to a file and the put it in your source control application. If you ever need to roll back to a previous version you can grab it from your source control app and update the database with the old version. Many times I have been thankful for having the SP stored in a source control app!




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 11, 2004 at 6:06 am

    #489538

    Cheers Gary,

    Hmm..I haven't implemented a source control application yet.Something I have to look at.Do most people use Visual Source safe, or perhaps implement their own thru drawing up their own procedures?

    Maybe I should start up another thread for this dicussion?

    ta, yogi

  • January 11, 2004 at 1:27 pm

    #489544

    Sourcesafe!

    I guess, because it comes with Visual Studio

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 11, 2004 at 2:22 pm

    #489546

    Hey Frank! I use VSS Too... I also do all of my TSQL work on Stored Procedures through a database project in Visual Studio. I like to use a lot of macros and columnar editing and QA doesn't provide either. So I do most of my work in Visual Studio. Plus since I use a database project I can save directly to VSS from the project. Works for me




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 11, 2004 at 3:26 pm

    #489547

    magic,

    I'm on vis studio 2003.Does this mean that you use the dbProject  as an actual DAL in your solution? as opposed to using a "classLibrary" file. At the moment, I have a separate "classLibrary"  for my DAL.Sorry for the question, but this is all a bit new to me.

    cheers,

    yogi.

     

  • January 12, 2004 at 10:41 am

    #489614

    No I don't use the dbproject as a DAL. Since I only do TSQL work for the most part I use the DB project to hold all of my SP scripts. I also have connections to many of the machines I support in the project. In this way I can quickly get to all the code I want without leaving visual studio. Also since visual studio connects directly with VSS I can check files in and out without even leaving the IDE. I have several macros that I have written for the studio for things like adding a description header for stored procedures, doing multi line comments using -- as the comment, undoing those multi line comments, and my favorite is the one where I replace all keywords with the capitalization I prefer for those keywords. I do this as I really like to have stuff like FROM, SELECT, UPDATE, etc all capitalized and this way if I'm a little lazy I can highlight the sql and run the macro and it will clean up my code before my code review.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 12, 2004 at 10:58 am

    #489615

    Thanks Gary,

    That's a lot to be going on with.It's amazing the info that comes up as the result of a simple post.

    Cheers,

    yogi.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply