"Deferred prepare could not be completed" error when using local database as linked server

Question

I created a Linked Server from my local Sql Server, as given in

http://sqlserverplanet.com/dba/local-linked-server

However when I try to execute a stored procedure in Linked Server, it throws:

Deferred prepare could not be completed

error and invalid object name 'table name'

Answer 1

Have you missed something in your object name. It should be always like Database.User.ObjectName (for e.g. Northwind.dbo.Customers)

Give complete object name when running queries via Linked servers.

Query for Stored Procedure may be like this when executing on Linked Servers:

Select  *
From    OPENQUERY([COM2\SQLEXPRESS], 'Exec Northwind.dbo.CustOrderHist ''CUST1''') as TB1

Check with options like SET FMTONLY OFF when executing Stored procedure.

Follow this link for OPENQUERY: http://msdn.microsoft.com/en-us/library/ms188427.aspx

Answer 2

Even if you have named a column incorrectly in your query, you are going to see this error. Example:

select *
from openquery(
     lnksrv
    ,'select top 10 * from db.schema.table where colunm = 10'
)

and the column name is column, not colunm.

Bottom line is check the actual query to be sent to the remote server for correctness first, then wrap it in openquery and re-test.