Execute number of Stored Procedures

Question

Post reply

Execute number of Stored Procedures

piet.kouwer

Old Hand

Points: 354
More actions
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

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

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

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 1

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

piet.kouwer Old Hand Points: 354 More actions · Answer 2

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

jason.spangler Say Hey Kid Points: 684 More actions · Answer 3

jason.spangler

Say Hey Kid

Points: 684

December 20, 2010 at 12:51 pm

#1264760

use SQLCMD.EXE

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 4

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" ....

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

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

piet.kouwer Old Hand Points: 354 More actions · Answer 6

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

Thanks and a merry Christmas everyone !

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

RichB SSCrazy Eights Points: 9651 More actions · Answer 9

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!