Executing a stored procedure 

Executing a stored procedure 

Hi All,

I have cretated a stored procedure in SQL database and I am trying to execute from outsystems and it is throwing errors.I am following below syntax to execute a stored procedure.

EXEC [DBName].[dbo].usp_TestProc @input
Where DBName=DatabaseName  ex:Sample

When I use above syntax,I am getting below error:

Error In Advanced Quey:"usp_TestProc".Database "Sample" does not exist.Make Sure that the name is entered correctly.

EXEC [DB User Id].[dbo].usp_TestProc @input

Where DB User Id=Login Id to connect to database "Sample".Ex:TestLogin

When I use above syntax,I am getting below error:

Error In Advanced Quey:"usp_TestProc".Could not find stored procedure.

Also I have tried another option as shown below,but not working.

EXEC [dbo].usp_TestProc @input .


2 scenarios

1.) the database where the sp is stored is an outSystems generated db

For this case you should grant OutSysUpdate User execute permissions to the SP and then it should work

like this EXEC [usp_TestProc]

2.) the database is an external DB that you created and use integration studio to bring into OutSystems. Use this code

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

EXEC [usp_TestProc]

The first line that looks like a comment is actually used by OutSystems to figure out which database to connect. It retrieves the connection used by a referenced table (Customer). So you have to reference at least one table via Integration Studio so that you get a connection to the database. Of course the user that you used to set up your integration to the database must have execute permissions for the Stored Procedure