MS SQL Server Deploy Stored Procedures

Without being too technical about, I would like to know the usual steps that are taken to deploy a Stored Procedure (Ex. A query that pulls the overnight orders) so that the Server (Windows) can trigger the Stored Procedure at a specified time and perhaps save a csv copy of this file in a predefined location.
Benki CanosoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
It could be as simple as adding the stored procedure to the database then creating a single-step job that calls it and scheduling the job as desired.
Pinal Dave's article on exporting to CSV covers the file export nicely.

Edit: Also see previous thread on EE
scheduled export to csv
0
Dung DinhDBA and Business Intelligence DeveloperCommented:
Hi,

In case, your .csv files is on local server, I mean that your data source  and csv files are on the same server you can use the solutions as SimonAdept's suggestion.

However, if your data source is from another server or you want to export .csv files and transfer them to anywhere, you should use SSIS tool. Because, it gives you the simpler / more flexible methods to help you export .csv file.

Create one SSIS package and call your store procedure. After then, you need also create a SQL job to call the package. You can see image below, I use 2 components File System Task and FPT Task. One is for copying file in local network and another is for copying file in external network. With File System Task, I will copy file to D:\ExportCSV\ folder after exporting data.
ExportCSVFiles.PNG
You can refer to article Exploring SSIS - Export to csv file for more detail.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.