Deferred prepare could not be completed ошибка - Решение и исправление самых разных ошибок на TopOshibok.ru

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

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,9782 gold badges45 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([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

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 badges34 silver badges65 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
«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

answered Apr 28, 2014 at 12:14

MAXEMAXE

4,9782 gold badges45 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

734k176 gold badges1332 silver badges1460 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

69515 silver badges27 bronze badges

Источник

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)

Источник

Problem

User launches ‘Transfer — Publish Data and Structures — Define Data Mart’. User creates a new cube definition template, with a template defined as MS SSAS.

User attempts to save the template. User receives an error.

Symptom

Error #1 — English:

Invalid object name ‘DMACTT’.

Invalid object name ‘DMACT’.

Error #1

— Swedish:

Error #2

The ‘ConnectionString’ required element is missing under

Envelope/Body/Execute/Command/Alter/ObjectDefinition/DataSource at line 21, column 25 (namespace

‘http://schemas.microsoft.com/analysisservices/2003/engine’

Error #3

(screen):

The SELECT permission was denied on the object ‘DMACTT’, database

Error #3

(Event Viewer):

Cause

There are several potential causes for the above errors:

Scenario #1 — DataMart database location has not been saved for this database connection.

Inside Cognos Controller, you need to define and save a DataMart definition (to the relevant datamart RDBMS database) before attempting to try to save a CUBE definition (to the Controller application database repository).

Scenario #2 — The Data Mart RDBMS database has not been correctly configured inside Controller Configuration
Scenario #3 — Problem with the Data Mart RDBMS

TIP: This scenario is especially likely if you receive Error #3.

Environment

Using Microsoft SQL 2005 Enterprise Server Analysis Services (MSAS 2005).

Resolving The Problem

Scenario #1
Inside Cognos Controller, define and save a IBM Cognos 8 BI DataMart definition (to the relevant datamart database) before attempting to try to save a CUBE definition (to the Controller application database repository).

Scenario #2
Launch Cognos Controller Configuration and populate the field correctly for ‘Database connections for publish to datamart’.

Steps for both Scenarios #1 and #2:

PART ONE: Ensure that Data Mart RDBMS database has been correctly configured

1. Logon to the Controller application server as an administrator
2. Launch ‘Cognos Controller Configuration’ from the start menu
3. Open the section ‘Database connections for publish to datamart’
4. Select the appropriate Controller database
5. Fill in the details for your (separate) dedicated Data Mart RDBMS database
6. Repeat steps 4 and 5 for each Controller database that you require (for example ‘ControllerLIVE’, ‘ControllerTEST’ etc.)
7. Open ‘Database Connections’ section
8. Double-click on appropriate Controller database connection
9. Click green ‘play’ button to launch ‘Database Conversion Utility’
10. Click on ‘DMConv’ tab
11. Ensure that ‘Data’ section is pointing to correct UDL file (for example ‘C:\Program Files\cognos\c8\DMData\Controller_LIVE.udl’)
12. Ensure ‘DM Version’ has the value inside ‘Actual’ (for example for Controller 8.2 this should be ’26’ — later versions of Controller will have a higher number) being the same as the value inside ‘DmConv’ — if not, click ‘Run Steps’
13. IMPORTANT: Finally, you *must* obtain a short period of downtime, and restart the Windows service ‘Cognos Controller Batch Server’

PART TWO: Save target as Cognos BI, then change to MS SSAS

1. Launch Controller, and open menu item ‘Define Data Mart’
2. Select additional target to be ‘Cognos BI’
3. Complete the selections
4. Click save.
5. Go back and re-define your cube
6. Set additional target to MS SSAS
7. Now you can successfully save your MSAS cube template definition.

Scenario #3:
If you have received Error#3, then it is typical that the database that you are using for your ‘Database connections for publish to datamart’ is invalid/corrupt.

WARNING: The following steps assume that this is a test system (so you are happy to delete your current ‘datamart’ database because you do not need any of the data inside it).

Assuming you have happy with this, then:

Perform a SQL Database backup of the datamart database (for example, called ‘DataMartDatabaseName’)
Archive this backup (.BAK) file to a sensible location, as a precaution
Create a new SQL database (e.g. ‘NEWDataMartDatabaseName’)
Ensure that SQL login (e.g. ‘fastnet’) has dbowner rights ticked for ‘DataMartDatabaseName’. TIP: This does NOT mean that it should have sysadmin (dbo) rights! For more information, see separate IBM Technote 1347969.
Finally, perform ‘Part ONE’ and ‘Part TWO’ (described above inside ‘Scenarios #1 and #2’)
Test

Related Information

[{«Product»:{«code»:»SS9S6B»,»label»:»IBM Cognos Controller»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w\/o TPS»},»Component»:»Controller»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»8.5;8.4;8.3″,»Edition»:»Edition Independent»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]

Historical Number

1036428

Источник

I’m having an issue with querying from linked server using OPENQUERY in MS SQL. There has been some topics and solutions already presented for this issue, but noone of the fixed this one.

Now, I’m trying to run following procedure, which was made to copy all new data from one database to another:

USE_GS_DB

SET NOCOUNT ON;

DECLARE @tsql varchar(8000);
DECLARE @idexternal varchar(255);
DECLARE @timeexternal datetime;

SET @idexternal = (SELECT external_id FROM dbo.insert_data WHERE test_type = 1);
SET @timeexternal = (SELECT external_time FROM dbo.insert_data WHERE test_type = 1);

SELECT @tsql = '
SELECT * FROM OPENQUERY(TESTSERVER, ''
SELECT [open_test_uuid]
      ,[time_utc]
      ,[client_version]
FROM [GS_DB].[dbo].[table_data] WHERE [time_utc] >= ''' + convert(varchar, @timeexternal, 121) + ''' and [open_test_uuid] <> ' +  cast(@idexternal as varchar) + ''') ';

EXEC('INSERT INTO [GS_DB].[dbo].[c_table_data]
      ([open_test_uuid]
      ,[time_utc]
      ,[client_version])' + @tsql)

@idexternal and @timeexternal are set in another table that are overwritten with last input every time when copy occurs (set up by trigger).

I’ve tried to run only first part of procedure (one with OPENQUERY) without WHERE condition and everything works as intended, but as soon as I add condition, I get following message (doesn’t matter if I use variables or I just input the parameter):

OLE DB provider «SQLNCLI11» for linked server «TESTSERVER» returned
message «Deferred prepare could not be completed.». Msg 8180, Level
16, State 1, Line 1 Statement(s) could not be prepared. Msg 102, Level
15, State 1, Line 5 Incorrect syntax near ‘1’.

I have similar procedure for another database and it work without any issues. Would anyone have any solution or comment to this issue?

Источник

Question

Answers

Problem

Symptom

Cause

Environment

Resolving The Problem

Related Information

Historical Number