Could not find stored procedure error with mssql-plus node

General
1

Hello Everyone,

I have a problem connecting to a stored procedure. I have also found other topics with similar problem. I went through those topics as well. But i could not find any mistakes on my configuration. May be i am still missing something that i could not simply notice it.

Also i am looking for a way to store a returned value from a store procedure, and forward this value as an output parameter from mssql-plus node to other nodes in the flow.

stored_procedure
stored_procedure713×877 19.9 KB

Any help would be very helpful.

Best Regards,

2

What is the problem? Are you getting an error? what error?

Use a debug node to see where the return value is given then use that in the parameter list of the 2nd query.

3

Hi Steve,
I am only getting "could not find stored procedure dbo.sp_test" error.

But if i set the editor in "Query" mode and pass the parameters by calling the stored procedure like -> dbo.sp_test @serialNumber, @iccid
then it works. Please let me know if you are looking for more details.

Best Regards,

4

try sp_test without dbo.

and / or, try the fully qualified name [database].[dbo].sp_test (where database is the name of the SQL database)

5

I tried only with `sp_test´, also and / or with the fully qualified names:

  1. ´[database].[dbo].sp_test ´
  2. ´[database].[dbo].[sp_test] ´

I am still getting the following error:
error

Do you have other suggestions or recommendation

Best Regards,

6

if your database really called database?

7

No, my database is named 'IoT' and i have replaced the word 'database' with 'IoT'. Like:

  • ´[IoT].[dbo].sp_test ´
  • ´[IoT].[dbo].[sp_test] ´

Best Regards,

8

I am having bother with my SQL Server so cannot help further at this moment (unless you can grant access to your DB)

For now, go with "query mode" Query - and enter the params explicity e.g. exec dbo.MySP @p1, @p2; It is still safer than mustache templating.

I'll try to get my SQL Server instance up and running later.

9

Hi Steve,
Thank you for your reply. Please let me know if you come with some other recommendations once you test it on your SQL serve instance running.

Also about my other query, can you please provide an example to get the return value from a stored procedure and forward it to other nodes? I checked that the returned value on the debug node is always 'null'.

I am not sure if i am doing in a right way. This is how i did:
I defined an output parameter 'returnValue' as 'int' in mssql-plus node, then i passed this parameter to the stored procedure together with other parameters like:

exec [IoT].[dbo].[sp_test]@serialNumber,@iccid,@returnValue

The 'output' property set from the database is null.

returnValue

Best Regards,

10

Will need more info. Can you share the SQL Procedure as a create procedure script?

11

Here is the screenshot of it,

create_stored_proc

12

In which case, you can specify an output parameter in the editor.

  • add a 3rd parameter to the Params UI
  • set it to an output parameter
  • set its name to returnValue
  • set its type to int

or do it in the query...

DECLARE @returnValue INT;

EXEC sp_test @p1, @p2, @returnValue = @returnValue OUTPUT;

SELECT @returnValue AS 'count';
1 Like
13

Hi Steve,

That's awesome. Both ways work perfectly. You just made my day. It was troubling me for a whole day. Thanks a lot.

Best Regards,

14

And also regarding the problem with 'cannot find stored procedure' error, if you come up with any solutions, then please kindly let me know. I would be glad to learn that as well.

Many thanks.

15

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.