Error in source sql query from TransformTask

• g.firpo

  Old Hand

  Points: 366

  More actions

  February 15, 2002 at 3:44 am

  #157556

  Hi,

  we have a Transform Data Task that use a sql query for the source;

  the select we use is like this:

  select * from [SERVER_A].[DB_NAME].[dbo].[View_name] .

  Executing the package from a SQL2K, on the same SERVER_A, there is the error:

  "Could not find server SERVER_A in sysservers. Execute sp_addlinkedservers ....

  WHY THIS ? I am ON SERVER_A !

  The same select, executed from Query Analyzer, is correct.

  Now we change the select, omitting the server name:

  select * from [DB_NAME].[dbo].[View_name] :

  is OK.

  Now we change the select, changing the server name to a second server:

  select * from [SERVER_B].[DB_NAME].[dbo].[View_name] :

  is OK.

  Executing the package from a server with SQL 7 1/2 there are NO problems.

  Anyone have an idea of this?

  Thanks

  Ciao

  Gianangelo

• Leon Platt

  SSCommitted

  Points: 1921

  More actions

  February 15, 2002 at 6:31 am

  #423909

  My guess would be that DTS uses OLEDB to access the data? Someone correct me if I am wrong. And maybe the server is not configured in the sysServers table. The help file says this about the sysservers table:

  sysservers

  Contains one row for each server that Microsoft® SQL Server™ can access as an OLE DB data source.