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.
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.
As my server name "arka-PC\SQLEXPRESS" and using Database as "DEMO", so in this case the command will be,
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:
In the next tutorial we will see how to put a bat file in a Task Scheduler in windows server.
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.
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeletecan you share an example sending a parameter to the SP?
ReplyDeleteThis 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
ReplyDeletecontent 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