Linked Servers OPENQUERY error "Deferred prepare could not be completed"

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

Answer 1

As an FYI the following statement works fine when run locally:

 

DECLARE @BuildClrVersion nvarchar(128)

SET @BuildClrVersion = CONVERT(nvarchar(128),SERVERPROPERTY('BuildClrVersion'))

 

Thanks,

Scott

Answer 2

Try:

 

declare @v sql_variant

 

set @v = (

SELECT *

FROM OPENQUERY(LKMSSQLXYZ01, 'SELECT SERVERPROPERTY(''BuildClrVersion'')')

)

 

select @v

GO

 

AMB

Answer 3

Thank you very much! That worked great.

Answer 4

This is really driving me crazy. I am trying to collect SERVERPROPERTY information across a linked server and store that information in a local table. Simple concept but using OPENQUERY makes the process extremely difficult.

 

Consider the following code. The linked server information is being put into my temp table (T1) just fine. I wanted to plug in the linked server name into a variable and execute that in my cursor OPENQUERY. However, OPENQUERY does not allow variable. So, I am building a string then executing that. But that gives me an error. Here is the code:  

 

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)

--Begin cursor

DECLARE T1_cursor CURSOR FOR

SELECT LinkedServer

FROM #T1

OPEN T1_cursor

FETCH NEXT FROM T1_cursor INTO @LinkServerName

WHILE @@FETCH_STATUS=0

BEGIN

SELECT @BuildQuery='SET @SN=(SELECT * FROM OPENQUERY (' + @LinkServerName+',''SELECT SERVERPROPERTY(ServerName)''))'

PRINT @BuildQuery

EXECUTE (@BuildQuery)

--SET @SN= (SELECT * FROM OPENQUERY (LKMSSQLxyz01, 'SELECT SERVERPROPERTY(''ServerName'')'))

FETCH NEXT FROM T1_cursor INTO @LinkServerName

END

 

CLOSE T1_cursor

DEALLOCATE T1_cursor

 

 

If I run the commented out SET statement within the cursor it works fine. As soon as I execute the string I get the following error:

 

(137 row(s) affected)

SET @SN=(SELECT * FROM OPENQUERY (AHSHPSSQL01,'SELECT SERVERPROPERTY(ServerName)'))

Msg 137, Level 15, State 1, Line 1

Must declare the scalar variable "@SN".

SET @SN=(SELECT * FROM OPENQUERY (AHSLMTWSQL01,'SELECT SERVERPROPERTY(ServerName)'))

Msg 137, Level 15, State 1, Line 1

Must declare the scalar variable "@SN".

.

.

.

 

I wish I knew what table the 'fielded' server property information was stored in. I could do away with the OPENQUERY. Any ideas would be appreciated.

 

Thanks,

Scott 

Answer 5

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

Answer 6

Thank you for the reply. This works. However, when I try to place the output in a variable it fails. This has been frustrating.

 

I would like to forget the OPENQUERY and use the four part name.

• Do you know what table(s) the SERVERPROPERTY information is stored in?

I looked at @@VERSION but it returns a blob of text data that isn't useful. I need the data in separate and distinct fields so I can store the SERVERPROPERTIES from multiple servers into one local table.

 

Thanks again for the help,

Scott