SQL Server - OPENQUERY

Question

I am using an Openquey which has been working fine on SQL Server 2005, I have 1 server that is SQL Server 2008 which this does not work on.

If I run the following:

SELECT * 
FROM OPENQUERY([Manchester], 
      '[Manchester].[PilotWebApp].[DBO].rsp_HandheldPerformance ''10/01/2009'', 
      ''10/10/2009''')

I get this error:

Cannot process the object "[Manchester].[PilotWebApp].[DBO].rsp_HandheldPerformance '10/01/2009', '10/10/2009'". 
The OLE DB provider "SQLNCLI" for linked server "Manchester" indicates that either the object has no columns or the current user does not have permissions on that object.

If I just run:

[Manchester].[PilotWebApp].[DBO].rsp_HandheldPerformance '10/01/2009', '10/10/2009'

it works fine. Has something been changed in 2008?

What it does is gets the data from the openquery and inserts into my temp table:

INSERT #TempHandheldPerformance SELECT * FROM OPENQUERY([Manchester], '[Manchester].PilotWebApp.DBO.rsp_HandheldPerformance ''10/01/2009'', ''10/10/2009''')

score 8 · Answer 1 · edited May 23 '17 at 10:27

Even the question it's from 2009, I had the same problem en 2012!! and it was kinda difficult to find the answer....anyway just used SET NOCOUNT ON before executing the SP

if Manchester is the LinkedServer the example code with SET NOCOUNT ON should be

SELECT * 
FROM OPENQUERY([Manchester], 
      'SET NOCOUNT ON; EXEC [PilotWebApp].[DBO].rsp_HandheldPerformance ''10/01/2009'', 
      ''10/10/2009''')

And to fill the temporary table I do

SELECT *
INTO #temptable
FROM OPENQUERY([Manchester], 
          'SET NOCOUNT ON; EXEC [PilotWebApp].[DBO].rsp_HandheldPerformance ''10/01/2009'', 
          ''10/10/2009''')

https://stackoverflow.com/a/2247200/181766

score 1 · Answer 2 · answered Oct 27 '09 at 18:46

Check and make sure that there's no linked server named [Manchester] on the remote box; your syntax could be interpreted as:

Connect from local server to a linked server named Manchester, and then execute a stored proc on another linked server named Manchester.

Stu

score 1 · Answer 3 · answered Jul 29 '15 at 18:33

1

Try add SET FMTONLY OFF; SET NOCOUNT ON; in your query

SELECT * INTO #temptable FROM OPENQUERY([Manchester], 'SET FMTONLY OFF; SET NOCOUNT ON; EXEC [Manchester].    [PilotWebApp].[DBO].rsp_HandheldPerformance ''10/01/2009'', ''10/10/2009''')

answered Jul 29 '15 at 18:33

Nolleto

34
2

score 0 · Answer 4 · answered Oct 26 '09 at 15:20

0

Verify that the linked server is using the same credentials as you are. You can find them on the Linked Server Properties, then Security Options.

answered Oct 26 '09 at 15:20

Andomar

232,371
49
380
404

already checked that and there fine. as if i try and do any other querys on that linked server it worked fine – MartGriff Oct 26 '09 at 15:35
Is the default catalog set properly? Is "RPC Out" enabled (it's required for stored procedure calls) ? – Andomar Oct 26 '09 at 15:43

SQL Server - OPENQUERY

4 Answers4