Linked Server openquery error

Question

Linked Server openquery error

sujiakm

SSC Eights!

Points: 955
More actions
December 11, 2008 at 3:28 am

#197372

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:)

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

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

CrazyMan SSChampion Points: 12560 More actions · Answer 1

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 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

sujiakm SSC Eights! Points: 955 More actions · Answer 3

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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

sujiakm SSC Eights! Points: 955 More actions · Answer 5

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

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