26

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'

MAXE
  • 4,978
  • 2
  • 45
  • 61
Anand B
  • 2,997
  • 11
  • 34
  • 55
  • 1
    I just ran into this and it turned out that the table name was case sensitive in openquery – gordy Apr 28 '15 at 19:30

5 Answers5

21

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

Vishal Vaishya
  • 596
  • 3
  • 15
  • 1
    Just ran into this recently when trying to help someone else out with a linked server. Wish the error message was more descriptive. – Dave Johnson Mar 08 '13 at 04:15
7

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.

ajeh
  • 2,652
  • 2
  • 34
  • 65
1

I had the same error trying to query through a linked server. However, I was querying a view on the target server, not a stored procedure.

The target server's view was created like this:

CREATE VIEW vABC AS
    SELECT ... FROM Table1
    UNION ALL
    SELECT ... FROM Table2

To fix the problem, I did an alter view, and wrapped the two UNION statements in a subquery, like this:

CREATE VIEW vABC AS
    SELECT * FROM (
        SELECT ... FROM Table1
        UNION ALL
        SELECT ... FROM Table2
    ) T

Must be some metadata issue with the original view.

Hope this helps you!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3377487
  • 41
  • 3
1

Same problem for me: I resolved it just fixing the "target" object, that was not named correctly.

In my case I was searching for [testDb].[Business.Customer] table, but the correct name was [testDb].[Business].[Customer]...same error as yours:

Il provider OLE DB "SQLNCLI10" per il server collegato "RIBOWEB10\SQLEXPRESS" ha restituito il messaggio "Deferred prepare could not be completed.".

I think SQL Server tries to dynamically resolve names that don't match directly with real objects, but not sure of that.

Quite easy, I hope this helps :)

MAXE
  • 4,978
  • 2
  • 45
  • 61
0

I had the same issue, the problem I had was the user specified in the linked server connection did not have access to the database I was trying to access.

AMouat
  • 685
  • 15
  • 27