Hi Clayton,

Have you tried to set parameters as "expand inline"? Try writting the procedure's name in square brackets.

I used an advanced query to execute a stored procedure (with no parameters) and the SQL statement i used is as simple as: EXEC [my_proc]

Hi Clayton,
 
Since it's hard to detect where something might have gone wrong, let me guide you through the process of creating a stored procedure and executing it.
 
1 - To start off, open SQL Server Management Studio, or a similar program, in order to login to the database.
2 - Login to (local)\OUTSYSTEMS using Windows Authentication
3 - Click on the "outsystems" database, under "Databases"
4 - Right-click on it, and select "New Query"
5 - Execute the following query:
 
use outsystems;
go
create procedure test1
as
 select name from ossys_espace
go
 
exec test1
 
6 - Now your stored procedure should show up on the tree, under outsystems > Programmability > Stored Procedures


 
7 - Right-click the stored procedure, and click "Properties"
 
8 - Click the "Permissions" option, and then the "Search..." button on the right side.
 
9 - Type OSRuntime and click on "Check Names"
 
10 - Click Ok
 
11 - Check the "Grant" checkbox, on the "Execute" line, to give him execution permissions.
 
11 - Now, on your eSpace, create an advanced query, and just type
 
exec test1
 
12 - You need to have an output structure, with a text field. I've attached a sample eSpace where this works, you just need to publish it, and test the query in Service Studio!
 
I hope this helps. Let me know if this solves your problem, or if you have other questions.
 
Regards,
 
Paulo Tavares

Hi Clayton,

i've once had to do something similar and you have to use one expand inline parameter as Fernando said before.

You have to assign to the inline parameter the instruction you want to execute like this: "'Execute your_stored_procedure ''" + myfirstparametervalue + "'', ''" + mysecondparametervalue+ "''," + mythirdparametervalue + "'"

Just another tip, if you need to run it in a linked server you should use an open query to run it, like this:
SELECT values_wanted FROM OPENQUERY (YOURLINKEDSERVER,@SQL) where @SQL is your expand inline parameter

Cheers, 
RNA

Hi,

Does your user (runtime user defined in outsystems configuration tool ) have permission to execute that stored procedure?

Cheers, 
RNA

Hi Rui,

 am getting the below error while trying to access the SQL Server Stored Procedure. Pls let me know what can be done to resolve this issue.

Thanks & Regards
Ravi Vakkalanka  

 

Hi Ravi

you need to grant execute permission on that stored procedure to the runtime user. 

If you dont have admin role Check with your DBA on how to do that

cheers 

rui

Hi Paulo,

I get this kind  of message :"the execute permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'" when i execute the query, the 'xp_cmdshell' is an extended stored proc

Hi Diana, 

you need to grant execute permission on that stored procedure to the runtime user. 

Cheers, 
Ricardo

I'd second Ricardo's suggestion here.

Happy new year!

Hi Diana,

If you're using Oracle, you might want to check out this:
https://www.outsystems.com/forge/component/392/oracle-connector/

Edit: I just noticed from your error msg that you're not...

Hi Ricardo,

When I grant the permission (execute to public) and tried to execute the  advanced query again, still got an error:
"the xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information."

Diana,
have you Checked out Paulo Tavares reply on this topic, specially the part about granting permissions to the runtime user of the platform.?

Usually the sql runtime user is OSRuntime, but if you're not sure, confirm it in the Configuration Tool, tab: Database, section: Runtime (you need access to the front-end to do that).

Hi Ricardo,

I still got the same error when i execute the stored proc in sql using OSRuntime user, only the admin user can do the execution without getting the error, since i guess the extended stored proc is for sysadmin users only. Could it be possible to call the admin user when the stored proc is executed in outsystems instead of the OSRuntime user?

HI Diana,

xp_cmdShell is a very dangerous command in SQL Server since it will enable you to execute operating system commands directly from the server.

Being such a dangerous command it is very well protected and you will need to ask your DBA folks or yourself to configure its execution properly.

However my recomendation is that you find an alternative way and discontinue the use of the xp_cmdShell.

It looks like the execution of xp_cmdShell is already enabled (it's such a bad prectice that this command comes disabled by default)

After that you should create a proxy account under which the xp_cmdshell will run with its security context.

Finally you need to grant the execution of xp_cmdShell it selft to the user of the calling connection (OSRuntime eventually)

You have some digging to do, these links will help you get started:
https://stackoverflow.com/questions/2016669/getting-execute-permission-to-xp-cmdshell
https://support.microsoft.com/kb/890775
https://www.practicalsqldba.com/2012/03/how-do-you-secure-sql-server-instance.html

Best Regards

Rui

Hi Rui,

This error: '##xp_cmdshell_proxy_account##' credential exists and contains valid information, lead me to this answer and it works fine. I can finally execute the query in outsystems even in SQL using OSRuntime user.

https://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/684/the-xp_cmdshell-proxy-account-information-cannot-be-retrieved-or-is-invalid-verify-that-the-xp_cmdshell_proxy_account-credential-exists-and-contains-valid-information

We're trying to run an SSIS package in outsystem, that's why we used the xp_cmdshell in executing the stored proc in sql, would it be possible to run an SSIS package direct in Outsystem? so we could limit the use or access of the xp_cmdshell.

thanks