DTS possibility?

[eggy168]

I posted this in the Microsoft SQL Server: Programming earlier today, someone suggested I should post this message in this forum instead. I hope some guru can help me to solve this problem.

I don't know is it possible, but is there anyway I can set up a job or a store procedure or a view in SQL Server 2000 (Server A) in every Monday.

However, the source table(s)comes from another sql server 2000 (Serve B). I have the UA/PWD in Server B as a user.

So, after I run the job, it will append the result information to a table in Server A.

Can anyone gives me some tips of how to start this project?

Thank You.

 

[dbomrrsm]

Hello again

In DTS set up a connection to server a and one to server b.

all you need is the connection properties for each server ie the address username and password.

Having done that select a trnasform data task - then click server b (the source) then click server a (the destination).

This will create a line between the two - double click the line.

You can then specify a query that will be run on server b to select the data you require. on the destination tab select the table in server a where you want the data tranferred to. look at the other tab options - if only to learn what they are about.

when you have done save the package.

Right click on the package and select schedule package. Fill in the relevant things and the package will run to the schedule you specify.

Be aware that you need to have sql Server agent running at the time the job is due to run as that is what is used to monitor scheduled jobs - ie start them.

good luck and post back if you need further info.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[eggy168]

Hi DBomrrsim,

I think it is more complicated than now. I have the Server B connected, but I just found out the table(I thought) is a View instead of a table. And the View created from many different tables which I don't have access.
I only want a query that run against from the View in every 2 weeks. do you think there is another way to solve this problem?
Thank You.

 

[dbomrrsm]

do you have access to the View - if so it doesnt matter about access to the underlying tables - one of the ideas of a view ! .

you can do a select from the view into your table in server a.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[eggy168]

I can do a select statement in the server B, but not in the Server A where I want to run the job. The view is in Server B. So, what is your recommendation?

 

[eggy168]

I tried to run the DTS from both Server, and it had this error message, "Error Description: Server user " xxx" is not a valid user in database 'msdb'".
However, when I tried to run it immediately, it works no problem, any ideas?