creating procedure connecting to link server

Question

When I run this query I get results.

SELECT *  FROM [UNI-TEST].[dm_reporting].[dbo].[Adm_Documents]

When I create this prodcedure I get an error

CREATE PROCEDURE loadDMS

AS
BEGIN

SELECT * INTO ESUE_Adm
FROM
OPENQUERY([UNI-DMSDB-TEST], 'SELECT * from Adm_Documents')
END
GO

This is the error. Any help would be appreciated

OLE DB provider "SQLNCLI10" for linked server "UNI-TEST" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Adm_Documents'.

Answer 1

Try giving 

SELECT a.* FROM OPENQUERY(LINKEDSERVERNAME, 'SELECT * FROM DBNAME.dbo.TABLENAME') a

http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling-stored-procedure-on-linked-server/

Answer 2

your syntex seems to be wrong, to access a table using linked server. you are missing the bold part mentioned below:

CREATE PROCEDURE loadDMS 
AS
BEGIN
SELECT * INTO ESUE_Adm
FROM 
OPENQUERY([UNI-DMSDB-TEST], 'SELECT * from DB_NAME.SCHEMA_NAME.Adm_Documents')
END
GO

NOTE: also it is better to use schmename in table which allows sql engine to find the table quickly. say dbo.ESUE_Adm instead of simple ESUE_Adm. but it is not mendatory.

regards

joon