Need to Automate Updates on WEB site

[DougP]

I am trying to create a DTS package or just plain T-SQL to:

1st Delete Records from Table1 on WEB site
2nd Import records from Access 2000 into a SQL Table1 on WEB site
3rd Delete records from SQL Table2 on WEB site
4th copy records from Table1 on WEB site to Table2 on WEB site.

How do I refer to a different Server in T-SQL
Use xxxx does not work
I cannot save the JOB on the WEB site server.

I have each step working separately OK.
So if I run the jobs one at a time they work OK
I just cannot get the whole thing to run as a Scheduled JOB.
DougP, MCP

 

[chenthorn]

Are you wanting to delete ALL records in your table 1 & table 2? if so, you can use an 'Execute SQL Task' in DTS to perform TRUNCATE TABLE <Table1>, perform a data pump from your access db into your table, then perform another 'Execute SQL Task' to truncate your other table, and use a third 'Execute SQL Task' with an insert statement to move the data into your table2.
To refer to another server, you first have to create a 'Linked Server' connection. I find it easiest to use EM to build them. THe applet is under <server>-> security --> Linked Servers. Right click on Linked Servers to add a new one. Pretty simple. After doing that, you can connect to another server in tsql by using the 4 part naming convention as explained in BOL.

 

[DougP]

Thanks for your FAST reply

Actually I forgot to mention the WEB Server is in my SQL Server Group already.
I created a ODBC connection to it and am using that.
So I can see the server and all the tables and can add and delete data manually.
Can I refer to it there somehow?

I tried to link it but it gave a message saying can't confirm the existance of the Server. And under the Server there is tables icon but no tables are showing. Is this normal? DougP, MCP