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

Answer 1

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.

Answer 2

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