Stored Procedures and execute in a batch file/program to execute them automatically against SQL db

[rdrunner400]

Hi,

I am supporting an application which we deploy onto remote servers. That goes fine.

My Question is .... we deploy the Application on a Server and at each release we have to run about 15 - 20 Stored procedures on the server. There are 10 sites that we deploy it on ... and I am looking for a way to run all the SP's that are included in each release either as a Batch that can be included into the Installer .... or as a separate process that I as the person who is installing the new release onto the server can execute similiar to the old batch file ... where you added the various lines that you wanted to run.

The version of SQL that resides on the server is currently 2008 R2 Express. In all cases there are about 10 pc's that has an application installed that connects remotely to the server.

Currently if the SP's are rather large in can take up to an hour to run some of them hence why i am looking for another way.

Any help would be greatly appreciated.

rdrunner40

 

[SQLBill]

You can put all the stored procs into one 'calling' stored procedure.

CREATE PROCEDURE callingproc
AS
EXEC proc1
EXEC proc2
EXEC proc3

Then just execute the 'calling' procedure.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

I'm confused about something. You mentioned 15-20 stored procedures, and then you mentioned an hour to create them.

Does it take an hour to create ALL of the stored procedures, but would only take seconds if you just created the 15-20 that you actually need?

There is a "batch file-ish" way you can do this, and I actually recommend it. There is a command line utility for SQL called SQLCMD. With this utility, you specify the server, database, login (trusted or sql), you can specify input files and output files. If you don't have SQLCMD on your computer, you can easily install it directly from Microsoft's website.

Take a look here:

http://technet.microsoft.com/en-us/library/ms162773(v=sql.100).aspx

And here:

http://www.microsoft.com/en-us/download/details.aspx?id=16177

(Scroll down to Microsoft SQL Server 2008 Command Line Utilities)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rdrunner400]

HI,

Thank you for the insight ....

I have to add a little bit more .... then .... some of the stored procedures are quite large and can take up to an hour to execute on db containing around 200k records.

I also have to be able to have it run on multiple servers after it deployed as we are supporting currently 5 sites in total with each server with a different name.

So i would like to have a unique way of just creating them in a single file and possibly having a CASE statement inside the complied file that I could list all the server attributes and then if I had a single Filename.EXE that I could copy to each server and when i clicked on it .... it would just execute and then read the variable off the server and install to the correct server reading the server name in the case statement.

It has to be simple cause I want to pass the deployment of this to the relevant IT staff to deploy.

Thank you so far for the help ... I am preferring to have the above in some sort of a complied exe that i can store on a weblink and then just get the IT staff at each site to down load and install.

Currently with this weeks release we have 5 Databases to upgrade 1 at each site .... takes about 30mins per site. and then this week i have a total of 37 store procedures to run. SO it will take in total about 10hrs to do this ... which i currently don't have time for hence the above.

Regards

rdrunner40

 

[gmmastros]

Here's what I would do.....

1. Investigate the stored procedures to see if they can be made faster. Taking an hour to process 200k rows is excruciatingly slow and I would bet that it could be made a lot faster. This is an assumption (obviously) because I don't know what the procedure is doing or how complicated it may be.

2. I would write an app that your users can run (the IT staff at customer sites). This app would download relevant .sql files from a web server, then it would have a log in screen for their database. I would enumerate the list of SQL Servers, have the user log in (username and password) or skip this step in case of windows authentication, and then pick the database. This app would store a small file on the local computer so that it can "remember" the server, username and database name (not the password).

With this structure, you would be able to call your customers, tell them to fire up the application and run it. It would automatically download the .sql files and run them.

Of course, this would take some time to set up, but it is a much better long term solution than what you are currently doing.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

If this is a .Net application you can add pre or post build actions to the setup project. But because you are talking about executing (not creating or altering) 15-20 SPs on each one of the servers, it is more complicated to carry out- it would the equivalent of launching the execution of the SPs from SQLCMD. What I would suggest goes along the lines of what George said, with a twist. Instead of having the users run the app, build the app so that it does what it needs to on each one of the servers. Define X connection strings in your app, one for each server, then go through each one executing the stored procedures. With proper error handling in place you should be fine.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)