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