CODEWITHSUNDEEP
sqlvisual-studio-2008sql-server-2008stored-proceduresjobs
DataGirl
DataGirl

Reputation: 449

SQL Server 2008 Job based on changing Stored Procedures

I have looked through the SQL Server questions and answers and I didn't see an answer to this one, if it is out there and I've missed it, please let me know.

Here's the situation:

I write stored procedures and views that are then run as reports (using Crystal) - this is not the problem. Before I am able to release the reports into Production, I need to have the end users run the reports and check them for errors, etc. In a perfect world, I would have a frozen test environment, but I don't live in a perfect world. Every night everything I place into my test environment is wiped out and every morning anything that is in end user testing needs to be re-added. This means that when I come in the first thing I do is run all of the stored procedure, along with a script that unhides the reports in the program we use.

What I'd like to be able to do is to write a package that would find all of the stored procedures in a folder and execute them to add them to the database and, then, run the script that unhides the reports.

I know how to set up an SSIS package to run a stored procedure, but I don't know how to set one up that would run an ever changing list of stored procedures. Is this even possible? And, if it is, how do I go about starting this up?

I should note that while I have more then 10 years of query writing experience, I haven't used VB since VB 6.0 and I very new to the SSIS and SSRS world.

Thanks in advance!

Upvotes: 2

Views: 351

Answers (2)

Filip De Vos
Filip De Vos

Reputation: 11908

Good old nt shell will do the trick. Run this statement in the folder containing the files. 

for %A in (*.sql) DO sqlcmd -i %A -S <myServer> -d <myDb> -E

if you want to include it in a batch file it could look like

@echo off
for %%A in (*.sql) DO sqlcmd -i %%A -S <myServer> -d <myDb> -E

sqlcmd -i script_to_update_config.sql -S <myServer> -d <myDb> -E

Upvotes: 3

Dan Williams
Dan Williams

Reputation: 4950

This actually sounds like it may be more of a deployment issue than a SQL one. Take a look at Jenkins CI. I believe it's mostly used for code build and deployment, but it can also be used for any automated task.

If you had one SQL file that listed all the changed procs and their associated files you can use that single script to run all the others http://www.devx.com/tips/Tip/15132. For that matter you could just use a scheduled task to run it every morning.

Adding one more step, you could build the file based on the contents of a folder (using a little Powershell script or the like).

I'm not sure trying to do this all within an SSIS package is the right tool for the job.

Upvotes: 1

Related Questions