Re: Cannot use openquery in SSIS

Can you create a connection in the new package to the linked server and call
the 'select top 1 * from table1' query directly on the server instead of
using OPENQUERY?

--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Maisie" <Maisie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:72155216-8936-49FB-8B47-229CC98BDE67@xxxxxxxxxxxxxxxx
I am converting an existing SQL 2000 DTS package to SQL 2005 SSIS package.

However when I test the package in the solution, I get this message saying
that
[Execute SQL Task] Error: Executing the query "SELECT * FROM OPENQUERY
(MyLinkedServer, 'select top 1 * from table1')" failed with the following
error: "Could not find server 'MyLinkedServer' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.". Possible failure
reasons: Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.

To make sure that my linked server is working, I tried to execute the same
TSQL statement and it works. Proving that my linked server is setup
correctly.

I have checked sysservers and my linked server is there. I even tried to
insert a new linked server via TSQL instead of by the wizard/window
(recommended by most the of searches I have done).

Can anybody help me with this? Is this a permissions thing?



.

Relevant Pages

  • RE: Tried that too...
    ... When you start DTS packackage start on srvB, the package run on srvB? ... If you execute sp on linked server, the sp run on target server, and start ... >> Andras Jakus MCDBA ...
    (microsoft.public.sqlserver.server)
  • Check for primary key violation
    ... I have a package that exports records from a linked server into a table ... type mismatch error on 'DTSSource'. ...
    (microsoft.public.sqlserver.dts)
  • Re: terrible question?
    ... Isn't that a question for the vendor of that package? ... you can create an Access db as a "Linked Server" in Sql Server. ...
    (comp.databases.ms-access)
  • MS SQL 2005 to Sybase via Linked server
    ... I'm having an issue setting up a linked server connection on a new SQL 2005 ... The connection is working on the existing sql 2000 box. ... I enter all the sybase server info and use the "Test ...
    (microsoft.public.sqlserver.odbc)
  • Transaction with linked server
    ... When I open transaction with insert statement from linked server and that ... When I kill connection it ... transaction or kill connection without restarting? ... (MySQLServerColumn) ...
    (microsoft.public.sqlserver.programming)
Loading