PROBLem to call a stored procedure in SSMS from the OS platform

PROBLem to call a stored procedure in SSMS from the OS platform

  

I have followed this link https://www.outsystems.com/forums/discussion/14867/how-to-execute-stored-procedures/


but it is not working. I have a stored procedure in the SSMS and I need to call it whenever someone click a button in frontend (using the OS platform). The button has an action where there is only one SQL block. The stored procedure will run a job (made in SSIS) in SSMS (sql server management studio).  To call it I need to run the  stored procedure in such SQL block


EXEC msdb.dbo.sp_start_job N'[name_of_the_stored_procedure']   . 

It is not working. The error is COuld not find stored procedure.

 tHE Next command is the key, most likely but I do not understand why you comment this line 

/* %LogicalDatabase%=GetLogicalDatabase({Customer}) */

and how to use in my particular casE?  Customer ... what is it? Table? (but in my case there is no need to call a table) I need to run a sp from the Programmbility folder.. 

Thanks!




Hello Jorge,

When looking for information and to understand something, it is important to read carefully. In the link you mentioned, it is explained what is the reason to the "commented" code (that must come before the command itself:

Use the Logical Database to hint the platform on what database connection to use.

Without this, the server probably will not find the stored procedure.
You need to chose the name of an entity in the same database where the stored procedure is.

Cheers.

exactly. I have written an exposed entity in OS that is also present in the same database where the stored procedure is :) And it still not work. I think it is about the user not being able to run the sp. 

instead of customer I wrote there an entity I have in the OS (exposed). 


In the service center there is an username in the


Database Connection


 However to update the xif DB extension the account/username is not the one present  in the username of DATABASE connection (in OS) but another one.... 

it is almost there... it seems there was a connection with tthe stored procedure i've written in SSMS but it throws this error in OS:


The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.   


EXEC msdb.dbo.sp_start_job N'[name_of_the_stored_procedure']   

Yeah... The user you use to connect to the database in your extension needs to have permissions to run the stored procedure...

Yes. That part (permission to connect OS to SSMS) was already done and it worked.

This line: 

/* %LogicalDatabase%=GetLogicalDatabase({one_exposed_entity_in_my_OS}) */

had the * close to the % and it should have one space! 

 Now I know that the account I used to run this stored procedure has no permissions to run the sp_start_job (the stored procedure I called from OS will run this another stored procedure so the job can be done) . Need to ask permission to give the possibility the account can run the stored procedure of start_job . And it will be done. :)