Execute Stored Procedure of MSSQL in Nifi

[#1]

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.

[#2]

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.

[#3]

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
INTO dbo.testtable;

GO

--procedure used in NiFi PutFile processor

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

GO

[#4]

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

[#5]

Yes, I meant PutSQL

[#6]

@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

[#7]

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

[#8]

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