none
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

    Tuesday, May 13, 2008 1:47 PM

Answers

  • Try:

     

    declare @v sql_variant

     

    set @v = (

    SELECT *

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

    )

     

    select @v

    GO

     

    AMB

    Tuesday, May 13, 2008 6:47 PM
    Moderator
  • 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

    Thursday, May 15, 2008 6:31 PM
    Moderator

All replies

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

     

    DECLARE @BuildClrVersion nvarchar(128)

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

     

    Thanks,

    Scott

    Tuesday, May 13, 2008 6:35 PM
  • Try:

     

    declare @v sql_variant

     

    set @v = (

    SELECT *

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

    )

     

    select @v

    GO

     

    AMB

    Tuesday, May 13, 2008 6:47 PM
    Moderator
  • Thank you very much! That worked great.

    Wednesday, May 14, 2008 11:40 AM
  • 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 

    Thursday, May 15, 2008 6:08 PM
  • 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

    Thursday, May 15, 2008 6:31 PM
    Moderator
  • 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

    Tuesday, May 20, 2008 2:51 PM