Post reply

Execute number of Stored Procedures

  • December 20, 2010 at 12:43 pm

    #388474

    Hi all,

    I developed an application which uses a SQL2K8 database with a number of stored procedures. One of these days I have to install my app at a customer, including the stored procedures.

    Is there a (free) tool available to help me install/execute the sprocs ?

    Kind regards,

    Piet Kouwer

    the Netherlands

  • December 20, 2010 at 12:46 pm

    #1264757

    You can keep a "CREATE" script and run it when needed.

  • December 20, 2010 at 12:47 pm

    #1264758

    Sorry for my start post, this is exactly what I mean: a tool to open & execute .SQL files with CREATE comands etc. I don't want to open and execute the files manually

  • December 20, 2010 at 12:51 pm

    #1264760

    use SQLCMD.EXE

  • December 20, 2010 at 12:53 pm

    #1264761

    Wildcat (12/20/2010)

    You can keep a "CREATE" script and run it when needed.

    Unfortunately, you may have to manually run it.

    Or, you can wrap it into your application, and use "SQLCMD.exe" ....

  • December 20, 2010 at 12:56 pm

    #1264762

    Well, if your scripts contain all the drop/create logic and end in a GO statement followed by a CR/LF you can use a command like

    COPY *.sql ..\combined.sql

    The file combined.sql in the directory above the current one will contain a script containing all the sprocs, you can then use SQLCMD or SSMS or another tool to actually execute this.

    I have used this method with some other control scripts to build up a script for a build of an entire database, tables, views, functions, sprocs, permissions, EVERYTHING. All into a single script.

    CEWII

  • December 20, 2010 at 1:06 pm

    #1264767

    well, I guess this solution will work for me since there is no tool.

    Thanks and a merry Christmas everyone !

  • December 20, 2010 at 1:30 pm

    #1264777

    If you're just trying to execute the create statements as a batch, SQLCMD is the tool.

    You could, with only a little bit more work, take advantage of PowerShell for the same thing.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • December 20, 2010 at 2:41 pm

    #1264820

    Grant Fritchey (12/20/2010)

    If you're just trying to execute the create statements as a batch, SQLCMD is the tool.

    You could, with only a little bit more work, take advantage of PowerShell for the same thing.

    Yes, that is the approach I would use. I would use Powershell to get the list of files to be executed, loop through the list and call sqlcmd.exe with that file.

    Several ways you can manage this so the scripts are executed in the appropriate order. Two off the top of my head are:

    Use a text file and list each file to be executed in the text file, in the order they need to be executed.

    Or, use a naming standard for the scripts that order them in the directory. For example, preceded each filename with a number (e.g. 001 - First Script.sql, 002 - Second Script.sql, etc...)

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • December 21, 2010 at 4:37 am

    #1264991

    As this place is sponsored by redgate, why not have a look at their tools - sqlpackager (if its still called that!) probably would do what you want!

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

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