Extracting data to linked Servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Extracting data to linked Servers

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-06-13 : 14:16:29

Greetings experts:

We have 2 databases running same server.

Let's call them database1 and database2.

A job was scheduled to extract data from database1 into database2 3 times a day.

This works fine.

Now, we have been asked to set up another job that will extract data, this time, from database2 into MS Access database.

I decided that setting up a linked server will be the easiest way to go about this.

I have set this up succeessfully.

We still want data extracted from database1 to database2 3 times daily, just like it is right now.

My questions, once data is extracted from database1 to database2, does that automatically update the linkedserver?

If no, can you please point me in the right direction to get this set up?

Thanks in advance

Thanks a lot in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-13 : 14:27:39

Nothing is automatic on a linked server.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-06-13 : 14:39:35

can you please instruct me on how to set it up so that once database1 is updated, linked server will be updated as well?

Thanks very much for your assistance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-13 : 15:15:48

I supposed you could put your code in triggers.

I've never used Access though, so I can't be of much help.

I believe replication works to Access though.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2007-06-13 : 15:30:04

I suppose this will work but I only have one doubt to clear up.

The way the current data extract from database1 to database2 is set up is that the current contents of database2 are deleted first and then repopulated with most current data.

As stated in my email, this happens 3 times a day.

The doubt I want to clear up is that since I am using triggers, if the contents of database2 are emptied and repopulated, will that have any adverse effect on the linked server?

Also, (sorry for the second questions), since I am setting up the trigger to fire off after each update, does it matter whether or not database2 is updated 3 times or more?

I appreciate your expertise Tara.

Here is the trigger I am using so far. It said, "Successfully created" but does it look like it will work?

CREATE TRIGGER trExtractCalendar_UpdateToOtherServer ON Custr AFTER UPDATE
AS
DELETE remoteServer...tableName --delete current data first
INSERT remoteServer...tableName --then insert latest data (field1,field2,field3,field4,fieldN)
SELECT value1,value2,value3.value4.valueM FROM inserted
GO

How does this look to you?

Again, thanks much for your assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources