Hi All,

Firstly apologies for the obscure subject line.

The task I am try to achieve is to copy information from Database A on
Server 1 to Database B on Server 2.

I would like this to be a DTS task (so that Many tables can be updated
in the target database). However my knowledge of DTS is limited to the
single database.

For each table I would firstly INSERT into the target table the missing
rows using an outer join and an IS NULL on the primary key of the
target table. Secondly I would execute an UPDATE to update the fields
linking on the primary keys.

The problem I have, is that none of the DTS tasks allow me to link to
the destination database without hardcoding the
server.database.owner.table

Is there anyway I can do this, or do I have to go down the route of
using linked servers?

I've tried that with success (see below), but I just can't help but
wonder if there is a slicker way.

Also, when I run the following script in query analyzer, it doesn't
work unless I execute the sp_addlinkedserver line sepearately and then
run the entire script. The error it gives is:

"Could not find server 'DEVTEST1' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers."

It's almost as if in alter the procedure it is actually runnning the
line??????

Cheers

Alex

*****Example Script*******

ALTER PROC proc_ImportData

AS


--Add the connection to the CRM Database server.
EXEC sp_addlinkedserver
'DEVTEST1',
N'SQL Server'

INSERT INTO tblCompany(CompanyID, CompanyName, PrimaryPerson)
SELECT Comp_CompanyID, Comp_Name, RTRIM(Pers_FirstName) + ' ' +
RTRIM(Pers_LastName)
FROM DEVTEST1.CRM.dbo.Company AS Company
LEFT JOIN DEVTEST1.CRM.dbo.Person AS Person ON
Company.Comp_PrimaryPersonID = Person.Pers_PersonID
LEFT JOIN tblCompany ON Company.Comp_CompanyID =
tblCompany.CompanyID
WHERE tblCompany.CompanyID IS NULL
ORDER BY Company.Comp_Name

UPDATE tblCompany
SET CompanyName = Comp_Name,
PrimaryPerson = RTRIM(Pers_FirstName) + ' ' + RTRIM(Pers_LastName)
FROM DEVTEST1.CRM.dbo.Company AS Company
JOIN tblCompany ON Company.Comp_CompanyID = tblCompany.CompanyID
LEFT JOIN DEVTEST1.CRM.dbo.Person AS Person ON
Company.Comp_PrimaryPersonID = Person.Pers_PersonID

--Remove the connection to the CRM Database server.
EXEC sp_dropserver 'DEVTEST1'

******************************

Hello Chubby,


Linked servers or OPENDATASOURCE seem to be the way forward here. There
is no cool way to do this in DTS 2000

Allan