I am trying to configure nifi Execute Process to connect to MSSQL and execute the store procedure .
I followed this link https://community.hortonworks.com/questions/26170/does-executesql-processor-allow-to-execute-stored.... where in @M. Mashayekhi has provided the steps how he connected to MSSQL and executed the Stored Procedure.
@Mashayekhi , I wanted to know how your execute process configuration screen looks like and also please let me know if any additional client tools are required to be installed so that I can execute the stored procedure.
In addition to the approach (sqlplus with ExecuteProcess / ExecuteStreamCommand) mentioned in the other post, you could also connect via a scripting language using ExecuteScript, calling Connection.prepareCall() and such as described here. I have an example on how to interact with SQL using Groovy and ExecuteScript here.
Very old question, but since googling led me here I'd like to share my own perspective with others who arrive later.
The simplest solution is using the PutFile processor.
You can simply enter your EXEC or EXECUTE statement, see screenshot and T-SQL code below:
I used this sample code to test it:
USE [your database configured in the JDBC Connection Pool];
SELECT GETDATE() AS currentdate
--procedure used in NiFi PutFile processor
CREATE PROCEDURE dbo.insertdate
INSERT INTO dbo.testtable
Thanks for your help.
Please, are you talking about PutSQL Processor.
I have a procedure to run in a DB against an incoming flow files.
Please, are you saying i will call the procedure name using the EXEC command.
@dominikschauer : I tried to use PutSQL processor to run a Postgres procedure. Unfortunately, i ended up with an error. I have given the details in the below thread
Could you please help.
I dont think you can use PutSQL for this. Try ExecuteSQL or you might have to use ExecuteScript processor as other posts suggest:
If that helps please accept solution.