How to select from stored procedure / place stored procedure in view sql server?
One of vendor synchronization system only recognizes the SELECT statement and cannot execute the stored procedure.
So, instead of using EXEC MyProc, I have to find a way to call SELECT with stored procedure.
I query the following SELECT statement.
SELECT * FROM
OPENQUERY(SERVERNAME, 'EXEC MyProc')
To find out what is my SERVERNAME,
(1) I check SELECT name from sys.servers and place the result in SERVERNAME value. But it did not work.
(2) So, I follow to create LOCALSERVER linked server according to http://sqlserverplanet.com/dba/local-linked-server.
I got this error :
Cannot process the object "exec usp_sample". The OLE DB provider "SQLNCLI11" for linked server "LOCALSERVER" indicates that either the object has no columns or the current user does not have permissions on that object.
What I want to do is once I can execute above SELECT statement, I will create it as view table and call the statement like SELECT * FROM MYVIEW.
Please advise if there is any better way. Thank you.