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

sql-server sql-server-2008 stored-procedures linked-server
Anand B picture Anand B · Mar 1, 2013 · Viewed 56.4k times · Source

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

Vishal Vaishya picture Vishal Vaishya · Mar 8, 2013

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

Related questions

How do I find a stored procedure containing <text>?

I need to search a SQL server 2008 for stored procedures containing where maybe the name of a database field or variable name.

Read More
SQL Server Insert if not exists

I want to insert data into my table, but insert only data that doesn't already exist in my database. Here is my code: ALTER PROCEDURE [dbo].[EmailsRecebidosInsert] (@_DE nvarchar(50), @_ASSUNTO nvarchar(50), @_DATA nvarchar(30) ) AS BEGIN INSERT INTO EmailsRecebidos (De, Assunto, …

Read More
SQL Server: Invalid Column Name

I am working on modifying the existing SQL Server Stored Procedure. I added two new columns to the table and modified the stored procedure as well to select these two columns as well. Although the columns are available in the …

Read More