Monday, June 20, 2016

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.

5 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. can you share an example sending a parameter to the SP?

    ReplyDelete
  4. This is the one and only time I have ever followed a link from a search and gotten the EXACT response to my request with no extra info or unnecessary complexity. It works. Period. Thank you

    ReplyDelete
  5. content get override every time when we run above command.Is there any possibility to add content instead of overriding because batch process will run everyday and i need to maintain the logs

    ReplyDelete

Popular Posts

Pageviews