Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Execute Stored Procedure of MSSQL in Nifi

avatar
Rising Star

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.

7 REPLIES 7

avatar
Master 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.

avatar

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

avatar
Contributor

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

avatar

Yes, I meant PutSQL

avatar
Explorer

@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

 

https://community.cloudera.com/t5/Support-Questions/Execute-Postgres-Procedure-in-Nifi/m-p/358798#M2...

 

Could you please help.

 

Thanks

Mareena

avatar
Explorer

Can we capture the output parameter values from the MSSQL stored procedure using PUTSQL? like?

RRG_0-1694607194890.png

 

 

 

avatar

I dont think you can use PutSQL for this. Try ExecuteSQL or you might have to use ExecuteScript processor as other posts suggest:

https://community.cloudera.com/t5/Support-Questions/Does-ExecuteSQL-processor-allow-to-execute-store...

https://stackoverflow.com/questions/72348844/get-output-of-stored-procedure-using-putsql-in-nifi

If that helps please accept solution.

Thanks