Let's create a table that we want to use for this example
CREATE TABLE dbo.Customer ( Id INT, FName VARCHAR(50), LName VARCHAR(50) )GO INSERT INTO dbo.Customer
VALUES (1, 'Aamir', 'Shahzad'), (2, 'Robert', 'Ladson'), (3, 'John', 'Rivers')
Create Stored Procedure that will return all results. In real time scenario you might have multiple SQL statements in SP such as create temp table, common table expressions, updates, deletes , and finally Select statement that is going to return us the results. All depends upon requirement for which the Stored Procedure is written for.
CREATE PROCEDURE dbo.Usp_customer
AS BEGIN SELECT ID, FName, LName FROM dbo.Customer END
Let's create view and execute our dbo.usp_Customer stored procedure by using OpenQuery.
CREATE VIEW dbo.vw_Customer
AS SELECT * FROM OPENQUERY([SERVERNAME\MSSQL2008], 'EXEC Testdb.dbo.usp_Customer')
When we try to execute create view statement , we will get below error if Data Access is not enabled on SQL server.
Msg 7411, Level 16, State 1, Procedure vw_Customer, Line 3
Server 'SERVERNAME\MSSQL2008' is not configured for DATA ACCESS.
Let's enable DATA ACCESS on SQL Server Instance
EXEC sp_serveroption 'SERVERNAME\MSSQL2008', 'DATA ACCESS', TRUE
Once DATA ACCESS is enabled , Run the Create View statement. It should run fine.
Use created view in Query to confirm if all working fine.