Run Stored Procedure from Batch file (MS SQL & Oracle)

In this post we will lean how to run a stored procedure from a batch file (.bat) externally. This is used very often to run a schedule job set in the server. The whole job logic is written in the Stored procedure and we are executing that from an external batch file. So what we have to do to run this, lets see.

We will use the sqlcmd.exe file to run the stored procedure from command.  First of all create a Stored procedure in your MS. SQL server.

CREATE PROCEDURE SP_TESTSP

AS

BEGIN

      print 'THIS IS A TEST MESSAGE. LOGGED AT ' + CONVERT(VARCHAR, getdate(), 120)

END

GO

I named this as SP_TESTSP, go with your name and now open a Notepad or any other editor and write down the following script.

sqlcmd -Q "exec <STORED PROCEDURE NAME>" -S <SERVER NAME> -d <DATABSE NAME> -o <OUTPUT FILE NAME WITH PATH>

As my server name "arka-PC\SQLEXPRESS" and using Database as "DEMO", so in this case the command will be,

sqlcmd -Q "exec SP_TESTSP" -S arka-PC\SQLEXPRESS -d Attendance -o D:\SPOutput.txt

Before run this script create a file name with SPOutput.txt in the D:\ drive or where you want to save. The output of the stored procedure will be captured.

Now save the file with an extension of  ".bat". Set all the privileges and run the file by clicking twice. Check the log file, it will contain the result of the executed stored procedure.

Output:

THIS IS TEST MESSAGE. LOGGED AT 2016-06-19 23:52:48

In the next tutorial we will see how to put a bat file in a Task Scheduler in windows server.

Read More

How to execute or perform a task at a specific time like 12 at night in SQL Server

Here in this example I will show you how to execute a task or call a store procedure at a specific time(like at midnight) using SQL Server or I can say how to schedule a job at a specific time. Some times you have to perform some thing daily, weekly or at a specific gap of time. Repeat the same task every time by a person is little bit difficult. So in that case SQL is here to solve your problem using SQL Job Scheduler. This is a task you fixed in your server, set timer on at particular time and write down the SQL query to perform. That's all you have to do. Lets see how to schedule your Job at a specific time.

Open your Management Studio and check the SQL Server Agent. Start the SQL Server Agent by following steps.

Now go to Job section in your SQL Server Agent and click to New Job.

After selecting the New Job a new window will be opened with all the properties of  a New Job. Now fill up each and every field according to your specification. 

In the General tabs write down any name according to your project and left the others as it is.

In the Step tab click on the New button, and a new window will be opened. 

Write a name for your step and your query to be executed and click on the OK button.

In the Schedule tab follow the steps as the image bellow.

Now your job is almost done. The only thing is left to start your job. To start the job follow the step.

After successfully starting of the job you will get a successful alert message.

So, your job schedule is done. Enjoy with your new job schedule. :)

Read More