Linked Server openquery error

  • Unable to execute the following linked server query. Enabled remote Adhoc query in surface configuration.

    Code

    ====

    select * from openquery([system2-PC\sqlserver2005],'set nocount on;

    declare @i int

    declare @maxval int

    set @i= 1

    set @maxval =5

    while(@i<=@maxval)

    begin

    print @i

    set @i=@i+1

    end

    select getdate()

    ')

    Error

    ===

    Msg 7357, Level 16, State 1, Line 1

    Cannot process the object "set nocount on;

    declare @i int

    declare @maxval int

    set @i= 1

    set @maxval =5

    while(@i<=@maxval)

    begin

    print @i

    set @i=@i+1

    end

    select getdate()

    ". The OLE DB provider "SQLNCLI" for linked server "system2-PC\sqlserver2005" indicates that either the object has no columns or the current user does not have permissions on that object.

    Any suggestions...

    Thanks

    Suji:)

  • Why do you really want to do a Open Query when you have an option to create a linked server and use that server on SQL, which is more efficiant in terms of performance 🙂

  • Start simple...does this work?

    select * from OPENQUERY([system2-PC\sqlserver2005],'select getdate()')

    Remember, OPENQUERY requires that the linked server is already set up, can you confirm that it is by querying against it directly?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • select * from OPENQUERY([system2-PC\sqlserver2005],'select getdate()')

    &

    select * from OPENQUERY([system2-PC\sqlserver2005],'select * from sysobjects')

    Both Works fine..

    I have create the linked server on the default instance as [system2-PC\sqlserver2005]

    and I want to run a adhoc query with while loop in the linked server using openquery()

    which will run on the linked server and collect the data from there and insert the data in the local instance ..local table.

    pls advice..

    thanks in advance

    suji

  • sujiakm (12/11/2008)


    select * from OPENQUERY([system2-PC\sqlserver2005],'select getdate()')

    &

    select * from OPENQUERY([system2-PC\sqlserver2005],'select * from sysobjects')

    Both Works fine..

    I have create the linked server on the default instance as [system2-PC\sqlserver2005]

    and I want to run a adhoc query with while loop in the linked server using openquery()

    which will run on the linked server and collect the data from there and insert the data in the local instance ..local table.

    pls advice..

    thanks in advance

    suji

    This works...

    select * from openquery(MyLinkedServer,'DECLARE @Today DATETIME;

    SET @Today = DATEADD(HH, 2, getdate());

    SELECT @Today, getdate(); ')

    Also, if you shift the SELECT GETDATE() to before the WHILE, then the batch works:

    select * from openquery(MyLinkedServer, '

    set nocount on ;

    declare @i int ;

    declare @maxval int ;

    set @i= 1 ;

    set @maxval =5 ;

    select getdate();

    while(@i<=@maxval) begin print @i set @i=@i+1 end ;

    select @i

    ')

    ... but the downside is that only the first result set is returned.

    Perhaps you could post the code which you're intending to run on the linked server?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi.. appreciate your immediate reply...

    But my intent is not to print th getdate()....

    select * from openquery([linked server], '

    set nocount on ;

    declare @i int ;

    declare @maxval int ;

    set @i= 1 ;

    set @maxval =5 ;

    select getdate();

    while(@i<=@maxval) begin insert into localserver.xyz.dbo.tt values (@i) set @i=@i+1 end ;

    select @i

    ')

    .. in this query I am trying to run an INSERT cmd , which inserts the data collected at the LinkedServer into a local table on my localserver ie., collect the data from linkedserver and put the data into the local table on the centralized server...

    Hope I am clear...

    Could you pls suggest..

    thanks in advance

    Suji

  • sujiakm (12/11/2008)


    Hi.. appreciate your immediate reply...

    .. in this query I am trying to run an INSERT cmd , which inserts the data collected at the LinkedServer into a local table on my localserver ie., collect the data from linkedserver and put the data into the local table on the centralized server...

    The usual syntax for that would be

    INSERT INTO [local table on the centralized server]

    SELECT * FROM openquery([linked server], ...

    ... is there something about your requirements which makes this syntax unworkable?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply