December 11, 2008 at 3:28 am
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:)
December 11, 2008 at 3:44 am
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 🙂
December 11, 2008 at 4:08 am
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?
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
December 11, 2008 at 4:31 am
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
December 11, 2008 at 4:53 am
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?
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
December 11, 2008 at 9:53 pm
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
December 12, 2008 at 12:29 am
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?
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