I have a package that lives and executes on ServerA. It is copying data
from ServerB to ServerA doing a join on ServerA. ServerB is a linked
server.

The query looks something like this:

select * from [serverb].Database.owner.tableb b
join [servera].Database.owner.tablea a on a.primarykey = b.primarykey
where b.primarykey >= 10

This query works perfectly in a Query Analyzer window on ServerA. In DTS,
when I try and save the Transform Data
task containing this query it tells me that ServerA is not a linked server.
I've never had to create a server that is linked to itself. Doesn't make
any sense.

The odd part is that it works fine if the package lives and executes on
ServerC. In other words, all I did was copy this package from ServerC to
ServerA and change the database references in the fully qualified pathname
from ServerC to ServerA and it stopped working.

Is there some configuration thing that I need to do on ServerA?

The problem is that the "from" connection is server B so the context of the
query is for that server. Using my example, it's the serverB reference I
don't really need since it's the source. It's the serverA reference that
throws up. Hmmm...maybe that's the problem. Pause for a test...

That was it! I forgot to create the linked server going from serverB back
to serverA.

Thanks for stirring up the remaining brain cell.

Bob


"K" <anonymous (AT) discussions (DOT) microsoft.com> wrote

can just do this...