CODEWITHSUNDEEP
sqlsql-server-2005sql-server-2008
MartGriff
MartGriff

Reputation: 2851

SQL Server - OPENQUERY

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

Upvotes: 4

Views: 9875

Answers (4)

Nolleto
Nolleto

Reputation: 34

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

Upvotes: 1

jjchiw
jjchiw

Reputation: 4445

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

Upvotes: 8

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

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

Upvotes: 1

Andomar
Andomar

Reputation: 238086

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.

Upvotes: 0

Related Questions