Grabbing New Data From SERVERA.DB.View to SERVERB.DB.Table 1

[SevereIdea]

I have two SQL Servers (SERVERA, SERVERB) On SERVERA I have a database (OGWIN) with a view (vw_zCutChecks). I created a one run DTS package that created a table (Checks) in a database (WTCHECKS) on SERVERB and imported all the records from that view. That was easy and worked perfectly, but now I'm having trouble with my next task...

I need to create a package that gets MAX(number) from SERVERB.WTCHECKS.Checks then pulls and imports into SERVERB.WTCHECKS.Checks all records from SERVERA.OGWIN.vw_zCutChecks where Number > MAX(Number) parameter.

Thanks,

Dave Savittiere

 

[mrdenny]

Working under the assumption that you have a single primary key record in the Checks table called checknumb.

First create a linked server from SERVERB to SERVERA.

Next create a job on SERVERB that runs the following.

insert into WTCHECKS.dbo.Checks
select *
from SERVERA.WTCHECKS.dbo.Checks
where CheckNumb not in (select CheckNumb from WTCHECKS.dbo.Checks)

No muss, no fuss, no DTS required.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SevereIdea]

Yep, that did the trick, thanks!! I added SERVERA to linked servers from EM on SERVERB and then wrote a stored procedure (sp_GetChecks) that I will now schedule to run twice a week.

Thanks again!

Dave

 

[mrdenny]

no problem.

Yeah, it'll work either way. Just depends on which server you want to run the job.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[yksvaan]

How come there's no star for mrdenny. Well, now there is.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]