Stored procedure on remote server

Stored procedure on remote server

  
Hi All,
         I have come across this scenario where I need to get some data from a remote server. I was able to add the connection to database connections just fine. I then went ahead and published the new extension on server. I can query all the tables just fine but now  need to execute a stored procedure on that database. I have got the execute permission for that and tried it in management studio and it worked fine. Now when I tried to execute it from advanced query, it is saying  that it could not find stored procedure. 

Do I need to add this server as linked server ?

Regards
Hello Khushwant,

You might need to add it as linked server.
However, the error you're getting it's not very self-explained.
Don't know the version of your database but, try to run the following query on an advance query to check if you can get the store procedure name:

SELECT NAME
FROM SYS.ALL_OBJECTS
WHERE type='P'


or to filter and return only user SP and the condition:

SELECT NAME
FROM SYS.ALL_OBJECTS
WHERE type='P' and is_ms_shipped = 0


I think that you'll have no access to this objects because you don't have direct access to the database.
Can try it and give me some feedback, please (but I don't think you can skip that task of making it a Linked Server)..
Good luck.

Kind Regards,
Gonçalo M.


Hi Goncalo, 
                   I was able to run the second query but the result is showing only 2 SP's . "Dequeue_ossys_Email_Status" and "Dequeue_ossys_Event" .

Now when I am executing the stored procedure with

2 part identifier i.e schema.spname  => error is "could not find stroed procedure"

3 part identifier i.e. dbname.schema.spname => error is "Could not locate entry in sysdatabases for database "dbname" .No entry found with this name. Make sure that name is entered correctly.

And I couldn't understand the need of linked server if I have provided the connecting string and logical database name is setup. Why can't I execute the stored procedure the same way as any other advanced query ? Is the underlined architecture different for stored procedures ? Thanks.

Regards
Hi,

Check the help section "Choosing the database" in http://www.outsystems.com/help/servicestudio/8.0/default.htm#Using_Data/Advanced_Logic_Canvas.htm

It explains how to tell advanced queries that do not have any entities on what database connection they need to run.

Regards,
João Rosado
Thanks Joao,

After adding /* %LogicalDatabase%=GetLogicalDatabase({Entity Name}) */  to the top of the advaced query , it started working great. 

One more question, According to help link 

"
Advanced queries are tested and run in the database using the Runtime user specified in the Configuration Tool. You need to make sure this user has permissions to run the SQL statements you specified in the advanced query, otherwise an error will be displayed when you test the query or execute the advanced query at runtime."

But I don't have OSRuntime user on that remote database although the user defined in the connection string has all the necessary permissions. Advanced query works fine, Could you please explain a little bit about it ? 

Regards