Support Questions
Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.

Execute Stored Procedure of MSSQL in Nifi

Hi ,

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.

Thank you.

3 REPLIES 3

Super Guru

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.

New Contributor

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: 

nifi_exec.png

I used this sample code to test it: 

USE [your database configured in the JDBC Connection Pool];

 

SELECT GETDATE() AS currentdate
INTO dbo.testtable;

GO

 

--procedure used in NiFi PutFile processor

CREATE PROCEDURE dbo.insertdate
AS
BEGIN
  INSERT INTO dbo.testtable
  SELECT GETDATE();
END

GO

Explorer

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.

Thanks