Post reply

Alternative to EM for writing sprocs?

  • October 11, 2005 at 4:29 pm

    #187971

    I've been using EM for writing stored procedures. I am ready to upgrade to something that works like an IDE. What do you use?

  • October 11, 2005 at 4:55 pm

    #596682

    Enterprise manager?

    You really should switch to Query Analyzer. That is the Baseline of what you should use.

    you can also use visual studio

  • October 11, 2005 at 5:33 pm

    #596685

    I spend most of my stored procedure development time in Query Analyzer.

    Haven't looked at the SQL Server 2005 Management/Development Studio much, but I've got a feeling they'll be a lot of extra overhead.

     

    --------------------
    Colt 45 - the original point and click interface

  • October 12, 2005 at 12:39 pm

    #596910

    How do you write sprocs in QA? If I right-click a stored proc and select Edit, I get it displayed in a Notepad-like window. How do I:

    - Check the script back into the database. Hitting Save offers to save the sProc as an .sql file

    - Display context help for functions and T-SQL keywords

    TIA.

  • October 12, 2005 at 4:22 pm

    #596956

    I save my stored procs in a .sql file and open this .sql file in QA when I need to make changes.  If you click on Execute Query in QA the stored proc will be compiled in the currently active database and ready for use.

  • October 12, 2005 at 5:05 pm

    #596959

    Right-Click the stored proc and choose "Script To New Window as -> Create". Then you can edit the stored procedure and when you run it (F5), and it's re-created in the database.

    Context help is available by highlighting the item you want help on and pressing Shift+F1

    Also, Right-click and object in the Object Browser and choose "Scripting Options..." this is were you can select things like including permissions, which I find is always handy

     

     

    --------------------
    Colt 45 - the original point and click interface

  • October 12, 2005 at 6:38 pm

    #596976

    Shift-F1 are Scripting Options are big help - thanks! Is there a way to highlight a section of code and have it commented out automatically. How about indenting/unindenting sections?

    When I r-clicked and selected Script to New Window as>Create and then hit F5, I got:

    There is already an object named 'CreateLinkTable' in the database.

    Do you save your scripts as .sql files? Is that good practice - why do people do this?

  • October 12, 2005 at 6:52 pm

    #596977

    -- Is there a way to highlight a section of code and have it commented out automatically. How about indenting/unindenting sections?

    U can find several commenting options under the Edit/Advanced menu.

    There is already an object named 'CreateLinkTable' in the database

    U must drop any object before you create it again. To do that you can change your script to

    drop procedure x

    go

    create procedure x

    as

    go.

    U can also Alter an object. This does not require you to drop it first.

    Do you save your scripts as .sql files? Is that good practice - why do people do this?

    Yes, creating scripts is the way you should work. You can modify them over and over, run them and most importantly back them up to a save place.

  • October 12, 2005 at 6:56 pm

    #596978

    Highlight row and press,

    Ctrl+Shift+C - Comment

    Ctrl+Shift+R - Un-comment

    Tab  - indent

    Shift+Tab - un-indent

    There are heaps more check "SQL Query Analyzer Keyboard Shortcuts" in Books Online.

    "When I r-clicked and selected Script to New Window as>Create and then hit F5, I got:

    There is already an object named 'CreateLinkTable' in the database."

    You can fix this by including the "Generate Transact-SQL to remove ..." option.

    Saving the script to as .sql files allows you to place them under source control. I know some people only work with .sql files. Their editing process would be something similar to,

    - Check file out of source control.

    - Edit file is tool of choice and save

    - Excute against database

    - Check file back into source control

    Whether you use .sql files is really up to you and how you can work best in your environment. For example, while the process above might be good, I know it just wouldn't function in my current job

     

    --------------------
    Colt 45 - the original point and click interface

  • October 13, 2005 at 7:28 am

    #597074

    The keyboard shortcuts are great. How do I create a new sproc. R-clicking the stored procedures node or the database node doesn't appear to give me that option.

    >>You can fix this by including the "Generate Transact-SQL to remove ..." option.<<

    Thanks - that worked.

  • October 13, 2005 at 7:36 am

    #597079

    I noticed that when I open an sproc using r-click>Script object to new window as>Create, when I try to save the file, the file name proposed in the Save Query dialog is generic, for example 'Untitled9' regardless of the name I saved it under previously. Is there a better way to work, i.e. open stored proc files from .sql files instead of from the database?

    >>You can modify them over and over, run them and most importantly back them up to a save place.<<

    Is there any advantage to modifying them as files instead of sProcs in the DB? Is the advantage to separate files that you keep your library of sprocs independent of the DB context? IOW, you can use sProcs you need in various DBs. I work on a single DB and back it up frequently.

    Thanks again for the prompt and thoughtful response. 

  • October 13, 2005 at 5:42 pm

    #597231

    "... How do I create a new sproc. R-clicking the stored procedures node or the database node doesn't appear to give me that option ..."

    Templates.

     

    --------------------
    Colt 45 - the original point and click interface

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

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