Deferred prepare could not be completed ошибка

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

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,9482 gold badges44 silver badges61 bronze badges

asked Mar 1, 2013 at 12:40

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([COM2SQLEXPRESS], '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

answered Mar 8, 2013 at 4:14

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.

answered May 11, 2016 at 17:16

ajehajeh

2,6422 gold badges33 silver badges64 bronze badges

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
«RIBOWEB10SQLEXPRESS» 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

answered Apr 28, 2014 at 12:14

MAXEMAXE

4,9482 gold badges44 silver badges61 bronze badges

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

729k175 gold badges1327 silver badges1455 bronze badges

answered Mar 6, 2014 at 16:13

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.

answered Feb 19, 2019 at 15:22

AMouatAMouat

68515 silver badges27 bronze badges

Источник

Remove From My Forums

Question
I am running the following query trying to return server properties across a linked server. I want to store the results in a table on the server where I an running the query.

DECLARE @BuildClrVersionx nvarchar(128)

SET @BuildClrVersionx =

(SELECT *

FROM OPENQUERY(LKMSSQLXYZ01, ‘CONVERT(nvarchar(128),SERVERPROPERTY(«BuildClrVersion»)’))

I am getting the following errors:

OLE DB provider «SQLNCLI» for linked server «LKMSSQLADM01» returned message «Deferred prepare could not be completed.».

Msg 8180, Level 16, State 1, Line 1

Statement(s) could not be prepared.

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword ‘CONVERT’.

If you have any ideas how I can run this query across a linked server I would appreciate it.

Thanks,

Scott

Answers

Try:

declare @v sql_variant

set @v = (

SELECT *

FROM OPENQUERY(LKMSSQLXYZ01, ‘SELECT SERVERPROPERTY(»BuildClrVersion»)’)

)

select @v

GO

AMB
Yes, It is a pain dealing with the apostrophes.

Code Snippet

DECLARE @LinkServerName nvarchar(128), @SN sql_variant, @BuildQuery varchar(128)

CREATE TABLE #T1 (LinkedServer varchar(128))

INSERT INTO #T1 (LinkedServer)

select NAME

from sys.servers

DECLARE T1_cursor CURSOR LOCAL FAST_FORWARD

FOR

SELECT LinkedServer

FROM #T1

OPEN T1_cursor

FETCH NEXT FROM T1_cursor INTO @LinkServerName

WHILE @@FETCH_STATUS=0

BEGIN

SET @BuildQuery = N‘SELECT * FROM OPENQUERY (‘ + @LinkServerName+‘,»SELECT SERVERPROPERTY(»»ServerName»»)»)’

PRINT @BuildQuery

EXECUTE (@BuildQuery)

FETCH NEXT FROM T1_cursor INTO @LinkServerName

END

CLOSE T1_cursor

DEALLOCATE T1_cursor

GO

DROP TABLE [#T1]

GO

AMB

Источник

Remove From My Forums

Вопрос

Hi, I understand that you cannot include variables in OPENQUERY so the work around is dynamic SQL and I did the following:

DECLARE @etd AS DATETIME = '2014-06-28'
DECLARE @source AS VARCHAR(46)
DECLARE @dbName AS VARCHAR(30)
DECLARE @query AS VARCHAR(MAX)
DECLARE @openQuery AS VARCHAR(MAX)

SELECT TOP(1) @source = [Source], @dbName = DbName
FROM dbo.SomeTable
WHERE SystemCode = 'SomeSystem'


SET @query = 'SELECT *
	FROM [' + @dbName + '].dbo.Table1 t1
	LEFT JOIN [' + @dbName + '].dbo.Table2 t2 ON t1.bookno = t2.tranno

	WHERE (YEAR(t1.etddate) = ' + CAST(YEAR(@etd) AS VARCHAR(4)) + 
	' AND MONTH(t1.etddate) = ' + CAST(MONTH(@etd) AS VARCHAR(2)) + 
	' AND DAY(t1.etddate) = ' + CAST(DAY(@etd) AS VARCHAR(2)) +')'


SET @openQuery = 'SELECT * FROM OPENQUERY([' + @source + '],''' + @query + ''')'


EXECUTE (@openQuery)

When I use «SELECT @openQuery» I don’t see anything wrong with the query string, but once I execute it, I received the following error:

OLE DB provider «SQLNCLI11» for linked server «xxx.xxx.xxx.xxx,1433» 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 ‘xxxx.dbo.t1’. (where ‘xxxx’ is the table name variable)

I’ve been searching for answers but I cannot find any, I really need your help guys.

Ответы

Hi SaravanaC, I would prefer to pass the table name and data source name as variable because it may changed in the future, do you think it’s the problem?

First of all, using OPENROWSET as suggested by Saravana is not a good idea. It is unlikely to resolve your problem, and OPENROWSET is disabled by default.

Next, the table name should not change, assuming a properly designed database. The name of the remote server may change, and so may the database. But the table name should not. If the server changes, this can easily be addressed by dropping and recreating
the linked server. It is also possible to encode the database name in the definition of the linked server.

Your earlier posts were not clear, because you said:

Invalid object name ‘xxxx.dbo.t1’. (where ‘xxxx’ is the table name variable)

xxxx should be the database. But if xxxx is something that starts with @ (you say it is a variable), it is not going to work out.

I can understand that you prefer to anonymise table and database names, but in process you may have altered the input and output, so any clues of what might have gone wrong may be lost. In any case, the error message simply indicates that the table is missing,
so you would need to research whether the database exists and that there is a table with that name in the database.

Finally, I would suggest that you construct the query this way:

SET @query = ‘SELECT *
   FROM + quotenme(@dbName) + ‘.dbo.Table1 t1
   LEFT JOIN ‘ + quotename(@dbName) + ‘.dbo.Table2 t2 ON t1.bookno = t2.tranno
   WHERE t1.etddate = »’ + convert(char(8), @etd, 112) + »»
SET @openQuery = ‘SELECT * FROM OPENQUERY(‘ + quotename(@source) +
dbo.quotestring(@query) + ‘)’

You find the quote for dbo.quotestring() here:
http://www.sommarskog.se/dynamic_sql.html#quotestring
This entry also explains why you should use quotename.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Предложено в качестве ответа
  
  16 июля 2014 г. 10:14
- Помечено в качестве ответа
  silvershadow58
  17 июля 2014 г. 6:16

Источник

December 29, 2008 at 3:05 pm

#217027

OLE DB provider «SQLNCLI» for linked server «Dev3» returned message «Deferred prepare could not be completed.

I am getting the above error when running this query

select distinct stateID

from Openquery (DEVstate, ‘Select * from vwstatePA’)

I cheked the open row set functionality, it is enabled too.
December 29, 2008 at 3:45 pm

#918646

Is this a remote SQL Server or another data source?
December 30, 2008 at 12:18 am

#918796

Hi

Seems to be a registry issue. I read somewhere that reinsatlling mdac might solve the issue not sure though. Saw a lot of links in google about the issue. Try googling.

«Keep Trying»
December 30, 2008 at 6:14 am

#918975

Yes its a Link Server where am querying from.
noeld

SSC Guru

Points: 96590

December 30, 2008 at 10:01 am

#919127

Mike Levan (12/29/2008)

OLE DB provider «SQLNCLI» for linked server «Dev3» returned message «Deferred prepare could not be completed.

I am getting the above error when running this query

select distinct stateID

from Openquery (DEVstate, ‘Select * from vwstatePA’)

I cheked the open row set functionality, it is enabled too.

You are missing the «alias»

select distinct a.stateID

from Openquery (DEVstate, 'Select * from vwstatePA’) a

* Noel

Viewing 5 posts — 1 through 4 (of 4 total)

Источник

Solution 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([COM2SQLEXPRESS], '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

Solution 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.

Solution 3

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!

Solution 4

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
«RIBOWEB10SQLEXPRESS» 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

Comments

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

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’
- I just ran into this and it turned out that the table name was case sensitive in openquery
Just ran into this recently when trying to help someone else out with a linked server. Wish the error message was more descriptive.

Recents

Источник

Question

Answers

Вопрос

Ответы

Solution 1

Solution 2

Solution 3

Solution 4

Related videos on Youtube

Comments

Recents

Related